TABLE JOINS 1

[je1001]

I am trying to joing two table and get certain information back based on YEAR.

I've tried a left inner join against TableA, but it excludes certain members. Any help would be great!

My Query:
SELECT MEM_ID,FNAME,LNAME,MNAME,PRIM_EMAIL,PAY1,PAY2,YEAR FROM (
select MEM_ID,FNAME,LNAME,MNAME,PRIM_EMAIL,IFNULL(N.PAY1,'N') AS PAY1,IFNULL(N.PAY2,'N') AS PAY2,IFNULL(N.YEAR,'2007') AS YEAR
FROM MEMBERS M LEFT JOIN NATIONAL_DUE_STATUS N
on M.MEM_ID = N.FK_MEM_ID ) AS NAT_DUE_VIEW WHERE YEAR=2007;

Thanks and if I figure it out, i'll put up my solution.



TableA data:
--------------
MEM_ID LNAME FNAME
222 SMITH JOHN
223 SMITHY JAMES
232 JONES JIM
234 TURNER TIMMY
345 GARCIA PABLO

TableB data:
---------------
FK_MEM_ID YEAR PAY1 PAY2
223 2007 Y N
223 2006 Y Y
232 2005 Y Y
345 2007 Y Y


Expected Results:
MEM_ID LNAME FNAME YEAR PAY1 PAY2
222 SMITH JOHN 2007 N N
223 SMITHY JAMES 2007 Y N
232 JONES JIM 2007 N N
234 TURNER TIMMY 2007 N N
345 GARCIA PABLO 2007 Y Y

 

[piti]

if i understand it right ...

SELECT ta.MEM_ID, ta.LNAME, ta.FNAME, IFNULL(tb.YEAR, '2007'), IFNULL(tb.PAY1, 'N'), IFNULL(tb.PAY2, 'N') FROM TableA ta LEFT JOIN TableB tb ON ta.MEM_ID = tb.FK_MEM_ID and tb.YEAR = '2007'

 

[je1001]

Thanks, that's what I need.

 

[guelphdad]

note that there is no such thing as an:
left inner join

a LEFT/RIGHT join is always an OUTER JOIN.

the words inner and outer are optional.

note your problem above stems from that fact that you used a WHERE clause on your right table. This effectively changed your OUTER join into an INNER one which only ever shows matched rows.

Just an FYI for clarity.