How to join 3 tables, and exclude rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to join 3 tables, and exclude rows

Author

Topic

mgrouff
Starting Member

2 Posts

Posted - 2009-12-14 : 08:16:05

I have a table supplier (supplier_id) and table product (product_id).
I have also a table A (supplier_id and product_id), witch connects suppliers and products that are registered together.

Issue:
I have a grid on a page where I register the suppliers products.
This cell has a dropdown list.

How do I create a view, that omits already registered products on the given supplier.

In other words, I only want to se products that are NOT registered.

I need the supplier_id and the product_id, as output.

I feel that I have tried all combinations...so Im stuck. Please help....

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-14 : 08:39:09

Select
pi.product_id
From Product_ID pi
Left Outer Join Table_1 a pi ON a.Product_ID = pi.Product_ID
WHERE
a.Product_ID IS NULL

Edited for correctness.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-14 : 08:41:07

Some other ideas to play around with...

declare @supplier table (supplier_id int)
insert @supplier select 1 union all select 2 union all select 3
declare @product table (product_id int)
insert @product select 4 union all select 5
declare @A table (supplier_id int, product_id int)
insert @A select 1, 4 union all select 3, 4 union all select 3, 5

select * from @supplier a cross join @product b
where not exists (select * from @A where supplier_id = a.supplier_id and product_id = b.product_id)
order by supplier_id, product_id

select * from @supplier a cross join @product b except select * from @A
order by supplier_id, product_id

select * from @product a where not exists (select * from @A where supplier_id = 2 and product_id = a.product_id) order by product_id

select * from @product where product_id not in (select product_id from @A where supplier_id = 2) order by product_id

select product_id from @product except select product_id from @A where supplier_id = 2 order by product_id

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

mgrouff
Starting Member

2 Posts

Posted - 2009-12-14 : 09:14:42

Hi, DP978.
Thanks for your reply. But, I have 3 tables.

Supplier - supplier_id as PK
-----------
supplierA
suppleirB
supplierC

Product - product_id as PK
---------
productA
productB
productC

connection supplier_id as FK and product_id as FK
-----------
supplierA - productA
supplierB - productC
supplierC - productB

What I want for output is
SupplierA with product B and C
SupplierB with product A and B
SupplierC width product A and C,

all other combinations than the combinations already registered in the connection table

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 09:35:44

This should work I guess...

declare @supplier table (supplier_id int,supplier_name varchar(10))
insert @supplier
select 1,'supplierA' union all
select 2,'supplierB' union all
select 3,'supplierC'

declare @product table (product_id int,product_name varchar(10))
insert @product
select 1,'productA' union all
select 2,'productB' union all
select 3,'productC'

declare @connection table (supplier_id int,product_id int)
insert @connection
select 1,1 union all
select 2,3 union all
select 3,2

select t.supplier_name,t.product_name from
(
select * from @supplier a 
cross join @product b 
) t
left join @connection c
on t.supplier_id = c.supplier_id and t.product_id = c.product_id
where c.supplier_id is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources