CODEWITHSUNDEEP
sqlsql-server
migreva
migreva

Reputation: 876

Query for Many-To-One With AND

I have a table for a issue tracking web app that maps issues to ticket numbers. A given issue can have (hypothetically) an infinite amount of tickets mapped to it. I want to do a SQL Query that will get an issue that might possibly have multiple tickets attached to it.

For example:

issue_id | ticket_id
--------------------
   1     |   100
   1     |   101
   2     |   101

(SQL Fiddle: http://sqlfiddle.com/#!2/b3c34/3)

(A little background) I'm working on a new search page for this web app, and one of the search-able fields is ticket number. The user has the ability to specify any number of ticket numbers to search for.

So let's say a user is trying to find all issues numbers that have ticket numbers 100 and 101 mapped to them. The following SQL query returns no rows:

select issue_id 
from tickets
where ticket_id = 100 and ticket_id = 101;

Can someone point me in the right direction? Is there a way to do the above efficiently with one query? Note that there could possibly be hundreds of issue numbers returned in the query (the ticket numbers are just one small piece of a much larger query) so I want this to be as simple as possible.

Thanks.

Upvotes: 0

Views: 76

Answers (3)

juergen d
juergen d

Reputation: 204746

select issue_id 
from tickets
where ticket_id in (101,102)
group by issue_id
having count(distinct ticket_id) = 2

Upvotes: 1

KevenDenen
KevenDenen

Reputation: 1726

Another option:

select ticket100.issue_id
from (select issue_id from tickets where ticket_id = 100) as ticket100 
inner join (select issue_id from tickets where ticket_id = 101) as ticket101
on ticket100.issue_id = ticket101.issue_id

No clue where this lies on the performance meter, but it was what came to mind for me.

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32145

Your tickets table should presumably be a Junction Table. However...

select distinct issue_id
from tickets t
where exists (select 1 from tickets where ticket_id = 100 and issue_id = t.issue_id)
    and exists (select 1 from tickets where ticket_id = 101 and issue_id = t.issue_id);

Or:

select distinct issue_id
from tickets
where issue_id in (select issue_id from tickets where ticket_id = 100)
    and issue_id in (select issue_id from tickets where ticket_id = 101);

Will do what you're asking for. The latter is often easier to understand, but the former will often perform better.

You could also do this, but it's a set operation query and is often discouraged. However, the Execution plan on SQLFiddle indicates this is the best of the three.

select issue_id from tickets where ticket_id = 100
intersect
select issue_id from tickets where ticket_id = 101;

There's at least one other way to do it, too, with a self INNER JOIN.

Upvotes: 1

Related Questions