Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Location
    Ahmedabad
    Posts
    4

    Unanswered: Joins using case or if statement

    Hello All,

    I have three tables in my database which I used in one query.

    Code:
    select * from a
    LEFT JOIN p ON a.id = p.id
    LEFT JOIN q ON a.id = q.id
    This works fine. Now I want to apply this joins at conditional level.
    Is it possible to apply join using case or if condition. Something like below query, I know this is not working.. just for example

    Code:
    select * from a
    (CASE field1 WHEN 'Yes' THEN 
    LEFT JOIN p ON a.id = p.id
     ELSE 
    LEFT JOIN q ON a.id = q.id
     END CASE)
    Would appreciated any kind of help.
    Thanks in Advance.

    Regards,

  2. #2
    Join Date
    Sep 2009
    Posts
    64
    Not sure if it's possible. But you can do this instead,

    Code:
    SELECT
    	*
    FROM
    	a
    	LEFT JOIN p ON field1 = 'Yes' AND p.id = a.id
    	LEFT JOIN q ON field1 <> 'Yes' AND q.id = a.id

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT * 
      FROM a
    LEFT OUTER
      JOIN p 
        ON p.id = a.id
     WHERE a.field1 = 'Yes' 
    UNION ALL
    SELECT * 
      FROM a
    LEFT OUTER
      JOIN q 
        ON q.id = a.id
     WHERE a.field1 <> 'Yes'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2009
    Location
    Ahmedabad
    Posts
    4
    Thanks for your reply.

    I was little aware about this solution. But in this case both tables should scan each time, I'm not much aware about the suggestion which I have provided with case statement. But I need any solution if available so unnecessary other table can't scan each time..

    Regards,

Posting Permissions

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