mysql join getting a value or a null into a column

Question

I am very new to SQL, so I am having a couple problems figuring things out. The database is for an online store, and the structure is something like this:

users: UserID, UserName, etc.

pricelists: PricelistID, UserID, ProductItemID, Price

productitems: ProductItemID, ProductID, ItemID

products: ProductID, ManufacturerID, WebPageText, etc.

Each product can have one or more items (e.g. if the product page is selling T-shirts, there may be 5 different T-shirt variations for sale). That's pretty normal. What's not normal is that the pricing for each item is determined by what's in the user's pricelist, because each user is assigned particular pricing. Not all users have a price assigned for every item.

Here is the question: The requirement from management is that the user should be able to see every product, even if that product is not in the user's pricelist. If the user has no pricelist entry for a productitem, the page should display, "Contact us for pricing." I have not been able to figure out how to do this with one query. Every join that I attempted involving the pricelist table threw out products for which the user didn't have a price assigned. I am given only the ProductID and UserID. I have put my code below, which is a mix of mysql and PHP. It works, but it is clunky, especially seeing as I have to redo the second query over and over. Please tell me how I really ought to be doing it.

$query_product = sprintf("SELECT * , (items.Stock - (SELECT Coalesce(Sum(DetailQuantity),0) 
FROM orderdetails 
INNER JOIN orders ON OrderID = DetailOrderID 
INNER JOIN productitems ON orderdetails.ProductItemID = productitems.ProductItemID
INNER JOIN products ON productitems.ProductID = products.ProductID
INNER JOIN items ON productitems.ItemID = items.ItemID
WHERE OrderDate > ProductUpdateDate))*ProductLive 
AS NumLeft
 FROM productitems
 INNER JOIN products ON products.ProductID = productitems.ProductID
JOIN items ON productitems.ItemID = items.ItemID
WHERE products.ProductID = %s",GetSQLValueString($ProductID, "int"));
$product = mysql_query($query_products, $x) or die(mysql_error());
$row_product = mysql_fetch_assoc($product);

//after narrowing down to one productitem, either through user selections or while looping through $row_product:

$query_price = sprintf("SELECT Price FROM pricelists 
WHERE UserID = %s AND ProductItemID = %s", GetSQLValueString($UserID, "int"), GetSQLValueString($row_product['ProductItemID'], 'int'));
$price = mysql_query($query_price, $x) or die(mysql_error());
$row_price = mysql_fetch_assoc($price);
$row_product['Price'] = $row_price['Price'];

Later in the code, I check whether $row_products['Price'] is empty or not to determine what is displayed on the page.

EDIT: I thought this would be obvious, but ... I have to limit pricelists by WHERE UserID = %s. That means I can't just tack on a LEFT JOIN pricelists ON pricelists.ProductItemID = productitems.ProductItemID, which was actually the first thing I tried, and which does not work. Limiting the outer join in the WHERE statement turns it into an inner join.

score 2 · Accepted Answer · answered Nov 22 '11 at 20:52

2

Add the pricelists table as an LEFT join to your main query:

SELECT field1, field2, ..., pl.*
FROM orderdetails 
INNER JOIN orders ON OrderID = DetailOrderID 
...
LEFT JOIN pricelists pl PN pl.ProductItemID = productitems.ProductItemID
..

In your code check if the price in the pricelists table is null, if so, show the text.

answered Nov 22 '11 at 20:52

Michiel van Vaardegem

2,260
20
35

An outer join won't work because I have to have the WHERE pricelists.UserID = %s statement, which automatically turns an outer join right back into an inner join. – miyasudokoro Nov 22 '11 at 21:39
Actually, I found a page [here](http://stackoverflow.com/questions/5236078/left-outer-join-not-returning-all-records-from-primary-table) that maybe completes your answer. Would this work? `LEFT JOIN pricelists pl ON pl.ProductItemID = productitems.ProductItemID AND pl.UserID = %s` – miyasudokoro Nov 22 '11 at 21:55
Putting the AND into the left join works. Thanks for helping me come up with the answer. – miyasudokoro Nov 22 '11 at 22:31

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

You should look into using an outer join for this. Heres a great description of the difference between an inner and outer join -> inner join vs outer join

edited May 23 '17 at 11:51

Community

1
1

answered Nov 22 '11 at 20:58

Ben English

3,900
2
22
32

1

An outer join won't work because I have to have the WHERE pricelists.UserID = %s statement, which automatically turns an outer join right back into an inner join. – miyasudokoro Nov 22 '11 at 21:39
1

huh? then switch the order of your tables in your join or use right outer join – Ben English Nov 22 '11 at 21:56
@user1053109 my point was to move your where clause to the ON portion of the join and if need be rearrange your join. -1 for not knowing how to join. – Ben English Nov 23 '11 at 16:14
if you had read my link this would have been pretty obvious to you, but instead of reading I guess you'd rather be snarky. – Ben English Nov 23 '11 at 16:21
I *did* read your link. You are the one who needs to read it, because contrary to your belief, it says nothing about "moving your where clause to the ON portion of the join." If that was really your point, you should have posted a link to something that actually stated your point. Duh. – miyasudokoro Dec 14 '11 at 18:22
1

read it again. It explains the difference of the on vs where clause and their effects on joins. – Ben English Dec 16 '11 at 06:04
1

I'll make it dead simple for you. you did notice this syntax didn't you: `select a.*,b.* from a,b where a.a = b.b(+);` vs `select a.*,b.* from a,b where a.a = b.b;` if you'd read the link it would've shown that what you were doing was in fact an inner join and that if you want to use that syntax you need to use either the (+) operator or move the condition to the ON clause otherwise it IS an INNER JOIN. It's even in the top response. Look at the INNER JOIN examples, then look at the OUTER JOIN examples and it should be pretty self explanatory. – Ben English Dec 16 '11 at 06:18

mysql join getting a value or a null into a column

2 Answers2