I need a smart select query, help on this one.

[csueiras]

Greetings,

 

I have a table with only two columns, a ctd_id and a dt_id. The ctd_id is the key that is auto_incremented. What happens is that I need a select that is able to give me a list of all the dt_id that are common to a certain range of ctd_id's. Like, lets suppose I want all the dt_id's that ctd_id=1, ctd_id=20 and ctd_id=34 have in common.

 

Right now I have this query:

select DISTINCT dt_id from ctd_data where ctd_id='1' OR ctd_id='2' OR ctd_id='4'

(I want the common dt_ids to ctd_id=1, ctd_id=2 and ctd_id=4)

 

The problem with that query is that it gives me all the dt_id's that those rows have, except they are not repeated. Any help on this would be greatly appreciated.

 

 

Christian

[pocobueno1388]

They are not being repeated because you are using DISTINCT.

 

Try

SELECT dt_id from ctd_data where ctd_id IN(1, 2, 4)

[csueiras]

Yeah, I don't want them repeated. Maybe I didnt explain the problem correctly. The problem is it gives me dt_id's that are not common TO ALL ROWS in the range I picked.

[Barand]

Frankly, I haven't a clue what you want.

 

What does your data look like?

 

Show us a sample and what you expect the query to produce from that sample.

[luca200]

You have to self join the table, but it's not that simple, as the query you need relies on how many ctd_id's values you choose.

This should work for the case you posted:

select distinct a.dt_id from ctd_data a join ctd_data b on (a.dt_id = b.dt_id and a.ctd_id='1' and b.ctd_id='2') join ctd_data c on (a.dt_id = c.dt_id and a.ctd_id='1' and c.ctd_id='4')

The matter is if you have less or more than 3 values, this query doesn't fit