Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: help linking tables

    this is the query I need help with

    select a.RecordID,a.RelocateID,a.WhenConfirmed,b.RequestI D,b.DocumentID,b.RequestWhen from RelocateeDTA a inner join
    docRequests b on ({a.RecordID}=' + CONVERT(VARCHAR, a.RecordID)'= b.WhereClause)

    because I am trying to link RelocateeDTA that has RecordID to docRequests that has the same piece of data in field WhereClause but is a varchar, and I am getting an error message (syntax error or access violation)

    Could somebody help me please

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql server doesn't support those curly braces (that i know of) and you have some funky string embedded in the ON clause which just won't fly

    is this some type of external scripting language?

    better test your sql out in query analyzer before embedding it in a scripting language

    could you please give a couple sample rows of data from each table that illustrate your problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    I am not sure, I am hopeless when it comes to using the right punctuation so to speak.

    I am testing this is query anlayzer since I want to link this to my table and then I need to create a report, not sure what you mean by funcky strings.

    this is a row from docRequest
    1 1 Sally {RelocationConsultants.RecordID}=103 2001-12-14 13:56:06.547 2001-12-17 16:21:15.013 Smith, Jeff test.benstead@test.com.au

    this is a row from RelocateeDTA

    2 86 59 1999-01-03 00:00:00.000 1999-01-07 00:00:00.000 4 .00 R NULL NULL 1 0x000000000025630C NULL NULL NULL AUD NULL NULL NULL NULL NULL NULL NULL NULL NULL P3875 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL


    so I am trying to link RElocateDTA using RecordID (int) to docRequest using WhereClaus (varchar)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't get it

    which columns are you trying to join?

    perhaps you should show the CREATE TABLE statement as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Exectaly those a.RecordID=b.WhereClause, but RecordID is interger and WhereCalues is varchar and I need to take care of the conversion of RecordID into varchar so I can link them and extract data, I think that is clear.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's clear to you, but not at all clear to me
    Code:
    select a.RecordID
         , a.RelocateID
         , a.WhenConfirmed
         , b.RequestI D
         , b.DocumentID
         , b.RequestWhen 
      from RelocateeDTA a 
    inner
      join docRequests b 
        on convert(varchar,a.RecordID) = b.WhereClause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Wink

    there you go, beautiful, I just didn't know how to take care of the converstion and linking at the same time, but know thanks to you I can continue.

    Cheers mate

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Might I suggest a bit more "industrial duty" variant of Rudy's example:
    Code:
    select a.RecordID
         , a.RelocateID
         , a.WhenConfirmed
         , b.RequestI D
         , b.DocumentID
         , b.RequestWhen 
      from RelocateeDTA a 
    inner
      join docRequests b 
        on convert(varchar(30),a.RecordID) = b.WhereClause
    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thank you guys, but I have just realized, having scrolled down to the end, that I seem to get results only from docREquests table all other records from the other table come up as null when they shouldn't be.

    Any suggestions

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about:
    Code:
    SELECT
       a.RecordID
    ,  a.RelocateID
    ,  a.WhenConfirmed
    ,  b.RequestI D
    ,  b.DocumentID
    ,  b.RequestWhen 
       FROM RelocateeDTA AS a 
       LEFT OUTER JOIN docRequests AS b 
          ON convert(varchar(30),a.RecordID) = b.WhereClause
    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    I am really stuck. This is the query I need help on

    "select a.RecordID as RecordDTA
    , a.RelocateID as RelocateDTA
    , a.WhenConfirmed as WhenConfirmedDTA
    , b.RequestID
    , b.DocumentID
    , b.RequestWhen
    from RelocateeDTA a left
    join docRequests b
    on convert(varchar(30),a.RecordID) = b.WhereClause"


    What I am trying to do is to link two tables but they are of a different field type and that is why I have converstion but what seems to be my problem is that when I run the query I only get results for the RelocateeDTA table or docRequests table, depending how I twist around the query, but all the field from the other table that I am linking the first table on are always empty/NULLS

    The reason for that is because RecordID from RelocateeDTA is an integer so saves ID (2667 for example) but field I am linking it to is a varchar and values saved in that field look like "{RelocateeDTA.RecordID}=2667 and they when I am doing a query it can't find any matching records.

    So how can I write this query so when I link these tables focuses only on the ID number from this piece of text "{RelocateeDTA.RecordID}=2667 " which should be comparable to RecordID from RelocateeDTA table.

    I hope I am making sense and I really need help.

    Anybody?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are actually storing the string '{RelocateeDTA.RecordID}=2667' ????

    you may want to re-think that design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as you are certain that there will be an equal sign in every b.WhereClause, you could use:
    Code:
    SELECT
       a.RecordID
    ,  a.RelocateID
    ,  a.WhenConfirmed
    ,  b.RequestI D
    ,  b.DocumentID
    ,  b.RequestWhen 
       FROM RelocateeDTA AS a 
       LEFT OUTER JOIN docRequests AS b 
          ON convert(varchar(30),a.RecordID)
          = RTrim(SubString(b.WhereClause, CharIndex('=', b.WhereClause), 3999))
    -PatP

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, i think what's happening is that the b.WhereClause column stores the actual table/column names of the column in another table that that row is supposed to join to

    can't think of any other reason to store a name in curly braces in there

    so while the design might be spiffy in concept, in practice zobey is having all kinds of pain actually getting this "dynamic join" to work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    You are right it stores in docRequests table values exactly as {RelocateeDTA.RecordID}=2267 for column WhereClause, so that is the reason I think it can spit any data out and I need to be able to.
    Last edited by zobernjik; 01-12-05 at 01:20.

Posting Permissions

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