My current code is
select u.name , u.postalcode
FROM user u, flightdetails f, userscan s
WHERE deppdelay > 1 AND deppdelay <5 AND f.ticketid = s.ticketid AND s.userid = u.userid AND u.postalcode IN
(SELECT postalcode FROM user GROUP BY postalcode HAVING COUNT(postalcode) > 1)
ORDER BY postalcode;
The output is this
| name | postalcode |
|---|---|
| Lee Jie Xuan | 120338 |
| Nicholas Sim | 120338 |
| Jasper Tan | 120359 |
| Nicholas Tan | 120359 |
| William Lee | 120381 |
| Brandon Cheong | 120381 |
| Choo Jia Yang Kiron | 120453 |
| Christopher Ang | 120453 |
| Ivan Goh | 120460 |
| Nelson Loo | 120460 |
| Kew Rui Hern | 680623 |
| Philbert Han | 680767 |
| Carter Reilly | 680790 |
| Phlip Wong | 680790 |
| Glen Ang | 681660 |
| Ignatius Ng | 681660 |
However the expected outcome i Would like is duplicate values with distinct names. However, there are postal codes in my code that do not have duplicates. Is there any recommended way to go about doing this?
P.S on the excel file, the postal codes that are displayed once has duplicate postal codes, however the other name has a flight delay not between 1 and 5.
Expected outcome:
| name | postalcode |
|---|---|
| Lee Jie Xuan | 120338 |
| Nicholas Sim | 120338 |
| Jasper Tan | 120359 |
| Nicholas Tan | 120359 |
| William Lee | 120381 |
| Brandon Cheong | 120381 |
| Choo Jia Yang Kiron | 120453 |
| Christopher Ang | 120453 |
| Ivan Goh | 120460 |
| Nelson Loo | 120460 |
| Carter Reilly | 680790 |
| Phlip Wong | 680790 |
| Glen Ang | 681660 |
| Ignatius Ng | 681660 |
I have actually managed to get rid of the single postal codes by using GROUP BY & HAVING COUNT (postalcode) > 1, however, because of the group by, I have distinct names missing.
select distinct u.name , u.postalcode
FROM user u, flightdetails f, userscan s
WHERE deppdelay > 1 AND deppdelay <5 AND f.ticketid = s.ticketid AND s.userid = u.userid AND u.postalcode IN
(SELECT postalcode FROM user GROUP BY postalcode HAVING COUNT(postalcode) > 1)
GROUP BY postalcode
HAVING COUNT(postalcode) >1;
Below is the o/p;
| name | postalcode |
|---|---|
| Lee Jie Xuan | 120338 |
| Nicholas Tan | 120359 |
| William Lee | 120381 |
| Choo Jia Yang Kiron | 120453 |
| Ivan Goh | 120460 |
| Phlip Wong | 680790 |
| Ignatius Ng | 681660 |
Sample data (user)
| userid (key) | name | postalcode | phonenum |
|---|---|---|---|
| 95559 | Uriah Ferry | 238859 | 83792072 |
| 32971 | Mariah lupin | 238859 | 81343214 |
Sample data (flightdetails)
| ticketid (key) | depddelay (departure delay) |
|---|---|
| KED100001 | 2 |
| KED100002 | 3 |
userscan (relationship)
| ticketid | userid |
|---|---|
| KED100001 | 95559 |
| KED100002 | 32971 |
CodePudding user response：
Hope this does the trick
SELECT
usr.name , usr.postalcode
FROM flightdetails AS fd
INNER JOIN userscan AS uss ON fd.ticketid = uss.ticketid
INNER JOIN user AS usr ON uss.userid = usr.userid
WHERE fd.deppdelay > 1 AND fd.deppdelay <5
AND usr.postalcode IN
(
SELECT
usr.postalcode
FROM flightdetails AS fd
INNER JOIN userscan AS uss ON fd.ticketid = uss.ticketid
INNER JOIN user AS usr ON uss.userid = usr.userid
WHERE fd.deppdelay > 1 AND fd.deppdelay <5
GROUP BY usr.postalcode
HAVING COUNT(usr.postalcode) > 1
)
ORDER BY postalcode;
If the output format is less important, you can also do something like this, without the subquery:
SELECT
GROUP_CONCAT(usr.name) as usernames,
usr.postalcode
FROM flightdetails AS fd
INNER JOIN userscan AS uss ON fd.ticketid = uss.ticketid
INNER JOIN user AS usr ON uss.userid = usr.userid
WHERE fd.deppdelay > 1 AND fd.deppdelay <5
GROUP BY usr.postalcode
HAVING COUNT(usr.name) > 1
ORDER BY postalcode;
In that case, watch out for truncated results, MySQL will return 1024 chars in the GROUP_CONCAT by default