A lot of MYSQL Combining tables i have read today, but none of them fits with my problem. can anyone help me achive my desire result like in below?
TABLE 1:
|--------------------| | tbl1 | |--------------------| |user_id|points|year | |--------------------| | 1 | 3.2 | 2001| | 1 | 2.2 | 2002| | 1 | 3.8 | 2003| | 1 | 3.6 | 2005| | 2 | 1.2 | 2001| | 2 | 1.2 | 2002| | 2 | 1.2 | 2003| | *etc... | |--------------------|
TABLE 2:
|--------------------| | tbl2 | |--------------------| |user_id|amount|year | |--------------------| | 1 | 6.2 | 2001| | 1 | 9.2 | 2002| | 1 | 2.8 | 2003| | 1 | 7.6 | 2004| | 2 | 3.2 | 2001| | 2 | 8.2 | 2002| | 2 | 6.2 | 2003| | *etc... | |--------------------|
i only want to get the user_id1 i have the following query, i tried many query combination but didn't get any.
SELECT
`tbl1`.`points`,
`tbl2`.`amount`,
`tbl1`.`year`
FROM (
SELECT *
FROM `tbl1`
WHERE `user_id` = 1
ORDER BY `year` DESC
) AS `tbl1`
INNER JOIN (
SELECT *
FROM `tbl2`
WHERE `user_id` = 1
ORDER BY `year` DESC
) AS `tbl2` ON `tbl2`.`year` = `tbl1`.`year`
my problem to that query is that i use:
ON `tbl2`.`year` = `tbl1`.`year`
which will only return the match year. so yeah, i'm stuck.
DESIRED RESULT:
|----------------------------| | JOINED/COMBINED | |----------------------------| |user_id|amount|points| year | |----------------------------| | 1 | 6.2 | 3.2 | 2001 | | 1 | 9.2 | 2.2 | 2002 | | 1 | 2.8 | 3.8 | 2003 | | 1 | 7.6 | Null | 2004 | | 1 | Null | 3.6 | 2005 | | *etc... | |----------------------------|
the problem in this case is that table2 has 2004 while table1 has 2005 both of them doesn't have another. but i still want to display them order by year.
If it can't be done with query alone i will also accept PHP scripting to make this done but Query is my concern here more..