Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Unanswered: concatenate in a join...?

    Assuming the 3 tables below:

    Table 1:
    memberKey Name
    1234 Bob
    2345 Sam
    3456 Joe

    Table 2:
    memberKey memberID memberState
    1234 A5328 FL
    2345 Z48X4 CA
    3456 Z48X4 IN

    Table 3:
    memberID memberState preference
    A5328 FL red
    Z48X4 CA green
    Z48X4 IN blue

    I'm not sure how to ask this / explain this, any way I tried didn't yield anything in my searches but here goes.

    I'm looking to make a SQL statement that will return all the attributes on one line like such:

    memberKey name memberID memberState preference
    1234 Bob A5328 FL red

    In order to link between table 2 and 3, the memberID and the meberState have to be concatenated to form a "unique" identifier that can be used to link to table 3, for exampke, A5328FL for Bob's record.

    Table 1 is joined to table 2 through the memberKey.
    Table 2 should join to table 3 through merging the memberID and memberState together.

    If it were in Access, I'd just make a few queries together but this has to be done in SQL and I am not versed enough to do this.

    Does that makes sense?
    Thank you for any help you can offer.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT t1.memberKey 
         , t2.memberID 
         , t2.memberState 
         , t3.preference
      FROM table1 AS t1
      JOIN table2 AS t2
        ON t2.memberKey = t1.memberKey
      JOIN table3 AS t3
        ON t3.memberID    = t2.memberID     
       AND t3.memberState = t2.memberState | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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