CODEWITHSUNDEEP
mysql
Synay
Synay

Reputation: 133

Display two different tables by id

I need help with a mysql query. I have these two tables in my database.

ZONE

+----+--------+
| id | name   |
+----+--------+
|  1 | ZONE 1 |
|  2 | ZONE 2 |
|  3 | ZONE 3 |
+----+--------+

DISTRICT

+----+---------+--------+-------------------+
| id | id_zone | name   | number_voters     |
+----+---------+--------+-------------------+
|  1 |       1 | DIST 1 | 2000              |
|  2 |       1 | DIST 2 | 3000              |
|  3 |       1 | DIST 3 | 4000              |
|  4 |       2 | DIST 4 | 3500              |
|  5 |       3 | DIST 5 | 1400              |
|  6 |       3 | DIST 6 | 3800              |
+----+---------+--------+-------------------+

When I make a two select and union with conditions

SELECT z.`name`, sum(d.`number_voters`) AS quantity 
FROM zone z 
    JOIN district d 
WHERE z.`id`= d.`id_zone` 
AND z.`id`=1 
GROUP BY z.`name` 
UNION 
SELECT d.`name`, d.`number_voters`  
FROM zona z 
    JOIN district d 
WHERE z.`id`= d.`id_zone` 
AND d.`id_zone`=1;

It shows me how I want:

+--------+----------+
| name   | quantity |
+--------+----------+
| ZONE 1 | 9000     |
| DIST 1 | 2000     |
| DIST 2 | 3000     |
| DIST 3 | 4000     |
+--------+----------+

Here comes the problem by removing the id from the both of query, it shows them like this:

+--------+----------+
| name   | quantity |
+--------+----------+
| ZONE 1 | 9000     |
| ZONE 2 | 3500     |
| ZONE 3 | 5200     |
| DIST 1 | 2000     |
| DIST 2 | 3000     |
| DIST 3 | 4000     |
| DIST 4 | 3500     |
| DIST 5 | 1400     |
| DIST 6 | 3800     |
+--------+----------+

I need you to show me this way:

+--------+----------+
| name   | quantity |
+--------+----------+
| ZONE 1 | 9000     |
| DIST 1 | 2000     |
| DIST 2 | 3000     |
| DIST 3 | 4000     |
| ZONE 2 | 3500     |
| DIST 4 | 3500     |
| ZONE 3 | 5200     |
| DIST 5 | 1400     |
| DIST 6 | 3800     |
+--------+----------+

I don't know why, or if it´s possible to do, your help would be very helpful.

Upvotes: 0

Views: 69

Answers (1)

forpas
forpas

Reputation: 164099

Use a type column indicating that the row is a zone or a district and sort 1st by the zone's id and then by type, name:

SELECT name, quantity
FROM (
  SELECT 1 type, z.id, z.name, sum(d.number_voters) AS quantity 
  FROM ZONE z JOIN DISTRICT d 
  ON z.id= d.id_zone
  GROUP BY z.id, z.name 
  UNION ALL 
  SELECT 2 type, d.id_zone, d.name, d.number_voters  
  FROM DISTRICT d 
) t
ORDER BY id, type, name

Unless there are districts that don't belong to any zone you don't need a join in the 2nd query of UNION.
See the demo.
Results:

| name   | quantity |
| ------ | -------- |
| ZONE 1 | 9000     |
| DIST 1 | 2000     |
| DIST 2 | 3000     |
| DIST 3 | 4000     |
| ZONE 2 | 3500     |
| DIST 4 | 3500     |
| ZONE 3 | 5200     |
| DIST 5 | 1400     |
| DIST 6 | 3800     |

Upvotes: 1

Related Questions