CODEWITHSUNDEEP
mysqlsql
iceMan33
iceMan33

Reputation: 369

Query for multiple tables

I'm trying understand how I can pull information from multiple tables at once in one query if that is possible.

I have 3 tables and I'm wondering if there is a way I can query all the product names for customers that live in california?

Table:
    products

Fields:
    productOid
    productName
    companyOid


Table:
    customerData

Fields:
    customerOid
    firstName
    lastName
    state

Table:
    orders

Fields:
    orderNumber
    customerOid
    productOid

Would this fall under something like an INNER JOIN?

Also, I'm learning mySQL.

Upvotes: 1

Views: 84

Answers (3)

BarryDevSF
BarryDevSF

Reputation: 405

This shows products that CA customers have ordered: 

SELECT p.productName 
FROM orders o 
INNER JOIN products p ON o.productOid = p.productOid 
INNER JOIN customerData c ON o.customerOid = c.customerOid 
WHERE c.state = 'CA'

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You could use one more join, but I would write it this way:

SELECT DISTINCT p.productName
FROM
  orders o INNER JOIN products p
  ON o.productOid = p.productOid
WHERE
  o.customerOid IN (SELECT customerOid
                    FROM customerData
                    WHERE state = 'California')

It might be a little slover than a join, but it's more readable.

Upvotes: 0

keelerm
keelerm

Reputation: 2943

You will need to use inner joins for this.

SELECT DISTINCT p.productName
FROM orders o
INNER JOIN customerData c ON o.customerOid = c.customerOid
INNER JOIN products p ON o.productOid = p.productOid
WHERE c.state = 'CA';

I am using DISTINCT here because it's possible a customer would order the same product more than once (or multiple customers would order the same products) and I'm assuming you don't want duplicates.

I'm also making the assumption that your state is represented as a two character column.

Read more about joins

Upvotes: 1

Related Questions