CODEWITHSUNDEEP
mysqlsqlinner-join
Lemmy
Lemmy

Reputation: 399

MySql, inner join query, must match multiple rows

I am making a MySQL query where I want to retrieve an ID but only if I find a match for it in all the rows which I specify 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 of the following conditions should be satisfied:

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

Only ID 50 should be returned. 49 should NOT be returned as it only satisfies the first of the final two clauses.

How might I go about this? (Please note, I asked this question previously but my requirement was unclear. Second attempt.) Thanks in advance for any suggestions.

Upvotes: 4

Views: 7767

Answers (2)

user359040
user359040

Reputation:

Single access of 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)

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230336

Double join to the rescue! :-)

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);

Upvotes: 7

Related Questions