• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

LEFT JOIN PROBLEM

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I have this minor issue with the sql statement below.

I suppose to receive 6 rows but for some reason i get only 5 ???

when I do this (simple)


SELECT agmrecord.i2dateQouted, agmrecord.i16departureDate, TableKey
FROM agmrecord
WHERE agmrecord.i16departureDate BETWEEN '2005-12-20' AND '2005-12-21'

the return result is 6 raws

+--------------+------------------+------------------------------------+
| i2dateQouted | i16departureDate | TableKey |
+--------------+------------------+------------------------------------+
| 2005-04-18 | 2005-12-20 | TS |
| 2005-04-18 | 2005-12-21 | RE |
| 2005-04-18 | 2005-12-20 | Sr |
| 2005-04-27 | 2005-12-20 | TH |
| 2005-05-11 | 2005-12-21 | RL |
| 2005-05-11 | 2005-12-21 | SR |
+--------------+------------------+------------------------------------+

but when doing this:
SELECT agmrecord.i2dateQouted, agmrecord.i16departureDate, GroupKey, TableKey
FROM agmrecord LEFT JOIN payment_traveler_info_table ON ( payment_traveler_info_table.GroupKey = agmrecord.TableKey )
WHERE agmrecord.i16departureDate BETWEEN '2005-12-20' AND '2005-12-21'
GROUP BY GroupKey

I get:
+--------------+------------------+--------------------------------+------------------------------------+
| i2dateQouted | i16departureDate | GroupKey | TableKey |
+--------------+------------------+--------------------------------+------------------------------------+
| 2005-04-27 | 2005-12-20 | [NULL] | TH |
| 2005-05-11 | 2005-12-21 | RL | RL |
| 2005-04-18 | 2005-12-21 | RE | RE |
| 2005-04-18 | 2005-12-20 | Sr | Sr |
| 2005-04-18 | 2005-12-20 | TS | TS |
+--------------+------------------+--------------------------------+------------------------------------+


why SR is not included?

anyone?
thanks
Peter
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Peter Primrose:

but when doing this:
SELECT agmrecord.i2dateQouted, agmrecord.i16departureDate, GroupKey, TableKey
FROM agmrecord LEFT JOIN payment_traveler_info_table ON ( payment_traveler_info_table.GroupKey = agmrecord.TableKey )
WHERE agmrecord.i16departureDate BETWEEN '2005-12-20' AND '2005-12-21'
GROUP BY GroupKey


I don't have any idea what's going wrong, but on all the databases I'm used to working on, that SQL statement would be invalid; the GROUP BY clause usually requires that every column in the SELECT clause either by one of the grouping fields (such as GroupKey), or be the result of an aggregate function (such as SUM() or AVG()), aggregating over each group.

I'm not sure what the GROUP BY is doing for you and suspect that it's causing a weird result; I suggest that you remove it and see what you get....
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you stu derby
Well...you got me somewhere (I eliminated it and realize the mistake).

anyhow, taking one step fw, I got this:



the return result is below.

the error is the Income and Expenses ( they have been multiplied)
any idea? I think i have to provide another restriction but where?

+------------------------------------+--------------------------------+----------------------------+--------------+------------------+-----------+----------+
| TableKey | GroupKey | GroupKey | i2dateQouted | i16departureDate | Income | Expenses |
+------------------------------------+--------------------------------+----------------------------+--------------+------------------+-----------+----------+
| RL | RL | [NULL] | 2005-05-11 | 2005-12-21 | 4000 | [NULL] |
| RE | RE | RE | 2005-04-18 | 2005-12-21 | 238880 | 4164 |
| SR | [NULL] | SR | 2005-05-11 | 2005-12-21 | [NULL] | 799 |
| Sr | Sr | Sr | 2005-04-18 | 2005-12-20 | 545779.52 | 1410 |
| TM | [NULL] | [NULL] | 2005-04-27 | 2005-12-20 | [NULL] | [NULL] |
| TS | TS | [NULL] | 2005-04-18 | 2005-12-20 | 69161.68 | [NULL] |
+------------------------------------+--------------------------------+----------------------------+--------------+------------------+-----------+----------+
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic