Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: Specific Query Quandry

    Forgive my utter n00bicity in advance. I'm not really all too familiar with SQL querying and what not and need a tiny bit of assistance working something out. Typically if it's explained or shown I can learn, understand and branch off. Currently using Access 2002.

    I'll use an example to help illustrate what I want to do (if it's too confusing just shoot me).

    Table 1 - has a primary key (rid), and a region name column
    Table 2 - has a primary key (pid), an rid column (which references Table 1's rid) and a property name column.
    Table 3 - has a primary key (iid), an pid column (which references Table 2's ipd) and an image name column.

    Table 1 has four records. Each one has unique names (like North, South East and West with their own respective primary keys)
    Table 2 has three records. Two of the properties (House 1 and House 2) have rids that reference North while the remaining one (House 3) has one that references West.
    Table 3 has four records. Two of the images have pids that reference House 1, one has a pid that references House 2 and one has a pid that references House 3

    How would the SQL statement look if I wanted access of only the data houses in the North (including their respective images)?

    A mock db is online to download for visual reference if needed.

    Any assistance would greatly appreciated. Thanks.
    Last edited by zenassassins; 07-15-09 at 11:04.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1

    Post

    zenassassins, Here is what I think you tables look like:
    Code:
    Region 
    
    RID	REGION
    1	North
    2	South
    3	East
    4	West
    
    Property
    
    PID	RID	PROPERTY
    1	1	House1
    2	1	House2
    3	4	House3
    
    Image
    
    IID	PID	IMAGE
    1	1	HOUSE1IMG1
    2	1	HOUSE1IMG2
    3	2	HOUSE2IMG1
    4	3	HOUSE3IMG1
    To take this in steps, first just select from the Region table for North.
    Code:
    SELECT R.RID, R.REGION
    FROM region-table R
    WHERE R.REGION = 'NORTH'
    
    RID   REGION
    1     North
    Next add in the Property table:
    Code:
    SELECT R.RID, R.REGION, P.PID, P.PROPERTY
    FROM retion_table   R
       , property_table P
    WHERE R.REGION = 'NORTH'
      AND R.RID    = P.RID
    
    RID   REGION PID PROPERTY
    1     North  1   House1
    1     North  2   House2
    Finally all in the Image table:
    Code:
    SELECT R.RID, R.REGION, P.PID, P.PROPERTY, I.IID, I.IMAGE
    FROM retion_table   R
       , property_table P
       , image_table    I
    WHERE R.REGION = 'NORTH'
      AND R.RID    = P.RID
      AND P.PID    = I.PID
    
    RID   REGION PID PROPERTY IID  IMAGE
    1     North  1   House1   1    HOUSE1IMG1
    1     North  1   House1   2    HOUSE1IMG2
    1     North  2   House2   3    HOUSE2IMG1

  3. #3
    Join Date
    Jul 2009
    Posts
    4
    Thanks so much for the assistance. I wonder if I could pick your brain a little bit more? I've been applying what you've explained to what I'm working on but I'm butting heads with something that's boggling my mind.

    I have a copy of the actual DB online - click here. (it's not a malicious site or file - least i HOPE not..hehe)

    I'm using classic ASP/VB script to read information from the database. While I'm in no way asking for anyone to do the work for me, I just need a little help with the data selecting (because queries I'm trying aren't returning the information I want).

    If and when you look at the DB it has 4 tables: provinces, regions, properties and images. The region id is supplied from a form and is used to track the rest of the information (so you can use a variable there or just pick a region id to use from the regions table). For the purpose of testing I'm currently using a rID of 7. Two properties have that same rID.

    Task: I need to select properties in the region selected and have access to their title (pimped), price, region, and cover for the purpose of running a looping ASP script that will list/show that information.

    What I've tried:
    Code:
    SELECT properties.pimped, properties.price, regions.region, images.cover
    FROM properties, regions, images
    WHERE properties.rID = 7
    AND regions.rID = properties.rID
    AND images.propID = properties.propID
    AND images.cover IS NOT NULL
    This returns only 1 property. I know there are 2 properties in that region though. Where am I going wrong?

    As before, I thank any and all in advance. Whoever can help me solve this dilemma shall be praised from high atop the mountains and have a monument built in their honor...

    ... okay so that's a little much, but I will be extremely grateful nonetheless.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    zenassassins, I don't have Access so I can't look at your database.

    When I have a situation where I 'know' more rows should be returned but aren't when doing a complex query, I break it down to smaller parts until I can identify what is causing the problem.

    For example, I would run this to prove the row(s) I want exist in the first table and the rID value used to do the join:
    Code:
    SELECT propID
         , rID
         , pimped
         , price
    FROM properties
    WHERE properties.rID = 7
    Next, I would take the rID value(s) run run a query on the next table it is joined with:
    Code:
    SELECT propID
         , rID
         , region
    FROM regions
    WHERE Rid IN(list of rID values)
    NOTE: In this case this will just be 7 but I am explaining the mothodology of how to track down the problem and this might not be the case for other situations.
    Finally, take the list of propID values and run a query on the last table in the join:
    Code:
    SELECT propID
         , cover 
    FROM images
    WHERE propID in(list of propID values)
    The results of these 3 queries should show you something that is causing the unexpected result.
    NOTE: I did NOT add the AND COVER IS NOT NULL to the images query becasue I want to see all the rows. After looking at the result, you can add that line and run the query again.

  5. #5
    Join Date
    Jul 2009
    Posts
    4
    Code:
    SELECT propID
         , rID
         , pimped
         , price
    FROM properties
    WHERE properties.rID = 7
    Worked properly. returned the 2 properties (one with a propID of 4 and the other with 10)

    Code:
    SELECT propID
         , rID
         , region
    FROM regions
    WHERE rID IN(list of rID values)
    I didn't try this because my regions table doesn't have a propID column.
    Code:
    SELECT propID
         , cover 
    FROM images
    WHERE propID IN(4)
    AND cover IS NOT NULL
    I used the propID of 4 (from one of the known properties in the region) and it returned the appropriate values. However I tried the following as well:
    Code:
    SELECT propID
         , cover 
    FROM images
    WHERE propID IN(4,10)
    AND cover IS NOT NULL
    That returned that same one value for propID 4.

    So would it be fair to assume the problem lies in my images table?

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I think that would be a safe assumption. It isn't the Region table since if there wasn't a rID value of 7 you wouldn't have any rows returned (because rID = 7 in both tables) and the property table returned the two rows expected (propID of 4 & 10).

    That does leave the Image table. And since propID = 4 works, means it is the rows in the image table with a propID of 10.

    What do you get back with
    Code:
    SELECT *
    FROM image
    WHERE propID = 10
    My guess is that the cover values are all NULL.

  7. #7
    Join Date
    Jul 2009
    Posts
    4
    You are absolutely correct (and a Godsend).

    I should really smack myself for not seeing it sooner but you know how it gets when you've been staring at code for hours.

    I went to the images table and added the appropriate records. Technically speaking the code was doing its job properly. I just failed to notice the entry wasn't there. Using the code I had originally tried worked perfectly now...
    Code:
    SELECT properties.pimped, properties.price, regions.region, images.cover
    FROM properties, regions, images
    WHERE properties.rID = 7
    AND regions.rID = properties.rID
    AND images.propID = properties.propID
    AND images.cover IS NOT NULL
    ... returning the two properties and their related data.

    Issue resolved.

    Thank you once again for all your help. I'll have to touch base again once your monument is completed.

    Take care.

Posting Permissions

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