-1

I have two tables member and alumni. alumni table can have the same member in more than one row with a different year field. I want to select all the data from both the table. The table given is:

Alumni:

 id, regd, name,   status,    year

 1    1    kim     inactive   2013
 2    1    kim     inactive   2014
 3    1    kim     inactive   2015 //This is generated for alumni purpose

The table member given is:

regd, name,   status,    year
 1    kim     Active     2015

The expected output is:

`regd`, `name,`   `status,`    `year`
 1       kim       Active       2015 
 1       kim       inactive     2014 
 1       kim       inactive     2013

If there is no record of for example 2015 year in alumni, it will still display the other record in alumni and 2015 from member table. I am trying to display this with regd using the following php and mysql statement but it does not work as expected.

Mysql:

SELECT m.*, a.* FROM member m 
LEFT JOIN alumni a ON m.regd = a.regd
WHERE m.regd ='1' GROUP BY a.year ORDER BY a.year DESC;

PHP:

foreach($members as member):
echo $member['regd'].' '.$member['year'].'<br>';
endforeach;

The error is, it selects all data from alumni table only. Where could I go wrong? Though I did not provide fiddle here, I hope this makes my point clear. please help me.

Zopa
  • 23
  • 4
  • `coalesce(member.status, alumni.status)`? If there's no alumni entry, then you get the member entry. – Marc B May 07 '15 at 18:57
  • @Marc B, I am newbie, please can you provide more complete example. – Zopa May 07 '15 at 18:58
  • is is absolutely unclear without fiddle :-)you have no `session` column in data tables you described. and according to th data samples provided it should better be `RIGHT JOIN` since Alumni has more records. And I see no reason to group records untill I see anu duplicates – Alex May 07 '15 at 19:04
  • @Alex, It must be year, I am mistaken. The reason why I did not use RIGHT JOIN is that member may not be recorded in the alumni table at all. – Zopa May 07 '15 at 19:08

1 Answers1

0

It is OUTER JOIN not supported by MySQL.

https://stackoverflow.com/a/4796911/4421474

So you should do something like:

SELECT res.* FROM (
SELECT 
m.regd, 
m.name, 
COALESCE(m.status, a.status),
m.year,
FROM member m 
LEFT JOIN alumni a 
ON m.regd = a.regd
   AND m.year = a.year
WHERE m.regd ='1' 
UNION
SELECT 
a.regd, 
a.name, 
a.status,
a.year,
FROM member m 
RIGHT JOIN alumni a 
ON m.regd = a.regd
   AND m.year = a.year
WHERE a.regd ='1' 
   AND m.regd IS NULL
) res
ORDER BY res.year DESC
Community
  • 1
  • 1
Alex
  • 16,739
  • 1
  • 28
  • 51
  • It's working without ORDER BY. The error with ORDER BY is incorrect usage of UNION AND ORDER BY. SO what could be the problem. – Zopa May 07 '15 at 20:06