Click here to Skip to main content
14,302,372 members
Rate this:
Please Sign up or sign in to vote.

I am using SQL server 2008. I have 3 tables. Table a has 1 column aid, table b has 1 column bid and table c has 2 columns aid, bid. Is it possible to use one SQL statement that returns all the combination of aid and bid that does NOT exists in table c.

e.g. table a has 2 rows: a, b
table b has 3 rows 1,2,3
table c has 2 rows (a, 2), (b,1)

and I want to return record set to be 4 rows: (a,1), (a,3), (b,2), (b,3)

Thanks in advance for any help.

Rate this:
Please Sign up or sign in to vote.

Solution 2

You can produce table a and b cartesian product and then subtract the c set from it to see the result. For subtraction use except operator. for more study read set functions of TSQL :[^]

So your SQL is this :
select aid, bid from a,b as cartesian
select aid, bid from c

Good Luck
snoopy18 1-Dec-13 2:08am
Thank you! It works perfectly.
Amir Mahfoozi 1-Dec-13 2:10am
You're welcome.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Sure, what comes to mind is using a Common Table Expression to form a set of all the relationships and then Outer Join that with table C and form a set of the elements in the CTE that are not in C.
snoopy18 1-Dec-13 1:59am
Thank you very much. This definitely helps and I will research on how to use CTE. However, I am looking for using one sql statement. Is it possible? Thanks.

PIEBALDconsult 1-Dec-13 10:24am
It would be one statement.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100