Databases
StackOverflow2013
Postsdbo
POmysql join getting a value or a null into a column
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql join getting a value or a null into a column
    primarykey
    data
    text
    <p>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:</p> <p>users: UserID, UserName, etc.</p> <p>pricelists: PricelistID, UserID, ProductItemID, Price</p> <p>productitems: ProductItemID, ProductID, ItemID</p> <p>products: ProductID, ManufacturerID, WebPageText, etc.</p> <p>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. </p> <p>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. </p> <pre><code>$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 &gt; 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']; </code></pre> <p>Later in the code, I check whether $row_products['Price'] is empty or not to determine what is displayed on the page.</p> <p>EDIT: I thought this would be obvious, but ... I have to limit pricelists by <code>WHERE UserID = %s.</code> That means I can't just tack on a <code>LEFT JOIN pricelists ON pricelists.ProductItemID = productitems.ProductItemID</code>, 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. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USgreg0ire
    1. USmiyasudokoro
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysql join getting a value or a null into a column
      1. This table or related slice is empty.
      1. VTDownMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload