Today I am going to show how to join two table and show data by making UNION of two joined table.
Let We have 3 Table whose diagram as given bellow:
And the picture of data and output table are given below.
Our main task is to show data as picture of output table.
Here we can see we can see primary key ID of users table is the foreign key of [balance] table and [credit] table. Both balance and credit table has the same column id, date and receiptno. To show the data as output table we have to show data from these three table.
First assume we do not have to show data from balance table. our output table will contain only five column id,name,date,receiptno and credit. Now how can we do that?
Note that here value of id is the same for both table. Because this is the primary key of users table and foreign key of credit table. We can easily do this by joining this two table. So the query and output will be as follow:
Now in the same way we will join the users table and balance table.
Now what we have to do is merge this two output table in such a way that data of id,name,date, receiptno column will be merged and two new column credit and balance will be added in the output table. We can do this by using UNION. But there is a problem here.
UNION can union only two table if they have same columns.UNION Specifies that multiple result sets are to be combined and returned as a single result set.
But here though both joined table have four (id,name,date,reciptno) same column but one different column. One has balance and another has credit.
Here my solution is, I add one null column named balance in credit & users joined output table and another null column named credit in balance & users joined table.
Here I am giving one query and output of that bellow:
Now if we use UNION to union these two table(with one null column) we can get the desired result.
Any kind of suggestion will be appreciated.
Thanks.
Let We have 3 Table whose diagram as given bellow:
And the picture of data and output table are given below.
Here we can see we can see primary key ID of users table is the foreign key of [balance] table and [credit] table. Both balance and credit table has the same column id, date and receiptno. To show the data as output table we have to show data from these three table.
First assume we do not have to show data from balance table. our output table will contain only five column id,name,date,receiptno and credit. Now how can we do that?
Note that here value of id is the same for both table. Because this is the primary key of users table and foreign key of credit table. We can easily do this by joining this two table. So the query and output will be as follow:
Now in the same way we will join the users table and balance table.
Now what we have to do is merge this two output table in such a way that data of id,name,date, receiptno column will be merged and two new column credit and balance will be added in the output table. We can do this by using UNION. But there is a problem here.
UNION can union only two table if they have same columns.UNION Specifies that multiple result sets are to be combined and returned as a single result set.
But here though both joined table have four (id,name,date,reciptno) same column but one different column. One has balance and another has credit.
Here my solution is, I add one null column named balance in credit & users joined output table and another null column named credit in balance & users joined table.
Here I am giving one query and output of that bellow:
Now if we use UNION to union these two table(with one null column) we can get the desired result.
Any kind of suggestion will be appreciated.
Thanks.
2 comments:
Very Good!
Post a Comment