CODEWITHSUNDEEP
mysqljoinsumunion
user3408779
user3408779

Reputation: 997

mysql query with Join and union all

I have a union all query from 4 tables. From the 4 tables I am getting the total quantity of the Original_APN from all the 4 tables. For that I am using the below query.

SELECT Original_APN SUM(Quanity) Quanity  FROM (
  SELECT Original_APN, Quanity FROM hs_apn1
  UNION ALL 
  SELECT Original_APN, Quanity FROM hs_apn2
  UNION ALL
  SELECT Original_APN, Quanity FROM hs_apn3
  UNION ALL 
  SELECT Original_APN, Quanity FROM hs_apn4
) a
GROUP BY Original_APN LIMIT 0,4

Below is the out put I am getting:

+---------------+---------+
| Original_APN  | Quanity|
+---------------+---------+
| 0097512135344 |   30.00 |
| 0097512203272 |    2.00 |
| 0097512203296 |    2.00 |
| 0617823119019 |  200.00 |
+---------------+---------+

Now my requirement is I have another table prices. IN that table I have the common column Original_APN. IN prices table I have Original_APN, aritkel, product_name, and price. Below is my desired output. But its failing to join this prices table with my above union all query.

+---------------+---------+---------+--------------+-------+
| Original_APN  | Quanity | Artikel | product_name | price |
+---------------+---------+---------+--------------+-------+
| 0097512135344 |   30.00 | 122     |    TEST1     | 11.00 |
| 0097512203272 |    2.00 |  34     |    TEST2     | 12.45 |
| 0097512203296 |    2.00 | AX1     |    TEST3     | 15.65 |
| 0617823119019 |  200.00 | MN4     |    TEST4     | 22.35 |
+---------------+---------+---------+--------------+-------+

Upvotes: 1

Views: 1438

Answers (1)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

You can use table alias and then join on prices table like this:

SELECT s.Original_APN, s.Quantity, p.Artikel, p.product_name, p.price FROM (
  SELECT Original_APN, SUM(Quantity) Quantity FROM (
    SELECT Original_APN, Quantity FROM hs_apn1
    UNION ALL 
    SELECT Original_APN, Quantity FROM hs_apn2
    UNION ALL
    SELECT Original_APN, Quantity FROM hs_apn3
    UNION ALL 
    SELECT Original_APN, Quantity FROM hs_apn4
  ) a
  GROUP BY Original_APN
) s
INNER JOIN
prices p
ON s.Original_APN = p.Original_APN

Upvotes: 1

Related Questions