Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: JOIN - 3 tables - multiple same field selected

    MySQL 4.0.18

    Hello

    Simplifying my problem:
    I have 3 tables.

    Code:
    user_master
    ----------
    int    user_id
    char   user_name
    
    product_master
    ----------
    int    product_id
    char   product_name
    int    product_category
    
    user_product
    ----------
    int    user_id
    int    product_id
    Of course the users can own multiple products
    I need to ouput for each of them the number of products of each
    category
    For example:

    Code:
    User      | Chair    | Table    | Desk     
    ----------+----------+----------+-----------
    Dupont    |        2 |        3 |        1
    Dupuy     |        1 |        0 |        3
    Martin    |        0 |        1 |        2
    I'm on this way:

    Code:
    SELECT
      usr.user_name 'Nom',
      count(up1.product_id) 'Chaise',
      count(up2.product_id) 'Table',
      count(up3.product_id) 'Bureau',
      pr.product_name 'Produit'
    FROM
      user_master usr
      NATURAL LEFT JOIN user_product up1
      NATURAL LEFT JOIN user_product up2
      NATURAL LEFT JOIN user_product up3
      NATURAL LEFT JOIN product_master pr
    WHERE
      up1.product_category = 1 AND
      up2.product_category = 2 AND
      up3.product_category = 3
    GROUP BY Nom
    ORDER BY Nom
    That doesn't seems to be complex, but this query gives me an empty
    result.
    In fact, I do understand the principal of the JOIN but I mainly
    don't understand his results.

    Thx in advance.
    Last edited by gtk; 10-24-05 at 12:10.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm guessing the problem is the NATURAL JOINS

    when you say

    ... FROM user_master usr NATURAL LEFT JOIN user_product up1

    this one is easy, it will join these two tables based on the identically-named columns in each table, i.e. user_id

    but when you then go on to add

    ... NATURAL LEFT JOIN user_product up2

    how is it going to join this table to the first two? by the identically-named columns, which happen to be both user_id and product_id

    which of course will produce erroneous results

    this is one reason why i recommend to people never to use NATURAL JOIN

    it's too easy for you to get totally unintended results (in this case, nothing)

    another problem i see with your query is that you are joining to three different user_product rows, ostensibly for three different products (based on the WHERE clause), and yet you are joining to the product_master only once

    i think you should just skip joining to the product_master

    so here's your query the way i would write it --
    Code:
    select usr.user_name   as Nom
         , count(up1.product_id) as Chaise
         , count(up2.product_id) as Table
         , count(up3.product_id) as Bureau
      from user_master usr
    left outer
      join user_product up1
        on usr.user_id = up1.user_id
       and up1.product_category = 1 
    left outer
      join user_product up2
        on usr.user_id = up2.user_id
       and up2.product_category = 2 
    left outer
      join user_product up3
        on usr.user_id = up3.user_id
       and up3.product_category = 3
    group 
        by Nom
    order 
        by Nom
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Ok I wondered why nobody used NATURAL JOIN
    I have never understood the difference between outer, inner ... and stuff like that. (but I'd like to)
    Your query seems closer that I'm searching for, but there is still a little problem:
    The product_category is in product_master and not in user_product
    as this message say to me:

    Code:
    #1054 - Unknown column 'up1.product_category' in 'on clause'
    How can I do to link this third table product_master on this query?

    Great thx for your help
    Last edited by gtk; 10-29-04 at 10:02.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select usr.user_name   as Nom
         , count(up1.product_id) as Chaise
         , count(up2.product_id) as Table
         , count(up3.product_id) as Bureau
      from user_master usr
    left outer
      join (
           user_product up1
        on usr.user_id = up1.user_id
    inner
      join product_master pm1
        on up1.product_id
         = pm1.product_id
       and pm1.category = 1 
           )
    left outer
      join (
           user_product up2
        on usr.user_id = up2.user_id
    inner
      join product_master pm2
        on up2.product_id
         = pm2.product_id
       and pm2.category = 2
           )
    left outer
      join (
           user_product up3
        on usr.user_id = up3.user_id
    inner
      join product_master pm3
        on up3.product_id
         = pm3.product_id
       and pm3.category = 3
           )
    group 
        by Nom
    order 
        by Nom
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    hum .... sorry
    That still doesn't work
    (You can find the enclosed db 'test.txt' that I'm testing on)

    Code:
    SELECT
      usr.user_name AS 'Nom',
      count(up1.product_id) AS 'Chaise',
      count(up2.product_id) AS 'Table',
      count(up3.product_id) AS 'Bureau'
    FROM
      user_master usr
      LEFT OUTER JOIN (
        user_product up1 ON (usr.user_id=up1.user_id)
        INNER JOIN product_master pm1 ON (up1.product_id=pm1.product_id AND pm1.category=1)
      )
      LEFT OUTER JOIN (
        user_product up2 ON (usr.user_id=up2.user_id)
        INNER JOIN product_master pm2 ON (up2.product_id=pm2.product_id AND pm2.category=2)
      )
      LEFT OUTER JOIN (
        user_product up3 ON (usr.user_id=up3.user_id)
        INNER JOIN product_master pm3 ON (up3.product_id=pm3.product_id AND pm3.category=3)
      )
    GROUP 
      BY Nom
    ORDER 
      BY Nom
    I tried that

    That gives me
    Code:
    #1064 - You have an error in your SQL syntax.
    Check the manual that corresponds to your MySQL server version for the right syntax to use near
    'ON ( usr.user_id = up1.user_id )
    INNER  JOIN product_master pm
    Are you sure of the parentheses positions?
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    upon further testing (thanks for your script, by the way), i don't think this can be done in one query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    astonishing
    Do you think it is a complicated request?
    I think there are other persons who may need this
    I have to do this in one query because I have a big php page which generate a big sql query returning a big result to be displaied a big table.
    (In fact the page in question is a dedicated online visual query builder)
    It will make the code too eavy if I a add another wrapper to keep the dynamism of my query builder.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Do you think it is a complicated request?

    the data is not too complex, however, your desire to denormalize three different quantities onto the same row make it so

    I have to do this in one query because I have a big php page which generate a big sql query returning a big result to be displaied a big table.

    if you would accept the results in a different format, a UNION query might (no guarantee) do it in one query
    Last edited by r937; 10-29-04 at 13:35.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Could you use:
    Code:
    SELECT Count(*), user_id, product_category
       FROM product_master AS pm
       INNER JOIN user_product AS up
          ON (up.product_id = pm.product_id)
       GROUP BY up.user_id, pm.product_category
    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, you could, pat, but it wouldn't give you the originally requested layout, it wouldn't give you the user names, it wouldn't give you the correct counts (zeros would be conspicuously absent), and it might give you more than the threee desired categories...

    but sure, you could do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Indeed I can't use this format because I would like to have not to rework the result object.
    I think that the UNION is not a bad solution
    Can I use it like that?
    Code:
    (select usr.user_name as 'Nom'
         , count(up.product_id) as 'Chaise'
      from user_master usr
    left outer
      join user_product up
        on usr.user_id = up.user_id
    left outer
      join product_master pm
       on pm.product_category = 
    group
        by Nom)
    UNION
    (select usr.user_name as 'Nom'
         , count(up.product_id) as 'Table'
      from user_master usr
    left outer
      join user_product up
        on usr.user_id = up.user_id
    left outer
      join product_master pm
       on pm.product_category = 2
    group
        by Nom)
    .
    .
    .
    order
        by Nom
    How can I combine these queries on one specified field (actually user_name)
    Last edited by gtk; 11-02-04 at 07:14.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you would have to be on version 4.0 in order to use UNION

    you would have to be on version 4.1 in order to combine the separate rows of the results into one row per user by making the UNION a subquery in an outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    By the way, this should work better.
    Code:
    SELECT
      usr.user_name AS "Nom",
      COUNT(IF(pm.product_category = 1), up.product_id, 0) AS "Chaise",
      COUNT(IF(pm.product_category = 2), up.product_id, 0) AS "Table",
      COUNT(IF(pm.product_category = 3), up.product_id, 0) AS "Bureau"
    FROM
      user_master AS um
      JOIN user_product AS up ON (um.user_id = up.user_id)
      LEFT JOIN product_master AS pm ON (up.product_id = pm.product_id)
    GROUP BY Nom
    ORDER BY Nom

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gtk, your COUNTS will return exactly the same number for each column

    if you count 937 zeroes, or 937 ids, or any combination of ids and zeroes of which there are 937, the answer will always be 937

    also, be careful of using pm columns in an outer join where pm is the right table


    by the way, how come it took you a full year to come back to this query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    I rediscovered my question searching something on google
    and with my last year of mysql experience, I have the impression that it was somebody else who asked this question
    In fact I make a mistake, I was more thinking about something like that.
    Code:
    SELECT
      usr.user_name AS "Nom",
      SUM(IF(pm.product_category = 1), 1, 0) AS "Chaise",
      SUM(IF(pm.product_category = 2), 1, 0) AS "Table",
      SUM(IF(pm.product_category = 3), 1, 0) AS "Bureau"
    FROM
      user_master AS um
      JOIN user_product AS up ON (um.user_id = up.user_id)
      LEFT JOIN product_master AS pm ON (up.product_id = pm.product_id)
    GROUP BY Nom
    ORDER BY Nom

Posting Permissions

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