Question 20792 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – Query to return repetitive values associated with multi table

I have two tables Name & Zip, and i want a query to return the zip codes which are having more than 2 plans associated with one different name

+------+------+     +------+------+
| TABLE: NAME |     |  TABLE: ZIP |
+------+------+     +------+------+
| Plan | NAME |     | Plan |  ZIP |
+------+------+     +------+------+
|123   |DAVE  |     |123   |11021 |
+------+------+     +------+------+
|456   |TOM   |     |456   |11021 |
+------+------+     +------+------+
|789   |STEVE |     |789   |21021 |
+------+------+     +------+------+
|321   |PAM   |     |321   |78210 |
+------+------+     +------+------+
|654   |JACK  |     |654   |21021 |
+------+------+     +------+------+

I would like to get the answers as below

+------+------+
| NAME | ZIP  |
+------+------+
|DAVE  | 11021|
+------+------+
|TOM   | 11021|
+------+------+
|PAM   | 21021|
+------+------+
|JACK  | 21021|
+------+------+

Can anyone help me to get this query?

Tags:

2

Answers


  1. 0

    Or you could do:

    SELECT n.NAME, z.ZIP
FROM (
    SELECT *, COUNT(*) OVER (PARTITION BY ZIP) AS num
    FROM ZIP
) z
JOIN NAME n ON z.Plan = n.Plan
WHERE z.num > 1

    db<>fiddle

    This uses COUNT() as a window function. PARTITION BY works similarly to GROUP BY but without collapsing the rows.

    I think your expected result is wrong. It should be DAVE, TOM, STEVE and JACK.

    Login or Signup to reply.
  2. 0

    You can do it using two inner join, first one to join tables based on the Plan, and the second one is to make sure ZIP having 2 or more Plans :

    with cte as (
  select Zip
  from Zip
  group by Zip
  having count(1) > 1
)
select n.Name, z.Zip
from Name n
inner join Zip z on z.Plan = n.Plan
inner join cte c on c.Zip = z.Zip;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search