mysql select problem

Question

Hi were trying to perform a mysql select which isnt going to plan and hoping someone can shed some light on it.

we have estimated 10,000 plus listing records, a customer can have several listing records for different locations. we need to select all customer listings where at least one of the locations is equal to a specifield location.

so for example lets say customer 1 has a listing in sheffiled, doncaster, leeds, wakefield and customer 2 has listings in london and brighton.

Now I want to select all customer listings where one of the listings is for the area sheffield.

Id hope to get back the 4 rows for customer 1 because one of his listings is in sheffield.

for the sake of this example lets just presume the table consists of just customerId and LocationName

I need to select all customerIds where one of the locationNames = sheffield. So Id get 4 rows retruend with the cusotmer ID and the 4 locations

How do you write this query in mysql? Im guessing subselect but not too sure.

Shef · Accepted Answer · 2011-08-17 15:42:09Z

4

SELECT customerid FROM customers_location
WHERE customerid IN(SELECT DISTINCT customerid FROM customers_location WHERE LocationName = 'sheffield')

answered Aug 17, 2011 at 15:42

Shef

45.6k15 gold badges82 silver badges91 bronze badges

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

Comments

Pete855217 · Accepted Answer · 2011-08-17 15:43:55Z

2

Something like:

SELECT * FROM CUST_TABLE WHERE CUST_ID IN (
   SELECT DISTINCT CUST_ID FROM CUST_TABLE WHERE CUST_LOCN='Sheffield')

Note; The distinct clause may not be strictly necessary, not sure. That would give you eg. 4 records for customer xyz who has one of their listing locations as Sheffield, which I think is what you're asking.

answered Aug 17, 2011 at 15:43

Pete855217

1,6225 gold badges24 silver badges37 bronze badges

Collectives™ on Stack Overflow

mysql select problem

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related