CODEWITHSUNDEEP
mysqlsqlphpmyadmin
user1629986
user1629986

Reputation: 51

Query in MYSQL to get the results for two values coming from another query

SELECT l.name 
FROM locations as l 
WHERE
    l.location_id =',2,32,'

the ",2,32," values we get from another table. This query gives zero result, although there are values with location_id=2 and also for 32. Please help me how to use ",2,32," to get result?

Upvotes: 1

Views: 109

Answers (3)

Mr. Alien
Mr. Alien

Reputation: 157334

Assuming that you want to return values for ID 2 and 32 you can use this query:

SELECT l.name FROM locations AS l WHERE l.location_id IN (2, 32);

IN operator allows you to specify multiple-values in the WHERE clause

You can also use NOT IN to eliminate certail ID's and you can also add more id's to it just by separating them with a comma..

Upvotes: 1

Vikdor
Vikdor

Reputation: 24124

If your location_id column is a VARCHAR column that contains comma-separated values, then your WHERE clause should be 

WHERE 
    (l.location_id LIKE '2,32%' OR l.location_id LIKE '%,2,32%' OR l.location_id LIKE '%,2,32')

But if your location_id column is a NUMERIC column, then your intention to get rows whose location_id is either 2 or 32 should be addressed by 

WHERE location_id IN (2, 32)

Upvotes: 0

gcochard
gcochard

Reputation: 11744

You'll want to change your where clause to WHERE l.location_id LIKE '%2,32%'.

It allows for more than just 2 and 32 to be in the location id.

Another suggestion is to use one row per location_id. This is called normalization and it is important to understand why to use it. Yes it duplicates some data, but it will make your query easier. You can then do WHERE l.location_id IN (2,32).

EDIT:

I just re-read your question and it appears your locations table is already normalized. You'll just want to use the second WHERE clause I posted.

Upvotes: 1

Related Questions