Problem mit SQL Abfrage

[Grolle]

Hallo,

ich habe einn Problem mit einer SQL-Abfrage:

SQL-Code:

SELECT a.id, a.color,a.archiv, RTRIM(a.name) + '', '' + RTRIM(a.first_name) as wholename FROM contacts as a
WHERE a.id NOT IN (SELECT b.contact_id FROM projects_to_contacts as b WHERE b.project_id=103) ORDER BY a.name

geht ohne Probleme!

SQL-Code:

SELECT a.id, a.color,a.archiv, RTRIM(a.name) + '', '' + RTRIM(a.first_name) as wholename FROM contacts as a
WHERE  [b]a.archiv=0 AND[/b] a.id NOT IN (SELECT b.contact_id FROM projects_to_contacts as b WHERE b.project_id=103) ORDER BY a.name

geht nicht. Fehlermeldung: Invalid operand for operator: = :gruebel:

Viele Grüße ...

//Edit: Tags schachteln geht wohl nicht. Bei der zweiten Abfrage ist ein a.archiv=0 AND in der where-Klausel!

[Die Muhkuh]

Geht es, wenn Du eine Klammer drum rum machst?

SQL-Code:

SELECT a.id, a.color,a.archiv, RTRIM(a.name) + '', '' + RTRIM(a.first_name) as wholename FROM contacts as a
WHERE  (a.archiv=0) AND (a.id NOT IN (SELECT b.contact_id FROM projects_to_contacts as b WHERE b.project_id=103)) ORDER BY a.nam

[Grolle]

Hi,

hatte ich auch schon getestet. Fehlermeldung bleibt die Gleiche :? Das geht auch nicht:

SQL-Code:

SELECT a.id, a.color,a.archiv, RTRIM(a.name) + '', '' + RTRIM(a.first_name) as wholename FROM contacts as a
WHERE  ((a.archiv=0) AND (a.id NOT IN (SELECT b.contact_id FROM projects_to_contacts as b WHERE b.project_id=103))) ORDER BY a.name

Greetz ...

[DeddyH]

So vielleicht?

SQL-Code:

SELECT a.id, a.color,a.archiv, RTRIM(a.name) + '', '' + RTRIM(a.first_name) as wholename 
FROM contacts a
LEFT JOIN projects_to_contacts b ON b.contact_id = a.id
WHERE a.archiv=0 
AND b.contact_id IS NULL
ORDER BY a.name

[Grolle]

falscher Datentyp :pale:

[joachimd]

[quote="Grolle"]

SQL-Code:

SELECT a.id, a.color,a.archiv, RTRIM(a.name) + '', '' + RTRIM(a.first_name) as wholename FROM contacts as a
WHERE  [b]a.archiv=0 AND[/b] a.id NOT IN (SELECT b.contact_id FROM projects_to_contacts as b WHERE b.project_id=103) ORDER BY a.name

was ist a.archiv für ein Datentyp?

[Grolle]

Hi,

war erst ein string, jetzt ist es ein integer und geht natürlich :oops:

Viele Grüße ...