CODEWITHSUNDEEP
mysqlsqljoin
user965748
user965748

Reputation: 2387

LEFT JOIN trouble

I'm probably missing something, but I can't make a simple SQL query work. I have 2 tables City and Event and I want to see all cities that hosted any event. The code below seems to perform something odd, because it just overloads PHPMyAdmin.

SELECT city.name, city.date, city.gh, city.ga 
FROM city 
LEFT JOIN event ON city.id = event.city

When I tried natural join, it displayed all events that were hosted in any city as expected.

Upvotes: 0

Views: 90

Answers (6)

sll
sll

Reputation: 62484

SELECT city.name, city.date, city.gh, city.ga 
FROM city 
INNER JOIN event ON city.id = event.city
GROUP BY city.name

The LEFT JOIN will return you all entries from city table even no event entry is associated, the output resultset would be pretty big, using INNER JOIN you requesting only rows which has an associated entry, and by doing GROUP BY you select single row per city so you would end up with a list of unique Cities which has any event associated in the events table.

Upvotes: 1

Neil
Neil

Reputation: 55382

There are a number of ways to express this in SQL, and you should profile to find out which way works best with your database.

SELECT DISTINCT city.name, city.date, city.gh, city.ga
  FROM city
  INNER JOIN event ON event.city = city.id

SELECT city.name, city.date, city.gh, city.ga
  FROM city
  INNER JOIN event ON event.city = city.id
  GROUP BY city.name, city.date, city.gh, city.ga

SELECT city.name, city.date, city.gh, city.ga
  FROM city
  INNER JOIN (
    SELECT DISTINCT event.city FROM event
  ) AS eventcity ON eventcity.city = city.id

SELECT city.name, city.date, city.gh, city.ga
  FROM city
  INNER JOIN (
    SELECT event.city FROM event GROUP BY event.city
  ) AS eventcity ON eventcity.city = city.id

SELECT city.name, city.date, city.gh, city.ga
  FROM city
  WHERE city.id IN (SELECT event.city FROM event)

SELECT city.name, city.date, city.gh, city.ga
  FROM city
  WHERE EXISTS (SELECT city FROM event WHERE event.city = city.id)

Upvotes: 1

Hogan
Hogan

Reputation: 70513

This is how you would do it with a left join, but I don't see the point -- just use an inner join and do a distinct.

SELECT distinct city.name, city.date, city.gh, city.ga 
FROM city 
LEFT JOIN event ON city.id = event.city
where event.city is not null

With inner join

SELECT distinct city.name, city.date, city.gh, city.ga 
FROM city 
JOIN event ON city.id = event.city

Upvotes: 1

Bohemian
Bohemian

Reputation: 424983

You need to change two things:

  • Make it an INNER join (don't use the LEFT keyword)
  • Use the DISTINCT keyword, otherwise you'll get the same city returned many times (once for each event it hosted)

Try this:

SELECT DISTINCT city.name, city.date, city.gh, city.ga 
FROM city 
JOIN event ON city.id = event.city

You could also accomplish this using a sub-select, like this:

SELECT city.name, city.date, city.gh, city.ga 
FROM city
WHERE id in (SELECT city from EVENT)

Personally, I find the sub-select easier to understand - it's almost like the English you used to describe the problem.

Upvotes: 1

Adam Wenger
Adam Wenger

Reputation: 17540

You probably want an INNER JOIN:

SELECT c.name, c.date, c.gh, c.ga 
FROM city AS c
INNER JOIN event AS e ON c.id = e.city

Take a look at this graphical explanation on JOINS, should help you out.

If you only want distinct City names that have had events, you could use:

SELECT DISTINCT c.name
FROM city AS c
INNER JOIN event AS e ON c.id = e.city

Upvotes: 1

Raymond Hettinger
Raymond Hettinger

Reputation: 226181

If you only want to see cities that hosted an event, use an INNER JOIN instead of a LEFT JOIN. The latter will show all cities regardless of whether an event was held.

Upvotes: 1

Related Questions