not in - sql query

hello, i have two tables, a table (PROF_CONT) with records (idcontact, name, ...), the other table (enq_fashion_answers), contains answers. Not every contact completed his answers, so i wanna have all records, that not completed the survey.

this query, shows me two records that completed the survey, i want, all records, that didn't filled it in, they are not in table enq_fashion_answers.

SELECT     admincomeos.PROF_CONT.K_PROFILE, Lemento.dbo.enq_fashion_answers.id_fash_enq AS Expr1, Lemento.dbo.enq_fashion_answers.id_answer, 
                      Lemento.dbo.enq_fashion_answers.id_fash_enq, Lemento.dbo.enq_fashion_answers.idcontact, , 
                      admincomeos.PROF_CONT.K_CONTACT
FROM         Lemento.dbo.enq_fashion_answers INNER JOIN
                      admincomeos.PROF_CONT ON Lemento.dbo.enq_fashion_answers.idcontact = admincomeos.PROF_CONT.K_CONTACT
WHERE     (admincomeos.PROF_CONT.K_PROFILE = 1658) AND (Lemento.dbo.enq_fashion_answers.id_fash_enq = 20)

Open in new window



someone an idea? thx? function not in?
fedkrisAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you can do like this:
SELECT     admincomeos.PROF_CONT.K_PROFILE, Lemento.dbo.enq_fashion_answers.id_fash_enq AS Expr1, Lemento.dbo.enq_fashion_answers.id_answer, 
                      Lemento.dbo.enq_fashion_answers.id_fash_enq, Lemento.dbo.enq_fashion_answers.idcontact, , 
                      admincomeos.PROF_CONT.K_CONTACT
FROM          admincomeos.PROF_CONT 
LEFT JOIN Lemento.dbo.enq_fashion_answers 
          ON Lemento.dbo.enq_fashion_answers.idcontact = admincomeos.PROF_CONT.K_CONTACT
AND (Lemento.dbo.enq_fashion_answers.id_fash_enq = 20)  
WHERE     (admincomeos.PROF_CONT.K_PROFILE = 1658) 
  AND Lemento.dbo.enq_fashion_answers.idcontact  IS NULL 

Open in new window

                                 

and you should read this article to start using table aliases to make your query more readable:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
0
All Courses

From novice to tech pro — start learning today.