SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Custom query...help please.

    OK everyone...I've got a question for you. I have a database of artists for an event going on in Nashville. We add artists every time we confirm a show for this event. Currently we have over 450 artists in our database. However, because we have moved shows around and artists have cancelled or been removed, there are people in the database that are not playing any shows, yet still showing up on the site. I've implemented a y/n field which enables me to removes from display any artist that is "n".

    However, as there are so many artists, I don't have the time to go through and check to see if each artist is scheduled to play at least one show. I would like to build a query that will return the first and last name for any artist that is playing zero shows. I've thought about it and thought about it, but I can't come up with a way to return the desired result set. Can someone help me out please? I'm using Access. The fields are listed below. The tables contain more information, but this should be all you need. I'm guessing the basic query would start like this:

    SELECT
    FirstName,
    LastName
    FROM Artist
    WHERE

    and the WHERE clause is where I get stuck. I don't know how to link the two tables to return the results that I want. Please help.

    Artist:
    FirstName
    LastName

    ArtistShow:
    ShowID
    ArtistID
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    assuming your artist table has an artistID field:

    SELECT
    FirstName,
    LastName
    FROM Artist
    WHERE ArtistID NOT IN (SELECT ArtistID FROM ArtistShow)


    ... i know this works in MSSQL, so i'd assume it would work in Access.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This one is just trying the obvious, but what does this give you:

    SELECT FirstName, LastName FROM Artists WHERE ID NOT in {SELECT ArtistID FROM ArtistShow};

    This is of course assuming that only Artists that perform shows have been assigned a ShowID to them.

    - Edit -

    You are a fast one crowdozer

  4. #4
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah...

    What is this method called? Nested query or something like that? I would have never thought to do that. I'll give it a go right now. Thanks crowdozer.

    And yes, the Artist TABLE does have an ArtistID field.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, creole, this is a nested query.

  6. #6
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i usually call them subqueries although "nested query" seems perfectly accurate as well.

  7. #7
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK...it seems to have worked. And worked great. That's perfect. I didn't think it would be that easy. I think I need to start reading up on SQL.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  8. #8
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its amazing how much you can do with it. i've been programming for SQL Server for almost two and a half years now and i still learn something new and significant every week.

  9. #9
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using Access right now but in about a month I'll be getting SQL server 200 installed on my machine, or at least have direct access to it. Then I know I'll be a big boy.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes


Bookmarks

Posting Permissions

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