Unmatch Query on 2 fields using 3 tables

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 2000 Forums

Transact-SQL (2000)

Unmatch Query on 2 fields using 3 tables

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2004-10-01 : 14:24:08

I have 3 tables: tbl_branch; tbl_branchmargins; tbl_products

tbl_branch is just a list of branch names
tbl_products is a list of products
tbl_branchmargins needs to contain a record for each product per branch.

I need to query the tbl_branchmargins table to list the products which do not have records per branch.

Meaning I need to identify products in which a single branch may not have a tbl_branchmagins record for.

[u]This gives me products which don't exist in tbl_branchmargins:[u]
SELECT
p.product_id AS PID

FROM
tbl_branchmargin bm RIGHT OUTER JOIN
tbl_product p ON bm.product_id = p.product_id

WHERE
(bm.product_id IS NULL)
AND (p.product_active = 1)

....but now I need to be sure that ALL of the branches have 1 record in branchmargin per product.

Do I need to do multiple passes on this?

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-01 : 15:14:47

select full_bp.*
from
(select *
from tbl_branch b,tbl_products p
WHERE p.product_active = 1 ) full_bp
left join tbl_branchmargins bp on full_bp.branch=bp.branch and full_bp.product=bp.product
where bp.branch is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources