kerv kerv - 1 year ago 44
MySQL Question

How do i get IDs from multiple rows without using AND

I have a problem that I am not able to solve. I am still a beginner in SQL and have no clue on how i should solve this problem.

Here is an example

If my database were to look like this:

id tags_id clients_id

1 10 1
2 10 2
3 11 1
4 12 3

Now how would my query look like to be able to get the result of client_id 1?
I cannot use AND because since the rows are seperated it would not work. I have also tried using this query:

SELECT * FROM tags WHERE tags_id IN (10, 11);

This would return the client_id 1 AND 2 but i only want 1.

It basically has to look something like this but then working ofcourse:

SELECT * FROM tags WHERE tags_id = 10 and 11

and it has to return the clients_id 1
Any help would be appreciated.

kb. kb.
Answer Source

Your solution using IN is correct but you seem to have a syntactical error.

Try removing the single quotes surrounding the condition.

SELECT * FROM tags WHERE tags_id IN (10, 11);

With PHP code in the style of your example this would be:

$sql = 'SELECT * FROM tags WHERE tags_id IN ('.implode(',',$criteria['tagList']).');';

Note that the tags_id column name is not included in a quote.


One of the more simple approaches, assuming your table contains only unique relationships is to only keep the rows with client_ids that matches the same amount of times as the number of tags_id you have in your IN clause.

SELECT client_id, 
       COUNT(client_id) AS client_count
FROM adlibris_odladev.test 
WHERE tags_id IN (10, 11)
GROUP BY client_id
HAVING client_count = 2;

What we're doing here is counting how many times each client_id matches and using HAVING filter out all client_ids that do not exist 2 times in the result set.

To make this dynamic with PHP you want to replace the (10, 11) clause with an implode(',', $ids) like you had before and the 2 with a count($ids).