I'm looking for a simple query

Databases
1

Need some, help
did somme google and testing

here’s a table :

 
product_id	section_id  product_type_id	brand_id
1		2		5		12
2		4		6		13
3		3		7		12
4		4		5		13
5		3		8		12

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

I’d like a sql only solution

ideas ?
tx
2 

SELECT DISTINCT brand_id FROM your_table WHERE section_id IN (3,4)

section_id IN (3,4) is the same as section_id=3 OR section_id=4
SELECT DISTINCT means SELECT, and remove all duplicates.

So for this query without using DISTINCT you would get 13,12,13,12
With distinct you get 13,12

Of course you can add an ORDER BY brand_id to sort the results according to brand_id.

When using PHP you can do something like


$section_ids = array(3,4);
$result = mysql_query('SELECT DISTINCT brand_id FROM your_table WHERE section_id IN ('.implode(',', $section_ids).')');

Does that answer your question?

3

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

4

Could you post a SHOW CREATE for both tables and maybe some dummy data? I’m not entirely sure what you’re trying to achieve.

5

requested info for helping me

dev.fourmi-integree.com/notes

tx

6

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.

7

okay !
I will test this today,
I’have never used “Group By”,
I’m lurning somthing here,
I’m very greatful.