Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: using a join table twice?

    hello,

    for example... say i have this query:

    Code:
    select
        table1.indicator
        table2.name
        table2.lastname
    
    left join table2
        on table1.client_name = table2.lastname
    
    where table1.indicator = '1952423'
    but I also have found out that "requestors" are also stored inside table2...
    I cant do another join table2 on table1.requestor_name = table2.lastname

    I am not sure what the correct way of doing it is, I think I can use IN() but I am not so sure

    Kind Regards,
    Penfold

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try something more like:
    Code:
    SELECT
       foo.doohickey
    ,  bar.fratz
    ,  bar.bazoinga
    ,  baz.fratz
    ,  baz.zippy
       FROM table1 AS foo
       LEFT JOIN table2 AS bar
         ON bar.lastname = foo.client_name
       LEFT JOIN table2 AS baz
          ON baz.wingus = foo.thingy
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    Try something more like:
    Code:
    SELECT
       foo.doohickey
    ,  bar.fratz
    ,  bar.bazoinga
    ,  baz.fratz
    ,  baz.zippy
       FROM table1 AS foo
       LEFT JOIN table2 AS bar
         ON bar.lastname = foo.client_name
       LEFT JOIN table2 AS baz
          ON baz.wingus = foo.thingy
    -PatP
    that was... strangely easier than I thought....
    is it possible to also name like so...

    Code:
    from table1 As main

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Certainly! I wouldn't use a reserved word (such as SELECT) for an alias, but otherwise have at it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2012
    Posts
    63
    is this a reasonable way to do it if there is only one column in the joined table I require?
    or is there a better way of doing that?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In my opinion using JOIN with an alias is the best solution whether you want one column or all of the columns.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2014
    Posts
    1

    zxcv

    very nice posttttttttt







    ________
    shafiq

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example joining table2 once.

    Code:
    SELECT t1.indicator
         , MAX( CASE t2.lastname WHEN t1.client_name
                                 THEN t2.lastname END ) AS client_lastname
         , MAX( CASE t2.lastname WHEN t1.client_name
                                 THEN t2.name     END ) AS client_name
         , MAX( CASE t2.lastname WHEN t1.requestor_name
                                 THEN t2.lastname END ) AS requestor_lastname
         , MAX( CASE t2.lastname WHEN t1.requestor_name
                                 THEN t2.name     END ) AS requestor_name
     FROM  table1 AS t1
     LEFT  JOIN
           table2 AS t2
      ON   t2.lastname IN (t1.client_name , t1.requestor_name)
     WHERE t1.indicator = '1952423'
     GROUP BY
           t1.indicator
    ;

Posting Permissions

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