Solved

not in - sql query

Posted on 2014-03-03
1
229 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 142

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

770 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