MYSQL Join two table fields with the same field value, but include it anyway if the other field is not the same as the other
Lots of MySQL tables I've read today but none of them match my problem. can anyone help me achieve the result of my desire like 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 user_id1 I have the following request: I tried many requests but didn't get it.
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 with this request is that I am using:
ON `tbl2`.`year` =` tbl1`.`year`
which will only return the year of the match. 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 table2 has 2004 and table1 has 2005, both of them don't have the other. but I still want to display them per year.
If it is not possible to do this with just a query, I will also agree with PHP scripts to do this, but Query is my problem here more.
+3
source to share
2 answers
The complete answer would be:
select user_id, sum(points) as poins, sum(amount) as amount, year from (
SELECT tbl1.user_id, tbl1.points, tbl2.amount, tbl1.year
FROM tbl1
LEFT JOIN tbl2 ON tbl1.user_id = tbl2.user_id
AND tbl1.year = tbl2.year
UNION
SELECT tbl2.user_id,tbl1.points , tbl2.amount, tbl2.year
FROM tbl1
RIGHT JOIN tbl2 ON tbl1.user_id = tbl2.user_id
AND tbl1.year = tbl2.year) tables
group by year
order by year
0
source to share