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

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

Very simple MSSQL syntax question

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
algernon23
Asked:
algernon23
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
bmatumburaCommented:
Your subquery (select dv_id, dp_id from dp) must not return multiple fields if you want to use the NOT in clause
0
 
algernon23Author Commented:
Thank you for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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