0

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)

Improve this question
7
  • is the query you posted and the output as it is when you are running it or did you modify it here when you posted the question? The output looks as if you are selecting the columns with empty data from the tableB Commented Feb 15, 2018 at 17:12
  • What's the group by ment to do? Group by without aggregation generally makes no sense. It may be hiding the rows you expect to see.. maybe you mean order by? Also instead of * use tableA.* or better spell out each column explicitly; the engine could be getting confused about which column to display since names are the same in both tables. Provide a sqlfiddle.com or rextester.com working example showing the problem; but I bet the * or the group by is causing the issue. in mysql Commented Feb 15, 2018 at 17:12
  • You said, "I can't figure out why Table A is returning null value after I left join with Table B." How do you know it's table A that's null? Are you sure the engine is using A in the select and not b? alias the * to tableA.* mySQL can be quirky when you have tables with the same column name! Other RDBMS would indicate an error as they wouldn't be able to resolve which table column you mean; mysql assumes they are equal and picks one. (much more complex than this but general gist) Commented Feb 15, 2018 at 17:18
  • Hi @xQbert I'll try this out too. Thanks! regarding your question, I assumed It's A or should I say NULL columns instead of entire table row since I can see that DATAA (150) from Table A. But IUEID and DATE are empty. Commented Feb 15, 2018 at 17:34
  • @isaace yes i just simulated it. Table A has date from Jan 01 to Jan 30 so as Table B. If i don't put Group by date it will display repeat date several times when running the result. Commented Feb 15, 2018 at 17:36

4 Answers 4

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

it should still work if the query is selecting those columns explicitly from tableA
0

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
Improve this answer

1 Comment

Hi @isaace. I've tried but got the same result <pre> "SELECT a.Date1, a.Date, a.Agent_ID, a.Agent_Name, a.Open, a.New, a.Decline, a.RFI_done_by_CO, a.RFI_done_by_Entity, b.FCA_made, b.Date1, b.Date, b.Agent_ID FROM view_agent_co_performance a LEFT JOIN view_agent_case_closure b ON b.Agent_ID = a.Agent_ID AND b.Date1 = a.Date1 WHERE a.Agent_ID = 'IUE0003060' ORDER BY a.Date ASC"; </pre>
0

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.

Improve this answer

1 Comment

Thanks! I tried Join and Inner Join, it completely removes the date that doesn't match on Table B... while Left join displays all except that the IUE and DATE returns null
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.