Need Help with Advanced SQL Query

[wjs]

It's advanced for me, at least. I have three tables that I need to use:

Product (product_id and product_title are the fields)

shipRegion (shipRegion_ID)

product_shipRegion_shipCharge (product_id, shipRegion_ID,
primaryShipCharge, secondaryShipCharge)

What I am trying to do is create a query that will look for two things:

1. Any product that is listed in the product_shipRegion_shipCharge table that
has a primary or secondary ship charge of $0.00.

That part is easy:

SELECT DISTINCT p.product_id, p.product_title
FROM product p
INNER JOIN product_shipRegion_shipCharge pss ON p.product_id = pss.product_id
AND (pss.primaryShipCharge IS NULL OR pss.primaryShipCharge = 0 OR
pss.secondaryShipCharge IS NULL OR pss.secondaryShipCharge = 0)
WHERE p.display = 1
AND p.price > 0



It's this next part that's tricky for me:

2. Get the product_id and product_title (from the product table) that does NOT
have any entry in the product_shipRegion_shipCharge table.

I'm guessing that I need to include some kind of LEFT JOIN in the above query,
but I have no idea how to do it.

Anyone?

Josh

[paross1]

As long as you don't want it in a single query, this should do it.

SELECT DISTINCT p.product_id, p.product_title
FROM product p
WHERE NOT EXISTS
(SELECT 1
FROM product_shipRegion_shipCharge pss
WHERE p.product_id = pss.product_id)

If you want it all in a single query, then just UNION your query with this one.

Phil

[liquid One]

Actually I think you may have better luck with using union. I have a similar
query that i use that for and it works great. Make all the same fields
selected, but put a NULL for the values that arent applicable. . .

[wjs]

The UNION indeed works fine. What I was missing knowledge-wise was the "WHERE NOT EXISTS."

Thanks to you both.

Josh

[paross1]

You could also use a NOT IN, but that generally isn't as efficient as using
EXISTS or NOT EXISTS.

SELECT DISTINCT p.product_id, p.product_title
FROM product p
WHERE NOT IN
(SELECT pss.product_id
FROM product_shipRegion_shipCharge pss)

Phil

[JaredJBlackburn]

This should be what the query would look like using the left join:

SELECT product_table.product_id, product_table.product_title
FROM product_table
LEFT OUTER JOIN product_shipRegion_shipCharge
ON product_shipRegion_shipCharge.product_id = product_table.product_id
WHERE product_shipRegion_shipCharge. product_id IS NULL

SQL Server's query optimizer will probably turn the '...IN (subquery)' or
'...exists (subquery)' into this kind of execution plan on its own - depending
on your table structures.