Search code examples
sqlselectdatabaserelational

How do I query three tables based on a single field

I have three tables in an SQL database. This relational system was designed by someone else but I was asked to create a new query because he no longer works for the company. I will use sample names for confidentiality purposes.

  1. customer
    customer_id
    product_code
    product_color
    product_size
    ...

  2. customer_link
    customer_id
    product_id

  3. customer_stats
    product_id
    product_actual_product
    product_actual_color
    product_actual_size
    ...

This system was designed for users to enter information in the system and have multiple products for each customer. In customer, the fields shown are all codes linked by customer_id from (1) to (2). Two then can have multiple links between (2) and (3) based on multiple records in (2) with the same customer_id and different product_id's. (3) has the linked product_id and the actual text for the codes listed in (1).

The bottom line is that I need a SELECT statement based on the fields in (1) but with the data stored in (3). Wow I'm confused and could really use some help.

Solution

  • This should be pretty straight forward,

    SELECT  a.*, c.*
FROM    customer a
        INNER JOIN customer_link b
            ON a.customer_ID = b.customer_ID
        INNER JOIN Customer_stats c
            ON b.product_id = c.product_id

    To learn more about joins, see the link below