[RESOLVED] join query prob

perpetualshaun

Here's my starting query:

SELECT shaun_users.response_demo002, count( * )
FROM shaun_users
LEFT JOIN shaun ON shaun_users.response_pcode = shaun.response_pcode
WHERE shaun.response_pcode < '999990'
AND response_completed_on != ''
GROUP BY shaun_users.response_demo002
ORDER BY shaun_users.response_demo002

That gives me results like this:

| response_demo002 | count( * ) |

| F | 99 |

| M | 22 |

BEAUTY!! However, the F and M actually correspond to the full values in another table. I know that there isn't much use for this example, but ideally I'll use this same query to print ALL the other demographic information as well. So, how can I add in another join to get the values for F and M from the shaun_answers table? I tried this:

SELECT shaun_users.response_demo002, count( * ) , shaun_answers.ans_titl
FROM shaun_users
LEFT JOIN shaun ON shaun_users.response_pcode = shaun.response_pcode
LEFT JOIN shaun_answers ON shaun_answers.ans_cd = shaun_users.response_demo002
WHERE shaun.response_pcode < '999990'
AND response_completed_on != ''
GROUP BY shaun_users.response_demo002
ORDER BY shaun_users.response_demo002

... but (perhaps obviously), that didn't work. It doesn't pull in the right title for the response, and the count is all jacked up. Any ideas?

Thx,
Shaun

Lars_Berg

All non-aggregated columns in the select list should be part of the group by clause

SELECT shaun_users.response_demo002, count( * ) , shaun_answers.ans_titl
FROM shaun_users
inner JOIN shaun ON shaun_users.response_pcode = shaun.response_pcode
LEFT JOIN shaun_answers ON shaun_answers.ans_cd = shaun_users.response_demo002
WHERE shaun.response_pcode < 999990
AND response_completed_on <> ''
GROUP BY shaun_users.response_demo002, shaun_answers.ans_titl
ORDER BY shaun_users.response_demo002

There is no reason to use a left join for the shaun table as there is a restriction on this table in the where clause. Or do you just use left joins always, without any considerations?

perpetualshaun

To be honest, I've just never learned enough about the different JOIN statements and how they work. I've just always used LEFT, and it usually works. I'll read up on that soon. I actually got a little closer on my own later today, but something's still off. Here's my query now:

SELECT shaun_users.response_demo002, count(shaun.response_id) AS response_count, shaun_answers.ans_titl
FROM shaun_users
INNER JOIN shaun ON shaun_users.response_pcode = shaun.response_pcode
LEFT JOIN shaun_answers ON shaun_answers.ans_cd = shaun_users.response_demo002
AND shaun_answers.ans_lbl = 'response_demo002'
WHERE shaun.response_pcode < '999990'
AND response_completed_on != ''
GROUP BY shaun_users.response_demo002, shaun_answers.ans_titl
ORDER BY response_count DESC

And then I get results like this:

| response_demo002 | count( * ) | ans_titl |

| F | 198 | Female |

| M | 44 | Male |

I can see that they've doubled, but why??

Roger_Ramjet

When you join 2 tables that have a one to many relationship, the data in the table on the one-side gets repeated for evry matching row on the many side

one_table
one_id
1
2
3
4
5

many_table
many_id | one_id
1 | 1
2 | 1
3 | 2
4 | 3
5 | 2
6 | 3

Joined would give
one_table.one_id | many_table.one_id | many_table.many_id
1 | 1 | 1
1 | 1 | 2
2 | 2 | 3
2 | 2 | 5
3 | 3 | 4
3 | 3 | 6

When you then join that to a third table, the multiple instances of one_table.one_id ALL get joined to the third table; and it gets worse if there is a one to many relationship with the third table as that will also multiply the instances of one_id, which in turn will multiply the instances of many_id. Hence your multiplication of results.
1id | 1id | mid | 3id
1 | 1 | 1 | 56
1 | 1 | 1 | 57
1 | 1 | 2 | 58
2 | 2 | 3 | 59
2 | 2 | 3 | 60
2 | 2 | 5 | 70
2 | 2 | 5 | 75

etc

The way around this is to use a sub-query to get the aggregate results from the first 2 tables, and then join the third table to that. Your version must support sub-queries for this to work
(I'm just going to show you how cos I can't be doing with typing all your column names etc)


$query = "SELECT  t3.col1, t1.col2, t1.tots FROM table3 as t3 INNER JOIN
      (SELECT a.col2 AS col2, count(b.col1) AS tots FROM table1 AS a INNER JOIN table2 as b
        GROUP BY a.col2 ORDER BY a.col2) AS t1"

The sub-query in the FROM clause returns a table-type recordset which MUST be aliased with a table name. You can then just join to it like any other table.

This is, as I said, about the only way to do aggregate results with more than 2 tables.