CODEWITHSUNDEEP
mysqlsqlselectleft-joinwhere-clause
XTRUST.ORG
XTRUST.ORG

Reputation: 3402

mysql query request from two tables with IN clause

I have mysql tables:

1. ms_fields

id name

1  Color
2  Gender

2. ms_fields_values

id fieldsid     value   ordering

1   1            White   0
2   1            Black   1
3   1            Orange  2
4   1            Green   3
5   1            Blue    4
6   2            Male    0
7   2            Female  1

3. ms_conn_products_to_fields_values

productid   fields_values_id

    9       5
    9       7
    10      5
    10      6
    11      2
    11      7
    12      1
    12      7

I have a query:

SELECT V.id, V.fieldsid, V.value, V.ordering, COUNT(P.productid) AS count
FROM `ms_fields_values` V
LEFT JOIN `ms_conn_products_to_fields_values` P ON P.fields_values_id = V.id
WHERE V.fieldsid =1
AND P.productid
IN (9, 11, 12)
GROUP BY V.id
ORDER BY `ordering` ASC

This query will return me: 

id  fieldsid    value   ordering count

1   1           White   0         1
2   1           Black   1         1
5   1           Blue    4         1

How can I change my request to get this data:

id  fieldsid    value   ordering count

1   1           White   0         1
2   1           Black   1         1
*3   1           Orange  2             0* // row needed
*4   1           Green   3             0* // row needed
5   1           Blue    4         1

Upvotes: 2

Views: 50

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT V.id, V.fieldsid, V.value, V.ordering, COUNT(P.productid) AS COUNT
FROM `ms_fields_values` V
LEFT JOIN `ms_conn_products_to_fields_values` P ON P.fields_values_id = V.id AND P.productid IN (9, 11, 12)
WHERE V.fieldsid =1
GROUP BY V.id
ORDER BY `ordering` ASC

Upvotes: 1

Related Questions