MySQL Inner join with where on multiple rows

Question

I need a SQL solution for the following:

Lets say I have one table named "TRIPS"

[  id ][ Name  ][ Duration]
[  1  ][ Trip1 ][   12    ]
[  2  ][ Trip2 ][   16    ]
[  3  ][ Trip3 ][   5     ]

And another table with countries each TRIP visits: LOCATIONS

[  tripId ][ country ]
[    1    ][    US   ] <--
[    1    ][    PA   ] <--
[    1    ][    RU   ]
[    2    ][    US   ] <--
[    2    ][    PA   ] <--
[    3    ][    PA   ]
[    3    ][    RU   ]

Now I want all the TRIPS which visitis "US" and "PA". So the result will be 1 and 2 becouse they both visit US and PA.

I thought I could use INNER JOIN but that only takes one row in the LOCATIONS table (afaik)

Can someone help me?

[EDIT]

I have it working with this ugly way:

SELECT * ,group_concat(distinct(LOCATIONS.country) separator ',') as COUNTRYCODES 
FROM TRIPS left join LOCATIONS on TRIPS.id = LOCATIONS.tripid  
group by TRIPS.id 
having COUNTRYCODES like '%PA%' and COUNTRYCODES like '%US%';

But I think using "like" is an ugly solution

Marc B · Accepted Answer · 2014-05-29 19:03:19Z

1

SELECT *, COUNT(DISTINCT tripID) AS cnt
FROM TRIPS
LEFT JOIN LOCATIONS ON TRIPS.id = LOCATIONS.tripID
WHERE LOCATIONS.country IN ('US', 'PA')
GROUP BY TRIPS.id
HAVING cnt = 2

Basically, get all records where the trip was to PA or US, count how many trips were made, and return only those where BOTH countries were visited.

answered May 29, 2014 at 19:03

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NLAnaconda Over a year ago

I cant get it to work. I get only one row with this QUERY with a count of all the rows it finds.So, if it finds 20 rows, the cnt = 20. "SELECT *, COUNT(DISTINCT tripID) AS cnt FROM TRIPS LEFT JOIN LOCATIONS ON TRIPS.id = LOCATIONS.tripID WHERE LOCATIONS.country IN ('US', 'PA')"

Collectives™ on Stack Overflow

MySQL Inner join with where on multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related