Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Providence, RI
    Posts
    9

    Unanswered: Help with INNER JOIN with two tables (soon to be 7 table)

    I have two tables (which will turn into 7 soon) that I need to join. I am new to SQL so, any help will be greatly appreciated.

    This is my code:
    SELECT dbo.CT.AreaCode, dbo.DE.AreaCode,
    dbo.CT.Number, dbo.DE.Number
    FROM dbo.CT, dbo.DE
    INNER JOIN dbo.CT
    ON dbo.CT.AreaCode=dbo.DE.AreaCode
    INNER JOIN dbo.DE
    ON dbo.DE.Number=dbo.CT.Number
    WHERE dbo.CT.AreaCode= " + Replace(phoneSearch__areaVar, "'", "''") + "
    OR dbo.DE.AreaCode=" + Replace(phoneSearch__areaVar, "'", "''") + "
    AND dbo.CT.Number= " + Replace(phoneSearch__phoneVar, "'", "''") + "
    AND dbo.DE.Number=" + Replace(phoneSearch__phoneVar, "'", "''") + ""

    I have a form for users to enter in an Area Code and a Phone Number (areaVar and phoneVar)

    Is this written correctly?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this part is incorrect --

    FROM dbo.CT, dbo.DE
    INNER JOIN dbo.CT
    ON dbo.CT.AreaCode=dbo.DE.AreaCode
    INNER JOIN dbo.DE
    ON dbo.DE.Number=dbo.CT.Number

    you don't list tables and inner join them, you just inner join them

    unless i misunderstand what you're doing, you only have two tables to be joined, right? not 4 as your query seems to imply?

    in that case, you have to connect the join conditions with AND --

    FROM dbo.CT INNER JOIN dbo.DE
    ON dbo.CT.AreaCode=dbo.DE.AreaCode
    AND dbo.CT.Number = dbo.DE.Number


    rudy
    http://r937.com/

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ummm..

    The double quotes are wrong for the function, plus the SQL looks malformed..

    did you try it, did you get an error message?

    And I don't understand what/why you're using the replace...
    but here's my shot..

    Code:
        SELECT CT.AreaCode
    	 , DE.AreaCode
    	 , CT.Number
    	 , DE.Number
          FROM CT
    INNER JOIN DE
    	ON CT.AreaCode=DE.AreaCode
           AND DE.Number=CT.Number
         WHERE CT.AreaCode= Replace(phoneSearch__areaVar, '''', '''''')
    	OR DE.AreaCode= Replace(phoneSearch__areaVar, '''', '''''')
           AND CT.Number=   Replace(phoneSearch__phoneVar, '''', '''''')
           AND DE.Number=   Replace(phoneSearch__phoneVar, '''', '''''')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2003
    Location
    Providence, RI
    Posts
    9

    i figured it out

    this is the code that worked for me:

    SELECT * FROM dbo.RI WHERE (RI.AreaCode=areaVar and RI.Number=phoneVar) UNION SELECT* FROM dbo.MA where (MA.AreaCode=areaVar and MA.Number=phoneVar) UNION SELECT * FROM dbo.DE WHERE (DE.AreaCode=areaVar and DE.Number=phoneVar) UNION SELECT * FROM dbo.NH WHERE (NH.AreaCode=areaVar and NH.Number=phoneVar) UNION SELECT * FROM dbo.MD WHERE (MD.AreaCode=areaVar and MD.Number=phoneVar) UNION SELECT * FROM dbo.NY WHERE (NY.AreaCode=areaVar and NY.Number=phoneVar) UNION SELECT *
    FROM dbo.PA
    WHERE (PA.AreaCode=areaVar and PA.Number=phoneVar)

    I just learned something new! Thanks anyway!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another example of where just a little bit more information would've saved us some time barking up the wrong tree

    who'd've thought that DE and CT were states

    of course it is wrong to try to join phone numbers from different states!!

    UNION is so obvious, when we see what this is really all about


    by the way, why 7 separate tables? why not just one, with a state code?

  6. #6
    Join Date
    Dec 2003
    Location
    Providence, RI
    Posts
    9
    hey, sorry man. as i said, i am new to SQL and dynamic programming. i truly appreciate you responding to my questions though, other forums are not has helpful as this one. about the database structure, we are planning to do more with the database so setting in up in several tables was the best solution. this DB is used for the DO NOT CALL list (i am an intern at a mortage company that has a telemarketing dept). next time i post something, i will be sure to be as specific as i can.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tell the powers that be at your mortgage company that the query will be seven times faster if they have one table instead of seven separate tables


Posting Permissions

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