Search code examples
sqlvirtuemart

SQL Beginner: Getting items from 2 tables (+grouping+ordering)

enter image description here

I have an e-commerce website (using VirtueMart) and I sell products that consist child products. When a product is a parent, it doesn't have ParentID, while it's children refer to it. I know, not the best logic but I didn't create it.

My SQL is very basic and I believe I ask for something quite easy to achieve

  • Select products that have children.
  • Sort results by prices (ASC/DSC).
Solution

  • This table design is subpar for a number of reasons. First, it appears that the value 0 is being used to indicate lack of a parent (as there's no 0 ID for products). Typically this will be a NULL value instead.

    If it were a NULL value, the SQL statement to get everything without a parent would be as simple as this:

    SELECT * FROM Products WHERE ParentID IS NULL

    However, we can't do that. If we make the assumption that 0 = no parent, we can do this:

    SELECT * FROM Products WHERE ParentID = 0

    However, that's a dangerous assumption to make. Thus, the correct way to do this (given your schema above), would be to compare the two tables and ensure that the parentID exists as a ProductID:

    SELECT a.* 
FROM Products AS a 
WHERE EXISTS (SELECT * FROM Products AS b WHERE a.ID = b.ParentID)

    Next, to get the pricing, we have to join those two tables together on a common ID. As the Prices table seems to reference a ProductID, we can use that like so:

    SELECT p.ProductID, p.ProductName, pr.Price
FROM Products AS p INNER JOIN Prices AS pr ON p.ProductID = pr.ProductID
WHERE EXISTS (SELECT * FROM Products AS b WHERE p.ID = b.ParentID)
ORDER BY pr.Price

    That might be sufficient per the data you've shown, but usually that type of table structure indicates that it's possible to have more than one price associated with a product (we're unable to tell whether this is true based on the quick snapshot).

    That should get you close... if you need something more, we'll need more detail.