Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

not in - sql query

Posted on 2014-03-03
1
Medium Priority
?
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

721 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