Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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?
0
fedkris
Asked:
fedkris
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now