4

I was wondering what the answer should be for 1c. on this website:

http://sqlzoo.net/6.htm

SELECT company 
FROM route WHERE stop=4
GROUP BY name, ID

this obviously isn't working, the ID and name isn't showing up no matter what. What is missing here? Thanks.

benni_mac_b
  • 8,803
  • 5
  • 39
  • 59
Evolutionary High
  • 1,257
  • 4
  • 14
  • 14

4 Answers4

3
SELECT stops.id, stops.name
FROM route 
INNER JOIN stops on route.stop = stops.id
WHERE route.num = 4 AND route.company = 'LRT'

You need to join the tables as the data you want to return is in a different table to the one which filters the data.

benni_mac_b
  • 8,803
  • 5
  • 39
  • 59
1

This works and does not include any unnecessary table joins. A good rule of thumb is to use EXISTS to verify values in a table that you do not need the output for. Otherwise, you would use a JOIN

SELECT stops.id, stops.name
FROM stops
WHERE EXISTS
(
    SELECT 1 FROM route 
    WHERE route.stop = stops.id AND num = '4' AND company = 'LRT'
)
Justin Pihony
  • 66,056
  • 18
  • 147
  • 180
  • why use EXISTS instead a JOIN? – wickedone Mar 27 '12 at 19:28
  • 1
    @wickedrunr http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx http://stackoverflow.com/questions/2177346/can-an-inner-join-offer-better-performance-than-exists It depends on the query, like these articles say, but generally, if you are not using the output I have found EXISTS to be more performant – Justin Pihony Mar 27 '12 at 19:45
0

It gives you this error:

sql: Unknown column 'name' in 'group statement'

There is no name in route tabel. The tables structure are:

stops(id, name)
route(num,company,pos, stop)

So the answer for this quiz is:

SELECT s.id, s.name
FROM   route r, stops s 
WHERE  r.stop= s.id 
and    r.num = 4 AND r.company = 'LRT'
gdoron
  • 147,333
  • 58
  • 291
  • 367
0
select s.id, s.name
from stops s
inner join route r
    on s.id = r.stop
where r.num= 4
    AND r.company= 'LRT'
Taryn
  • 242,637
  • 56
  • 362
  • 405