CODEWITHSUNDEEP
mysqlsql
shiningstarpxx
shiningstarpxx

Reputation: 41

sql join two tables, one summary the other is detail

When join two tables, i am not sure how to join the following tables into the exact I wanted.

Table A:

--------------------------------------
|   id |   name |  buy time | total |
--------------------------------------
|    1 |    A   |    3      |   30  |
--------------------------------------
|    2 |    B   |    1      |   10  |
--------------------------------------

Table B:

-------------------------------
|   id |  orderid | price  |
-------------------------------
|   1  |     1    |   10   |
-------------------------------
|   1  |     2    |   10   |
-------------------------------
|   1  |     3    |   10   |
-------------------------------
|   2  |     4    |   10   |
-------------------------------

Join to Table C

---------------------------------------------------------
|  id |  name  |  buy time |  total |  orderid |  price |
---------------------------------------------------------
|   1 |    A   |     3     |    30  |          |        |
---------------------------------------------------------
|   1 |        |           |        |    1     |   10   |
---------------------------------------------------------
|   1 |        |           |        |    2     |   10   |
---------------------------------------------------------
|   1 |        |           |        |    3     |   10   |
---------------------------------------------------------
|   2 |    B   |     1     |    10  |          |        |
---------------------------------------------------------
|   2 |        |           |        |    4     |   10   |
---------------------------------------------------------

if I use "Left OUT JOIN on A.id = B.id" the blank area will be filled with the duplicated value, which cause sum of total income is incorrect

Another way is "Select 0 as Name from B", to fill the blank with 0, which will a disaster if the number of column is too many.

Therefore, i would like to ask for help is there some better way to achieve my goal?

Upvotes: 4

Views: 1156

Answers (4)

VShetty
VShetty

Reputation: 69

select ID,name, buytime,total,orderid,price from (

  SELECT  [id]
 , null as [orderid]
  ,null as [price],
   [name]
  ,[buytime]
  ,[total]
   FROM A
     UNION

 SELECT [id]
  ,[orderid]
  ,[price],
  null as[name]
  , null as[buytime]
  ,null as [total]
 FROM B

)as aa

Upvotes: 0

Wasiq Muhammad
Wasiq Muhammad

Reputation: 3128

its look like you need to use UNION

select id, name, `buy time`, total, null AS orderid, null AS price
from A
UNION 
select id, null, null, null, orderid, price
from B
order by id, name DESC

Upvotes: 0

sagi
sagi

Reputation: 40491

Looks like you need a UNION ALL :

SELECT `id`,`name`,`buy time`,`total`,null,null
FROM TableA 
UNION ALL
SELECT id,null,null,null,orderid,price
FROM TableB 
ORDER BY `id`,`name` ,orderid

No need to make this unnecessary join, since you are not joining them to each other any way.

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

What you really want is a UNION of both tables:

SELECT id, name, `buy time`, total, null AS orderid, null AS price
FROM A

UNION 

SELECT id, null, null, null, orderid, price
FROM B
ORDER BY id, name DESC, orderid

Demo here

Upvotes: 3

Related Questions