I can't figure out why Table A is returning null value after I left join with Table B. It should be 3 tables but for now, i'm just getting the two tables to work.
Table A
IUEID DATE MONTH DATAA
4444 01 JAN 150
4444 02 JAN 98
4444 03 JAN 78
4444 04 JAN 55
TABLE B
IUEID DATE MONTH DATAB
4444 02 JAN CORN
4444 03 JAN GRAPES
SELECT *, TABLEB.DATAB
FROM TABLEA
LEFT JOIN TABLEB
ON TABLEB.IUEID = TABLEA.IUEID
AND TABLEB.DATE = TABLEA.DATE
GROUP BY TABLEA.DATE
OUTPUT:
IUEUD DATE DATAA DATAB
(empty) (empty) 150 (emtpy)
4444 02 98 CORN
4444 03 78 GRAPES
(empty) (empty) 55 (empty)</div>
If you specify in your select from which table the query should pull from then is should work as expected.
SELECT DISTINCT A.IUEUD, A.DATE, A.DATAA, B.DATAB
FROM TABLEA A
LEFT JOIN TABLEB B
ON TABLEB.IUEID = TABLEA.IUEID
AND TABLEB.DATE = TABLEA.DATE
This won't work for all records because not all records in table B have a date value that matches in table A so it has nothing to join onto. Therefore it shows the value as it is in table A and nulls the Table B columns. This is typical of the characteristics of a left join. If you want to show records where they only appear in both tables, you should use a inner join instead.
Lookup the use of different joins to see how they work to get a better understanding.
LEFT JOIN will show you all the values from TABLEA and then will match then with the values in TABLEB. If there is no data related in TABLEB, it will show null.
Like Juakali92 suggested use just JOIN.
Hi Thanks for sharing your thoughts... I was able to solved the problem by adding..
Used Join and Inner Join (It removes the result that doesn't match Table B) Left Join displays the result but IUEID and DATE are empty so I add the following IFNULL
SELECT a.IUEID, a.DATE, b.DATAB
IFNULL(a.IUEID,'0') as IUEIDa,
IFNULL(a.DATE,'0') as DATEa,
FROM TABLEA
LEFT JOIN TABLEB
ON TABLEB.IUEID = TABLEA.IUEID
AND TABLEB.DATE = TABLEA.DATE
ORDER BY TABLEA.DATE
Thanks for all your help.</div>