Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    1

    Unanswered: Tricky SQL Question

    I have an Access database for which I'm trying to write a query based on a request from the users.

    I have a table of data related to artwork, with three columns: ArtworkId, DescriptionText, and CalloutNumber. Each piece of artwork can have more than one DescriptionText value.

    What would be a good way to write an SQL query to return, for example, all ArtworkId records having both DescriptionText = "Start Switch" and DescriptionText = "Stop Switch" records?

    ArtworkID / DescriptionText
    123 / Motor
    234 / Fuse
    234 / Start Switch
    456 / Stop Switch
    789 / Start Switch
    789 / Stop Switch
    789 / Light

    Result = ArtworkID 789

    Any assistance will be greatly appreciated. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    With Artwork being the name of the table, try:
    Code:
    SELECT a.ArtworkID
    FROM ( SELECT Artwork.ArtworkID
             FROM Artwork
             WHERE (Artwork.DescriptionText="Start Switch")) AS a
    INNER JOIN
         ( SELECT Artwork.ArtworkID
             FROM Artwork
             WHERE (Artwork.DescriptionText="Stop Switch")) AS b
    ON a.ArtworkID = b.ArtworkID
    GROUP BY a.ArtworkID;
    Last edited by Sinndho; 09-26-12 at 07:53. Reason: added GROUP BY clause
    Have a nice day!

Tags for this Thread

Posting Permissions

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