CODEWITHSUNDEEP
mysqlsql
Ayrton
Ayrton

Reputation: 2303

MySQL query return all groups where value is part of the group

Suppose I have a table contract_items. My contract_items table has the fields item_id and contract_id .

I need to build a query that gets all items from the table with the same contract_id as a given item, returning their item_ids together in a group_concat. The query would have something like a where in clause, but in reverse, since I need the entire group based on a single id.

Some thing like:

...
where item_id in item_ids

How would I do that? The only thing I can think of is subquerying, but that might not scale well.

What I have accomplished so far:

select group_concat(i.item_id SEPARATOR ', ')
from contract_items i
where i.contract_id = (
    select contract_id
    from contract_items
    where item_id = itemnum
)

How can I make that query better (assuming itemnum is the given item)?

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use a having clause:

select i.contract_id, group_concat(i.item_id SEPARATOR ', ')
from contract_items i
group by i.contract_id
having sum( i.item_id = @itemnum ) > 0;

For performance, you might find a subquery better:

select i.contract_id, group_concat(i.item_id SEPARATOR ', ')
from contract_items i
where exists (select 1
              from contract_items i2
              where i2.contract_id = i.contract_id and
                    i2.item_id = @itemnum 
             )
group by i.contract_id;

This filters before the aggregation (the performance gain) and can use an index on contract_items(contract_id, item_id).

Upvotes: 2

Related Questions