Query for multiple tables

Question

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.

keelerm · Accepted Answer · 2014-03-20 21:45:42Z

1

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.

1 Comment

iceMan33 Over a year ago

Thanks! Well explained!

fthiella · Accepted Answer · 2014-03-20 21:46:11Z

0

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.

answered Mar 20, 2014 at 21:46

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Comments

BarryDevSF · Accepted Answer · 2014-03-20 21:57:16Z

0

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'

answered Mar 20, 2014 at 21:57

BarryDevSF

4054 silver badges12 bronze badges

Collectives™ on Stack Overflow

Query for multiple tables

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related