I need to select contacts

1. where field "email" = value "email1" OR field "name" = value "name1"

2. where field "email" = value "email1" AND field "name" = value "name1"

I have solution SQL query for first condition OR

SELECT v.contact_id 
FROM values v
INNER JOIN fields f ON f.id = v.field_id
INNER JOIN contacts c ON c.id = v.contact_id
WHERE (f.tag = 'email' AND v.value = 'email1' AND f.listing_id = 1) 
   OR (f.tag = 'name' AND v.value = 'name1' AND f.listing_id = 1)

But if I change OR in the beginning of fifth line to AND it of course won't work

SELECT v.contact_id 
FROM values v
INNER JOIN fields f ON f.id = v.field_id
INNER JOIN contacts c ON c.id = v.contact_id
WHERE (f.tag = 'email' AND v.value = 'email1' AND f.listing_id = 1) 
  AND (f.tag = 'name' AND v.value = 'name1' AND f.listing_id = 1)

Please help me to improve this query to work in both options, i will be very grateful for help with solving this problem (mysql db)

Database scheme: https://i.imgur.com/I5J2hh0.png

Database values:

Table groups

id | name
--- --------
1  | group1

Table contacts

id | group_id
--- ----------
1  | 1
2  | 1

Table fields

id | group_id | tag
--- ---------- --------
1  | 1        | email
2  | 1        | name
3  | 1        | email
4  | 1        | name

Table values

id | contact_id | field_id | value
--- ------------ ---------- ----------
1  | 1          | 1        | email1
2  | 1          | 2        | name1
2  | 2          | 3        | email2
4  | 2          | 4        | name2

CodePudding user response：

Fot the second query (AND), try this query :

SELECT a.contact_id
  FROM 
     ( SELECT v.contact_id
         FROM values v
        INNER JOIN fields f
           ON f.id = v.field_id
        INNER JOIN contacts c
           ON c.id = v.contact_id
        WHERE (f.tag = 'email' AND v.value = 'email1' AND f.listing_id = 1)
     ) AS a
 INNER JOIN 
     ( SELECT v.contact_id
         FROM values v
        INNER JOIN fields f
           ON f.id = v.field_id
        INNER JOIN contacts c
           ON c.id = v.contact_id
        WHERE (f.tag = 'name' AND v.value = 'name1' AND f.listing_id = 1)
     ) AS b
    ON a.contact_id = b.contact_id

or this simpler query :

SELECT v.contact_id
  FROM values v
 INNER JOIN fields f
    ON f.id = v.field_id
 INNER JOIN contacts c
    ON c.id = v.contact_id
 WHERE (f.tag = 'email' AND v.value = 'email1' AND f.listing_id = 1)
    OR (f.tag = 'name' AND v.value = 'name1' AND f.listing_id = 1)
 GROUP BY v.contact_id
HAVING count(*) > 1