SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I'm looking for a simple query

    Need some, help
    did somme google and testing

    here's a table :
    HTML Code:
    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. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,096
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Code SQL:
    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

    PHP Code:
    $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. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,096
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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. #5
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    requested info for helping me

    dev.fourmi-integree.com/notes

    tx

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Would you like the brands common to each section specified? If so the methodology would be outlined in the below query.

    Code SQL:
    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.

    Code SQL:
    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. #7
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay !
    I will test this today,
    I'have never used "Group By",
    I'm lurning somthing here,
    I'm very greatful.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •