I’m looking for a simple query that would give me this result :
the brand_id’s that belong to all section_id for some specific section_id
if
section_id = 4,3
then
the result would be
brand_id = 12,13
subquery does not give me the result
my only solution for now is
select all from table where
section_id = a
or
section_id = b
or
section_id = c
or
product_type_id = z
and then
parsing the array with php to remove
brand_id that do not intersect
here is the actual query ( the abreviation are not for english )
SELECT DISTINCT ms.id_g, mg.name
FROM (y_marques_s AS ms)
JOIN y_marques_g AS mg ON ms.id_g = mg.id
WHERE ms.section IN (5,6,16,12) ORDER BY mg.name ASC
this is no good
more there is section value
more row will it be in the result
no good
the id of brand (ms.id_g) must be in all section to be returned
the chance that a brand appears is less whne the number of section get biger
Would you like the brands common to each section specified? If so the methodology would be outlined in the below query.
SELECT
b.id
,b.name
FROM
sections s
INNER
JOIN
brands b
ON
s.id = b.section_id
WHERE
s.id IN (5,6,16,12)
GROUP
BY
b.id
HAVING
COUNT(*) = 4
I attempted to translate that to your schema but the language barrier is proving to be an obstacle.
SELECT
ms.id_g
,mg.name
FROM
y_marques_s ms
INNER
JOIN
y_marques_g mg
ON
ms.id_g = mg.id
WHERE
ms.section IN (5,6,16,12)
GROUP
BY
mg.id
HAVING
COUNT(*) = 4
The having clause provides the necessary filter to remove all brands that don’t belong to every section. Just make certain that the comparison is against the number of sections specified in the in condition.
If the relationship between sections and brands is m:n you will need to replace COUNT(*) = 4 with COUNT(DISTINCT ms.section) = 4.