[SOLVED] Left join not giving expected results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

[SOLVED] Left join not giving expected results

Post 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
Last edited by impulse() on Tue Aug 28, 2007 7:47 am, edited 1 time in total.
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post 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
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post 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 
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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?
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

Which version of MySQL do you use ?

Also give me sample data and expected results.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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.
Post Reply