I am trying to complete an assignment and I am not having much luck with it.

I have 12 questions I need to convert into queries and hope the some help with one of the will start me in the right direction.

I believe I am supposed to be using Joins to link info from two tables but I can't get it to work.

Here are the two tables-
Gig-
GigId
idvenue
idband
Gigdate-
NumAttendees

band-
idband
bandname

The question I need to convert is-

Which band did not have any gigs?

Any help would be greatly appreciated!

We need to take this in steps. What SQL statement would produce a list of everything you know about bands? What SQL statement would produce a list of everything you know about gigs?

-PatP

The two queries would be-

select * from band;

select * from gig;

as you point out you need to do a join between the two tables and as part of that query identify which which bands have no (or a null) gigID

I can't get the join to work, I have tried it a hundred different ways.

pls show one or two of them

Here is the last one I tried.

Select band.idband, gig.gigdate from gig join band on gig.bandname where gigdate = null;

Thanks

try rewriting your SQL so it matches the syntax used in MySQL
MySQL :: MySQL 5.0 Reference Manual :: 12.2.8.1 JOIN Syntax

Try this:

I put that in and had to switch the bandid to idband in third line.


Select band.idband, gig.gigdate
from gig join band
on gig.idband = band.idband
where gigdate is null;

It comes up empty string, I believe the code is right but the actual databse does not have null for the bands that do not have gigs. It just does not list them.

I am going to need to adjust the gigdate with something else. How do I get it to list the bandname of the bands not listed?

Thanks for the help so far

you are on the right path, but not quite there yet

you need a LEFT OUTER JOIN, for starters

what relevance has the gigdate column in this query?
you are searching for bands that don't have any matching records in the gig table, so that column will always be null.

you need to refine the join to get the results you want.

The gigdate column only shows the actual dates of the bands that did have a gig. I would think the idband is the key. If a band's id is not listed then they do not have a gig.

your original question is to find what bands didn't have any gigs
...so why do you think having gigdate in this query is relevant or useful?