NATURAL JOIN - 3 tables - multiple same field selected

gtk · #1 (**permalink**) 10-29-04, 05:15

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.

r937 · #2 (**permalink**) 10-29-04, 07:31

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

gtk · #3 (**permalink**) 10-29-04, 08:39

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

r937 · #4 (**permalink**) 10-29-04, 09:08

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

gtk · #5 (**permalink**) 10-29-04, 10:07

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?

r937 · #6 (**permalink**) 10-29-04, 11:13

upon further testing (thanks for your script, by the way), i don't think this can be done in one query

gtk · #7 (**permalink**) 10-29-04, 11:59

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.

r937 · #8 (**permalink**) 10-29-04, 12:33

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

Pat Phelan · #9 (**permalink**) 10-29-04, 13:18

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

r937 · #10 (**permalink**) 10-29-04, 14:46

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

gtk · #11 (**permalink**) 11-02-04, 04:21

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)

r937 · #12 (**permalink**) 11-02-04, 06:34

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

gtk · #13 (**permalink**) 10-24-05, 11:05

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

r937 · #14 (**permalink**) 10-24-05, 11:56

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?

gtk · #15 (**permalink**) 10-25-05, 03:33

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

Internet Services

Web Development

Digital Marketing

Consumer Tech