Sql statement help - not sure whether it is possible to do it in one sql statement

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,

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.

Cathy

Posted 30-Nov-13 18:36pm

snoopy18

Add a Solution

2 solutions

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.

Posted 30-Nov-13 18:48pm

PIEBALDconsult

Comments

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.

Cathy

PIEBALDconsult 1-Dec-13 10:24am

It would be one statement.

Add a Solution

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

Amir Mahfoozi · Accepted Answer · 2013-11-30T19:57:00

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 :
http://technet.microsoft.com/en-us/library/ms188055.aspx[^]

So your SQL is this :

SQL

select aid, bid from a,b as cartesian
except
select aid, bid from c

Good Luck

Posted 30-Nov-13 19:57pm

Amir Mahfoozi

Comments

snoopy18 1-Dec-13 2:08am

Thank you! It works perfectly.

Amir Mahfoozi 1-Dec-13 2:10am

You're welcome.

Sql statement help - not sure whether it is possible to do it in one sql statement

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Sql statement help - not sure whether it is possible to do it in one sql statement

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us