MySql, an internal connection request, must match multiple lines

Question

I am making a MySQL query where I want to get the identifier, but only if I find a match for it on all the lines that I specified in the query.

Table: view_layout_rows ID owner rows ___________________ 49 1 2 50 1 2 Table: view_layout_rows_columns ID row columns ___________________ 49 1 5 49 2 4 50 1 5 50 2 5 SELECT vlr.id FROM view_layout_rows vlr INNER JOIN view_layout_rows_columns vlrc ON vlr.id = vlrc.id WHERE vlr.rows = 2 AND (vlr.owner = 0 OR vlr.owner = 1) 

And all the following conditions must be met:

 (vlrc.row = 1 AND vlrc.columns = 5) (vlrc.row = 2 AND vlrc.columns = 5) 

Only the identifier 50 needs to be returned. 49 must NOT be returned, since it satisfies only one of the last two sentences.

How can i do this? (Please note that I asked this question earlier, but my requirement was unclear. Second attempt.) Thank you in advance for any suggestions.

Answer 1

Double joining salvation !:-)

 SELECT vlc.* FROM view_layout_rows vlc INNER JOIN view_layout_rows_columns vlrc1 ON vlrc1.id = vlc.id INNER JOIN view_layout_rows_columns vlrc2 ON vlrc2.id = vlc.id WHERE vlrc1.row = 1 AND vlrc1.columns = 5 AND vlrc2.row = 2 AND vlrc2.columns = 5 /* imported from original query */ AND vlr.rows = 2 AND (vlr.owner = 0 OR vlr.owner = 1); 

Answer 2

Single access to each table:

 select r.* from view_layout_rows r join (select id, count(*) rec_count from view_layout_rows_columns where row in (1,2) and columns = 5 group by id having count(*) = 2) c on r.id = c.id where r.rows = 2 and r.owner in (0,1)