There must be a better way…

[jkon]

There must be a better way…

Hello all, I am now designing a new data structure and I faced this problem that I am going to describe making me think that maybe I should redesign it. Maybe the solution is in front of my eyes, and please if you have any ideas help me figure this out.

Lets say that we have a table persons_records
(table and data in this example made as simple as possible)

ID 	PERSON_ID 	RECORD_ID
1 	1 		1
2 	1 		2
3 	2 		3
4 	2 		2
5 	2 		1
6 	3 		1
7 	3 		3

What I want is to get all the PERSON_ID s that have for example the RECORD_ID 1 and the RECORD_ID 2

The solution I came up is

SELECT a.PERSON_ID FROM persons_records as a JOIN persons_records as b WHERE a.RECORD_ID = 1 AND b.RECORD_ID = 2 AND a.PERSON_ID = b.PERSON_ID GROUP BY a.PERSON_ID

And it works but … I can’t stop thinking that there must be a better (and maybe more generic) way…

Thank you in advance for your ideas.

[smantscheff]

select distinct person_id from persons_records
where person_id in (select person_id from persons_records where record_id=1)
and person_id in (select person_id from persons_records where record_id=2);

or, this being mySQL:

select person_id, group_concat(record_id) as r 
from persons_records 
having locate(1,r) and locate(2,r)

But your code is fine, though.

[diafol]

@smant - loved the last query, but couldn't get it to work:

with the OP data, it only gives person_id 1 - expected person_id 2 as well. I would imagine that this would be quick?

I got this to work:

SELECT person_id, COUNT(person_id) AS r
FROM persons_records WHERE record_id = 1 OR record_id = 2
GROUP BY person_id
HAVING r > 1

Took average 0.0012 to run vs. 0.0018 on subquery example. Perhaps that would change with a bigger table?

[smantscheff]

Of course: in my 2nd query the group by clause was missing:

select person_id, group_concat(record_id) as r 
from persons_records 
group by person_id
having locate(1,r) and locate(2,r)

[diafol]

//sorry edited my post while you posted :(

??EDIT - nice one smant - it's about as quick as my query (my tests anyway), but yours is much cleaner I think. :)

[smantscheff]

@ardav: Your solution is fine if (PERSON_ID, RECORD_ID) is unique, which we cannot know from the sample data. But I guess your implicit assumption is correct.

[jkon]

Yes, the combination of PERSON_ID and RECORD_ID is unique. Thank you for your answers. I tested all queries and the faster result was from the original one (If I am wrong and this is not the case to big amount of data please correct me). But I will stick to ardav suggestion (time difference is really small) , because I consider it cleaner then my original query and I can make it more generic.

e.g.

SELECT person_id, COUNT(person_id) AS r
    FROM persons_records WHERE record_id IN (1,2)
    GROUP BY person_id
    HAVING r = 2

And ...

SELECT person_id, COUNT(person_id) AS r
    FROM persons_records WHERE record_id IN (1,2,3)
    GROUP BY person_id
    HAVING r = 3

Thank you also smantscheff since your queries introduced me to ways I am not yet familiar with.

[diafol]

Thank you also smantscheff since your queries introduced me to ways I am not yet familiar with.

Here, here. I learned some new stuff too - the LOCATE stuff - never used that before - like it a lot.