Page 1 of 1
[SOLVED] Left join not giving expected results
Posted: Tue Aug 28, 2007 4:03 am
by impulse()
I want as many rows returned in my query as there are rows in table 1. I also want a field from table 2 joined onto the end of table 1 by a left join. So far this all works fine and I get the expected results. But table 3 has several thousand rows where multiple rows may relate to a field from table 1. I want to count how many rows from table 3 relate to table 1 and then use group by to show the figure on the outputted results. This is where things go wonky. If there are no rows from table 3 that relate to table 1, then the row from table 1 isn't show at all. So I'm only getting 1/2 of the rows I want in the output.
My query looks something like this at the moment:
Code: Select all
SELECT a.fName, a.sName, a.addr, a.tel, b.sage, COUNT(c.Lid)
FROM custs AS a
LEFT JOIN sageAccounts as b ON a.id = b.id
LEFT JOIN aOrders AS c ON a.id = c.Lid
GROUP BY c.Lid
Regardsm
Posted: Tue Aug 28, 2007 4:09 am
by xpgeek
Hi
impulse()
Replace Left Join to Inner JOin for table 3 and remove GROUP BY c.Lid
Code: Select all
SELECT a.fName, a.sName, a.addr, a.tel, b.sage, COUNT(c.Lid)
FROM custs AS a
LEFT JOIN sageAccounts AS b ON a.id = b.id
INNER JOIN aOrders AS c ON a.id = c.Lid
Posted: Tue Aug 28, 2007 4:19 am
by impulse()
If I remove the group by I receive an error:
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
Posted: Tue Aug 28, 2007 4:26 am
by xpgeek
You can use subqueries:
Code: Select all
SELECT a.fName, a.sName, a.addr, a.tel, b.sage, (select COUNT(c.Lid) from aOrders AS c where a.id = c.Lid ) as countLid
FROM custs AS a
LEFT JOIN sageAccounts AS b ON a.id = b.id
INNER JOIN aOrders AS c ON a.id = c.Lid
Posted: Tue Aug 28, 2007 4:43 am
by impulse()
I'm getting syntax errors with a query like that. Maybe it would be a better idea for to have 2 mysql queries? The second being in a loop from the first?
Posted: Tue Aug 28, 2007 4:48 am
by xpgeek
Which version of MySQL do you use ?
Also give me sample data and expected results.
Posted: Tue Aug 28, 2007 7:13 am
by impulse()
Sorry for the delay.
I'm using MySQL 4.
I can't show the data I'm using, sorry. Maybe if I explain the situation better it may help.
Table A - Customer details
Table B - Accounts details
Table C - Order details
I'm looking to be able to show every row in Table A. Then 1 field which is a Sage code from Table B, this is done easily by using left join to show a Sage code if there's one there, but not to affect the total rows if there's not. Finally I want to count how many rows are in Table C that have the same customer ID as each row in Table A. There may be 0 or there may be 100 rows in Table C that will join with 1 row in table 1.
The problem I'm getting is that if there are no rows in Table C that join with a row in Table A then the row from Table A isn't included in the output.
I would hope to see something along the lines of
Row 1 - Mr J Bloggs ABC123 23 (Title, Initial, Surname, Sage, Amount of orders)
Row 2 - Mrs A Smith CAB123 0 (Title, Initial, Surname, Sage, Amount of orders)
Etc.
And there to be as many rows outputted as there are rows in Table A.
Posted: Tue Aug 28, 2007 7:46 am
by impulse()
This was my fault. I was grouping the wrong table. I was grouping by Table C instead of Table A.
Thank you for your help.