MySql, inner join query, must match multiple rows

Question

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.

Sergio Tulentsev · Accepted Answer · 2012-01-05 09:53:37Z

7

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

answered Jan 5, 2012 at 9:53

Sergio Tulentsev

231k43 gold badges381 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lemmy Over a year ago

Thanks - that looks good. If I have more than 2 rows, 5 for example, will this still work if I construct the query dynamically with five inner joins?

Sergio Tulentsev Over a year ago

@Lemmy: should work, yes. Although, if I were you, I would rethink my schema in this case :-)

Lemmy Over a year ago

Thanks Sergio, it works with the multiple inner joins. Agreed though that a better schema might be a good idea.

user359040 · Accepted Answer · 2012-01-05 10:24:08Z

0

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)

answered Jan 5, 2012 at 10:24

user359040

2 Comments

Lemmy Over a year ago

How might you express this if the column values were different? e.g. (vlrc.row = 1 AND vlrc.columns = 2) (vlrc.row = 2 AND vlrc.columns = 5)

user359040 Over a year ago

Amend the where clause on the inline view accordingly - eg. (row = 1 AND columns = 2) or (row = 2 AND columns = 5).

Collectives™ on Stack Overflow

MySql, inner join query, must match multiple rows

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related