Solved

Very simple MSSQL syntax question

Posted on 2009-04-11
3
163 Views
Last Modified: 2012-05-06
Hi,

Why does this query give me a syntax error in MS SQL Server?

select dv_id, dp_id from em where dv_id, dp_id not in (select dv_id, dp_id from dp)

I'm just trying to check for invalid data for a foreign key referencing a two-part primary key.

What is the correct syntax for this? Thanks in advance.
0
Comment
Question by:algernon23
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24121882
this will do:
select dv_id, dp_id from em where not exists (select null from dp where dp.dv_id = em.dv_id and dp.dp_id = em.dp_id) 

Open in new window

0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24121884
Your subquery (select dv_id, dp_id from dp) must not return multiple fields if you want to use the NOT in clause
0
 

Author Closing Comment

by:algernon23
ID: 31569162
Thank you for your help.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Parsing a String with SQL Syntax 23 45
Query group by data in SQL Server - cursor? 3 34
2 IIF's in Access query 25 32
awk and Pythagoras? 5 8
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

831 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