The Student Room Group
Uni students - Complete our survey >>
Uni students - Complete our survey >>
Uni students - Complete our survey >>

This discussion is now closed.

Check out other Related discussions

SQL help

hi, trying to figure out these 2 questions using SQL.

[anybody help me please ?
You'll need to do some joins. Below is "proper" way to do it although most teachers/exams boards won't teach you to use the JOIN statement but use loads of where clauses to link the tables instead.

SELECT band.name AS Band, venue.name AS Venue
FROM band
INNER JOIN spot ON (band.Band_id = spot.band_id)
INNER JOIN gig ON (spot.gig_id = gig.gig_id)
INNER JOIN venue ON (gig.venue_id = venue.venue_id)

It's the AS xxxx that changes the name that you access it with, instead of using the default field name. The INNER JOIN links the tables together and the ON statement links the primary and foreign keys together.

HTH

EDIT: just reformatted the SQL so it's easier to read - you'd put it all as one line when you execute it.
Forgot the second bit...

Second part is similar to the first but uses some WHERE clauses to filter the results.

SELECT musician.firstName, musician.lastName, band.name
FROM musician
INNER JOIN lineup ON (musician.musician_id = lineup.musician_id)
INNER JOIN band ON (lineup.band_id = band.band_id)
INNER JOIN spot ON (band.Band_id = spot.band_id)
INNER JOIN gig ON (spot.gig_id = gig.gig_id)
INNER JOIN venue ON (gig.venue_id = venue.venue_id) WHERE (venue.venue_id = 2)
^ not 100% on this one, would need to play with the actual data but I think it works. You might want to use a GROUP BY statement to group the results by band.

EDIT: just reformatted the SQL so it's easier to read - you'd put it all as one line when you execute it.
Reply 3
Avatar for HallysComet
HallysComet
OP
cheers,

getting this error for the 2nd bit

MySQL Error #Column 'venue_id' in where clause is ambiguous
d239113g
cheers,

getting this error for the 2nd bit

MySQL Error #Column 'venue_id' in where clause is ambiguous

Oh sorry, my bad, forgot to put the table name in front of it. It should be:
WHERE (venue.venue_id = 2)

I'll edit the earlier post...
Reply 5
Avatar for HallysComet
HallysComet
OP
thats brilliant thanks.

think i will need to read up on the Inner Join function. never been taught that before.

cheers
d239113g
thats brilliant thanks.

think i will need to read up on the Inner Join function. never been taught that before.

cheers

No problem :smile:

What I was taught when I did computing was to do:
SELECT columnName1, columnName2
FROM table1, table2
WHERE (table1.key = table2.fKey)

but from reading various books it's apparently better to use joins as it's more efficient and it gives you 2 ways of working using INNER and OUTER joins.

The INNER and OUTER part controls what happens if the record in the joined table doesn't exist. There's some stuff on wikipedia if you're interested: http://en.wikipedia.org/wiki/Inner_join#Join_forms

Related discussions

Latest

Trending

Trending

Articles for you