-2

hello pls i need help on my school project. I have tables try1 and try2, i want to join the two showing the income and expenditure sorted with the date accordingly from the try1 and try2

try1
         +------+-------+--------+--------+
         |  id  | date  | name   |expensis|
         +------+-------+--------+--------+
         |  1   |17/11/1|  shoe  |   200  |
         +------+-------+--------+--------+
         |  2   |17/11/2|  books |   300  |
         +------+-------+--------+--------+
         |  3   |17/11/2| T.fair |   400  |
         +------+-------+--------+--------+

try2
         +------+-------+--------+--------+
         |  id  | date  | name   | source |
         +------+-------+--------+--------+
         |  1   |17/11/1|  Dad   |  1000  |
         +------+-------+--------+--------+
         |  2   |17/11/2| uncle  |        |
         +------+-------+--------+--------+
         |  3   |17/11/4|  Bro   | 150    |
         +------+-------+--------+--------+
 

this is how i want my table to look like

   
    RESULT
             +-------+--------+--------+
             | date  |expensis| source |
             +-------+--------+--------+
             |17/11/1|  200   |  1000  |
             +-------+--------+--------+
             |17/11/2|  300   |        |
             +-------+--------+--------+
             |17/11/2|  400   |        |
             +-------+--------+--------+
             |17/11/4|        |  150   |
             +-------+--------+--------+

i tried many codes and it didnt work out this is the code somewhat close to what i want

 
SELECT u.date, b.amount, h.amount
FROM
  (SELECT Date FROM try1 UNION SELECT Date FROM try2) u
  RIGHT JOIN try1 b ON u.Date=b.Date
 RIGHT JOIN try2 h ON u.Date=h.Date

EDIT: ddl and sample data

CREATE TABLE try1 
( id int(2) AUTO_INCREMENT
, date date
, name varchar(10)
, expencis double(10,0)
,     PRIMARY KEY(id) );

CREATE TABLE try2 
( id int(2) AUTO_INCREMENT
, date date
, name varchar(10)
, sourcee int(10)
,     PRIMARY KEY(id) );

INSERT into try1 (date,name,expencis)
VALUES ('2017-11-1','shoe',200)
     , ('2017-11-2','books',300)
     , ('2017-11-2','t.fair',400);

INSERT into try2 (date,name,sourcee)
VALUES ('2017-11-1','dad',1000)
     , ('2017-11-2','uncle',null)
     , ('2017-11-4','bro',null);
7
  • I recommend you see how do I ask a good question and how to create a minimal, complete and verifiable example, then come back and update your question accordingly; in particular, provide the create table and insert commands for your sample data, the join criteria, and also provide the query(s) you've tried so far; you need to put some (more) effort into your question and show us what you've tried so far ... we're not here to do your homework for you – markp-fuso Nov 24 '17 at 16:34
  • i am not good in english i am using a translator to write this that is the challange i have; the code i tried: SELECT u.date, b.amount, h.amount FROM (SELECT Date FROM try1 UNION SELECT Date FROM try2) u RIGHT JOIN try1 b ON u.Date=b.Date RIGHT JOIN try2 h ON u.Date=h.Date – Joshua Sampson Nov 24 '17 at 16:41
  • Double-check the documentation on JOINs. In a right join, the table on the right side of the join will be included regardless of whether or not there's a match; the table on the left side will be ignored except where it matches the table on the right. I believe you'd want the date (the left table) regardless of whether or not try1 or try2 has a match (at least one will, of course). – RDFozz Nov 24 '17 at 19:44
  • @JoshuaSampson - Did you try replace your RIGHT JOINs with LEFT JOINs, as I suggested? – RDFozz Nov 24 '17 at 21:02
  • @RDFozz wow EPIC!! u made my day...i now understanded more about Joins . i start learning and practicing MYSQL 3weeks ago thanks for your great contribute .I realy appreciate all the efforts – Joshua Sampson Nov 24 '17 at 22:37
0

Not entirely sure that I understand your problem, but it appears as if you are looking for a FULL OUTER JOIN. I don't think that is supported by MySQL, but you can mimic the result by doing a union between a LEFT OUTER JOIN and a RIGHT OUTER JOIN:

select t1.date, t1.expencis, t2.sourcee 
from try1 t1 
left join try2 t2 
    on t1.date = t2.date 
union 
select t2.date, t1.expencis, t2.sourcee 
from try1 t1 
right join try2 t2 
    on t1.date = t2.date 
order by date

+------------+----------+---------+
| date       | expencis | sourcee |
+------------+----------+---------+
| 2017-11-01 |      200 |    1000 |
| 2017-11-02 |      300 |    NULL |
| 2017-11-02 |      400 |    NULL |
| 2017-11-04 |     NULL |    NULL |
+------------+----------+---------+

Note that order by is applied after union. For future posts, never use ascii tables like above. Provide CREATE TABLE statements and INSERT statements instead. Your question will likely get more attention, and you will get better answers that way.

8
  • when i run the code under expencis i have 200,200,200,300,400 null and under source i have 1000,0,150,300,300,400 i dont know why – Joshua Sampson Nov 24 '17 at 15:46
  • 1
    Beats me, I can't run the query since I neither have table definitions nor sample data. – Lennart Nov 24 '17 at 16:11
  • but its not working please do help me,create the tables and insert the records into them and see how you can help me out please – Joshua Sampson Nov 24 '17 at 16:19
  • I might do that if you provide table definitions (CREATE TABLE try1 ( ... ), ...) and some sample data (INSERT INTO try1 (...) VALUES ( ... ) ). – Lennart Nov 24 '17 at 17:33
  • CREATE TABLE try1 ( id int(2) AUTO_INCREMENT, date date, name varchar(10), expencis double(10,0), PRIMARY KEY(id) ); CREATE TABLE try2 ( id int(2) AUTO_INCREMENT, date date, name varchar(10), sourcee int(10), PRIMARY KEY(id) ); INSERT into try1 (date,name,expencis)VALUES("2017-11-1",'shoe',200), ("2017-11-2",'books',300), ("2017-11-2",'t.fair',400); INSERT into try2 (date,name,sourcee)VALUES("2017-11-1",'dad',1000), ("2017-11-2",'uncle',' '), ("2017-11-4",'bro',' '); – Joshua Sampson Nov 24 '17 at 18:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.