Query with multiple EXIST

Question

I have a database of rooms and equipment. I want to query the database and return a list of rooms, for example. TV, radio, sat and refrigerator (eq1, eq2, eq3, ...., eqN).

I have the following SELECT statement:

select * from rooms r where 
exists (select id from equipments where eq_id='eq1' and room_id=r.id)
and
exists (select id from equipments where eq_id='eq2' and room_id=r.id)
and
exists (select id from equipments where eq_id='eq3' and room_id=r.id)
.......
and
exists (select id from equipments where eq_id='eqN' and room_id=r.id)

Is there a way to optimize or make it shorter?

Answer 1

To cut back, you could

select * 
from rooms r 
where @N = (select count(distinct eq_id) 
            from equipments 
            where eq_id IN ('eq1','eq2',...,'eqN') and room_id=r.id)

CHANGE but not sure if this will actually accelerate ... quite the opposite, the version with EXISTS AND EXISTS has the ability to trim the execution branch on the first false value, this should actually count the various values ​​(go through all the records) and see what that value is .

So you should think that faster:

• , ( )
• N ( ), ( )

( , , , , , , EXISTS , , , , .. )

GROUP BY

select r.* 
from rooms r join
     equipments e on r.id = e.room_id
group by r.id
where eg_id in ('eq1','eq2',...,'eqN')
having count(distinct e.eq_id) = @N

( SQL )

Answer 2

( - , , )

select * from
    rooms r,
    (
        select count(distinct id) as cnt, id from equipments  where eq_id in ('eq1','eq2') group by id
    ) as sub
where sub.id = r.id 
and sub.cnt >= 2 'Options count

: 2 - . : "eq1", "eq2"

Answer 3

select * from rooms r where 
(select count(id) from equipments where eq_id='eq1' and room_id=r.id) > 0
and
...

Answer 4

Use a stored procedure.

here is the procedure for mysql:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `GetRooms`(IN roomtable TEXT, IN equipmenttable TEXT, IN equipments TEXT )
BEGIN
    DECLARE statement text;
    DECLARE Pos int;
    DECLARE cond text;
    DECLARE element text;   
    DECLARE tmpTxt text;   

    set tmpTxt = equipments;
    set cond = "";
    set Pos = instr(tmpTxt,';');
    while Pos <> 0 do
    set element = substring(tmpTxt, 1, Pos-1);

    if cond <> "" then
        set cond = concat(cond,' and ');
    end if;

    set cond = concat(cond,'exists (select id from ' , equipmenttable ,' where eq_id=''' ,  element ,''' and room_id=r.id) ');


    set tmpTxt = replace(tmpTxt, concat(element,';'), '');


    set Pos = instr(tmpTxt,';');
    end while; 

    if tmpTxt <> "" then
        if cond <> "" then
            set cond = concat(cond,' and ');
        end if;

        set cond = concat(cond,'exists (select id from ' , equipmenttable ,' where eq_id=''' ,  tmpTxt ,''' and room_id=r.id) ');
    end if;

    SET @statement = concat('Select * FROM ' ,  roomtable , " WHERE " , cond , ";");

    PREPARE stmt FROM @statement;
    EXECUTE stmt;
END

Run it with: CALL GetRooms('RoomTableName','EquipmentTableName','EquipmentIDs')

Example:

Call GetRooms('rooms','equipemnts','eq1;eq2;eq3');

Hope this helps.