CODEWITHSUNDEEP
phpmysql
Karthik Nk
Karthik Nk

Reputation: 377

Searching over multiple rows mysql

I have two tables projects and projects_meta

   Projects
    --------------------
    id  name
    --------------------
    1   A    
    2   B
    3   C

projects_meta
------------------------------------------------------------
id    project_id   additional_field  additional_value
------------------------------------------------------------
1     1            verified_by       Erik
2     1            approved_by       Dave
3     2            verified_by       Riyaj
4     2            approved_by       Mike
5     3            verified_by       Erik
6     3            approved_by       Dave

Now i want the output where joining both tables to find out what are the projects verified by Erik and approved by Dave

SELECT * 
FROM projects a 
INNER JOIN projects_meta b 
ON a.id = b.project_id 
WHERE b.additional_field= 'verified_by' 
AND b.additional_value = 'Erik' 
AND b.additional_field= 'approved_by' 
AND b.additional_value = 'Dave'

The Above query seems to work with a single condition but when there is more it returns empty result.

Thanks in advance

Upvotes: 0

Views: 40

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You need to JOIN twice to projects_meta table:

SELECT * 
FROM projects a 
INNER JOIN projects_meta b ON a.id = b.project_id 
INNER JOIN projects_meta c ON a.id = c.project_id 
WHERE b.additional_field= 'verified_by' AND b.additional_value = 'Erik' AND 
      c.additional_field= 'approved_by' AND c.additional_value = 'Dave'

The above query returns all projects being linked both to 'verified_by Erik' AND 'approved_by Dave' records.

Upvotes: 3

Related Questions