CODEWITHSUNDEEP
phpmysqlpdo
Joseph_J
Joseph_J

Reputation: 3669

How to properly join tables with a many to one relationship

I have six tables

Using only the customerId I am trying to query the four store tables to see if any of the store tables contain the customerId. If the customerId is found on any of the four store tables I want to use the orderIdHash to get me back to the original Orders table and return the row for any orders that were found.

enter image description here

If I use a the customerId for Mike I would expect row 1 from the Orders table.

This is what I have tried so far.

"SELECT 

    o.dateShipped       AS orderShipped,
    o.shipped           AS shipped,
    o.recieved          AS recieved

    FROM Orders o

    JOIN OrdersHash oHash
        ON o.orderId = oHash.orderId
    JOIN StoreA a
        ON ohash.orderIdHash = a.orderIdHash
    JOIN StoreB b
        ON ohash.orderIdHash = b.orderIdHash
    JOIN StoreC c
        ON ohash.orderIdHash = c.orderIdHash
    JOIN StoreD d
        ON ohash.orderIdHash = d.orderIdHash

    WHERE
    a.customerId = :customerId1
    OR b.customerId = :customerId2
    OR c.customerId = :customerId3
    OR d.customerId = :customerId4";

**customerId 1,2,3,4 are all the same value.. I have to use a different name for binding in PDO.

This will return a result but it seems to return the same row from Orders for every row in a store with a matching orderIdHash when I just need the one record from the Orders table.

Thank you in advance for your help.

Upvotes: 0

Views: 57

Answers (1)

Nick
Nick

Reputation: 147146

It seems you probably want to UNION the store results and then JOIN that to the Orders table. By using UNION rather than UNION ALL, we can select only the distinct orderIdHash values, ensuring we only get one row for each Order in the result table. Something like this:

SELECT o.dateShipped       AS orderShipped,
       o.shipped           AS shipped,
       o.recieved          AS recieved
FROM (SELECT customerId, orderIdHash
      FROM (SELECT customerId, orderIdHash FROM StoreA
            UNION
            SELECT customerId, orderIdHash FROM StoreB
            UNION
            SELECT customerId, orderIdHash FROM StoreC
            UNION
            SELECT customerId, orderIdHash FROM StoreD
            ) stores
      WHERE customerId = :customerId) c
JOIN OrdersHash oHash ON oHash.orderIdHash = c.orderIdHash
JOIN Orders o ON o.orderId = oHash.orderId

Upvotes: 1

Related Questions