Mysql OR/AND query

Question

I developed a tag system with PHP and MySql. Websites (table 1) are associated to many tags (table 2) in a third table (table 3):

table1:

! ----- ! -----------------!
! t1_id ! t1_libelle       ! 
! ----- ! ---------------- !
! 1     ! Site 1           ! 
! ----- ! ---------------- !
! 2     ! Site 2           !

table2:

! t2_id ! tag             ! 
! ----- ! ----------------!
! 3     ! Référencement   ! 
! ----- ! ----------------!
! 4     ! Linking         ! 
! ----- ! ----------------! 
! 5     ! HTML            !

table3:

! t1_id ! t2_id     ! 
! ----- ! ----------!
! 1     ! 3         ! 
! ---- ! ---------- !
! 1    ! 4          ! 
! ---- ! ---------- ! 
! 2    ! 4          ! 
! ---- ! ---------- !
! 2    ! 5          !

My query (below) displays websites that have the tags 'Référencement' or 'Linking', so sites 1 and 2 are displayed, but this is not what I need. I would like to display websites having tags 'Référencement" and 'Linking' both (so here the 'site 1' only):

SELECT t1.* 
FROM table1 t1, table3 t3
WHERE t1.t1_id = t3.t1_id 
AND (t3.t2_id = 3 OR t3.t2_id = 4)

I hope I am being clear, sorry I am not fluent in English.

Thank in advance for your help.

Possible duplicate of [Mutiple criteria in 1 query](http://stackoverflow.com/questions/2057439/mutiple-criteria-in-1-query) — Matt Raines, Jun 07 '16 at 16:13
You can use the or, but group by the "website" and then count that the number of criteria matches the or list's length. _Also, using actual JOIN syntax is much much cleaner, clearer, and considered standard._ — Uueerdo, Jun 07 '16 at 16:31

score 3 · Answer 1 · answered Jun 07 '16 at 16:12

3

You can do this with group by and having (or other methods as well). More importantly, learn to use proper join syntax. Simple rule: Never use commas in the from clause.

select t1.*  -- this is fine, assuming that `id` is unique in `t1`
from table3 t3 join
     table1 t1
     on t3.t1_id = t1.id join
     table2 t2
     on t3.t2_id = t2.id
where t2.tag in ('Référencement', 'Linking')
group by t1.id
having count(*) = 2;

answered Jun 07 '16 at 16:12

Gordon Linoff

1,242,037
58
646
786

1

In the more general case, if there's not a unique constraint on (id,tab) in t2.. i.e. if it's possible for there to be two rows with tag value of 'Linking', then we'd want something like... **`COUNT(DISTINCT t2.tag) = 2`** – spencer7593 Jun 07 '16 at 16:16

Brian Driscoll · Answer 2 · 2016-06-07T16:17:11.713

1

You will need to join to table 3 multiple times to accomplish what you are looking for:

SELECT t1.*
FROM table1 t1
JOIN table3 t3_first
ON t1.id = t3_first.t1_id
AND t3_first.t2_id = 3
JOIN table3 t3_second
ON t1.id = t3_second.t1_id
AND t3_second.t2_id = 4

Only rows from table1 that satisfy both join conditions will be returned in the result set, which is what I believe you want.

edited Jun 07 '16 at 16:17

answered Jun 07 '16 at 16:12

Brian Driscoll

19,373
3
46
65

Mysql OR/AND query

2 Answers2