Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2011
    Posts
    6

    Unanswered: Converting a sentence into an SQL Query

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2011
    Posts
    6
    The two queries would be-

    select * from band;

    select * from gig;

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2011
    Posts
    6
    I can't get the join to work, I have tried it a hundred different ways.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by midnightkomp View Post
    I can't get the join to work, I have tried it a hundred different ways.
    pls show one or two of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2011
    Posts
    6
    Here is the last one I tried.

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

    Thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try rewriting your SQL so it matches the syntax used in MySQL
    MySQL :: MySQL 5.0 Reference Manual :: 12.2.8.1 JOIN Syntax
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2011
    Posts
    20
    Quote Originally Posted by midnightkomp View Post
    Here is the last one I tried.

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

    Try this:
    Code:
    Select band.idband, gig.gigdate 
    from gig join band 
    on gig.bandid = band.bandid 
    where gigdate is null;

  10. #10
    Join Date
    Sep 2011
    Posts
    6
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are on the right path, but not quite there yet

    you need a LEFT OUTER JOIN, for starters
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Sep 2011
    Posts
    6
    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.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •