Post reply

Using JOINS in SQL - help!

  • January 29, 2004 at 11:00 am

    #85446

    Hi there,

    I'm using ColdFusion to develop a webpage that connects to an Access database.

    I'm having trouble getting my SQL statement to pull out the data I want, and I'm ready to pull my hair out. 🙂

    I have two tables - one for properties and one for documents.  Each property record relates to several document records, related by a unique ID, which is a combination of the property ID and the property date.  Understand?  Each document record contains the prop ID and prop date of the property record it belongs to.

    The SQL statement it uses (written by somebody else and I am trying to fix it so it works properly) is as follows:

    SELECT * FROM PropInfo, DocInfo

    WHERE PropInfo.Street Like 'xxxxxx' And PropInfo.Municipality='xxxxx'

    And DocInfo.DocType='CONVEYANCE' And DocInfo.PropId=PropInfo.PropId;

    Basically, what I want to do is this.  Go to the property table and find all the records with a specific street and city.  Then look in the document table and find all the documents related to those properties.  Return one result for each match document with the doc type CONVEYANCE.

    I write my results to a search results page and in each result line, I want to display some fields from the matching document record (Conveyance) and some fields from it's corresponding property record.

    But the problem is this - because I say select from both tables, it ends up giving me two result lines for each match, one from the property table and one from the document table.

    I think I need to do a JOIN of some sort (instead of just saying select from both tables) but I can't figure out which one.

    Does this make any sense?  I hope so! 🙂

    Thanks!

    Christy

  • January 29, 2004 at 11:30 am

    #492224

    >> Does this make any sense?

    Maybe a little ...

    I do not see the "property date", you mention, in the SELECT statement you provided??? Where using a JOIN syntax, or the Table1, Table2 ... syntax would not alter the results you are getting, they are just two "styles" of SELECT syntax. In either case the result will have the data from both tables in each record, because each record in the result has columns from both tables. If you see "duplicate" records, then either the columns in the result set do not convey enough data to see the uniquness of them, and/or the condition joining the the tables is not finite enough to limit the result to the desired outcome. Here is the above statement in "JOIN" style.... Note: The RED (I hope) line is new to the statement.

    SELECT *

    FROM PropInfo

    Join DocInfo

        On  DocInfo.PropId=PropInfo.PropId

        And DocInfo.PropDate=PropInfo.PropDate

    WHERE   PropInfo.Street Like 'xxxxxx'

        And PropInfo.Municipality='xxxxx'

        And DocInfo.DocType='CONVEYANCE'



    Once you understand the BITs, all the pieces come together

  • January 30, 2004 at 7:43 am

    #492324

    Great explanation Thomas

    This is one of my favourite mistakes and now is the first thing I look for in duplicate results

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply