Solved

not in - sql query

Posted on 2014-03-03
1
235 Views
Last Modified: 2014-03-04
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
Comment
Question by:fedkris
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39902612
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question