Select rows based on an array of values and join data

Question

I have a MySQL database of contacts with three tables.

person
personContact
personDetails

Each contact shares a primary key called 'ID'

The personContact table contains a value called 'personZip' which happens to be their mailing address zip code.

I'd like to write a SQL query that will give me all the contact data for each person in a specific array of zip codes.

I've written a simple statement to perform an inner join on 2 of the tables:

SELECT * FROM `personContact`
INNER JOIN person
ON personContact.ID=person.ID

I've written a statement to select only the zip codes I need:

SELECT * FROM 'personContact'
WHERE personContact.personZip=12564 
OR personContact.personZip=12563 
OR personContact.personZip=12522 
OR personContact.personZip=12590
OR personContact.personZip=12594
OR personContact.personZip=12533
OR personContact.personZip=12570
OR personContact.personZip=12589
OR personContact.personZip=10509

I'm not sure how to perform two joins, to merge all columns from all three tables.

I'm not sure how to write the query to accommodate both the selection of zip codes and the JOINS.

MySQL errors are not helping me move in the right direction.

I posted exactly what you asked for right from the start. Have you even tried it? — Morpheus
– Morpheus, Commented Oct 7, 2015 at 22:05

Vamsi Prabhala · Accepted Answer · 2015-10-07 22:02:34Z

2

You were almost there. Use in which is equivalent to or.

SELECT pc.* --select columns from the other tables as needed. 
 FROM 
`personContact` pc 
INNER JOIN person p ON pc.ID = p.ID
INNER JOIN personDetails pd on pd.ID = p.ID
where pc.personzip in (12563, 12522, 10509) -- add more zips as needed

edited Oct 7, 2015 at 22:02

answered Oct 7, 2015 at 21:37

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

KJ Prince Over a year ago

pc.zip is throwing unknown column error. Not sure what .zip is.

Vamsi Prabhala Over a year ago

it should have been personzip. modified the query

KJ Prince Over a year ago

While this might work, I'm not sure what's happening here. pc.* does...what?

Morpheus Over a year ago

pc.* returns all columns from the personContact table only

KJ Prince Over a year ago

This just filters zip codes and returns columns from personContact table only. Not seeing columns from other two tables joined.

|

Morpheus · Accepted Answer · 2015-10-07 21:56:46Z

1

Join all three tables and return all columns while filtering by zip code:

SELECT person.*, personContact.*, personDetails.*
FROM person
INNER JOIN personContact ON personContact.ID = person.ID
INNER JOIN personDetails ON personDetails.ID = person.ID
WHERE personContact.personZip = 12564
    OR personContact.personZip = 12563
    OR personContact.personZip = 12522
    OR personContact.personZip = 12590
    OR personContact.personZip = 12594
    OR personContact.personZip = 12533
    OR personContact.personZip = 12570
    OR personContact.personZip = 12589
    OR personContact.personZip = 10509

EDIT: Multiple OR statements can be replaced using IN as others have suggested

WHERE personContact.personZip IN (12564, 12563, 12522, 12590 ...)

edited Oct 7, 2015 at 21:56

answered Oct 7, 2015 at 21:38

Morpheus

1,6321 gold badge21 silver badges32 bronze badges

4 Comments

Hotdin Gurning Over a year ago

Use IN would be more efficient in this case. select * would return all joined tables without describe them one by one.

Morpheus Over a year ago

I would probably also alias the tables, but I left it in the format presented in the question.

KJ Prince Over a year ago

Any particular reason this query would take a very long time?

Morpheus Over a year ago

If you are comparing the run time to the other answer keep in mind that an additional table was added and more zip codes are being checked. Otherwise, there could be various factors such as number of rows, table index, etc.

Collectives™ on Stack Overflow

Select rows based on an array of values and join data

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related