CODEWITHSUNDEEP
sqlsql-server
Abhi
Abhi

Reputation: 6568

inner join on two columns of same table on another table including with OR condition

Table 1 - USERS:

user_id | email_id         | type
---------------------------------------
000121  | [email protected] | EXT
000125  | [email protected] | LOT
000128  | [email protected] | EXT

Table 2- HISTORY:

old_user_id | new_user_id | another_type
----------------------------------------
000128      | 000121      | INIT

I have email_id in hand say [email protected], I want to do a join query that should return 1, if the user_email_id I had, is in USERS table with EXT as TYPE and in HISTORY table with ANOTHER_TYPE as INIT;

I have a query with a single join for old_user_id .

IF EXISTS (SELECT 1 FROM   history cmr (nolock) 
                  INNER JOIN users au (nolock) 
                          ON au.user_id = cmr.old_user_id 
                  AND cmr.another_type = 'INIT  'AND au.type = 'EXT' 
                   AND au.email_id = '[email protected]') 
  SELECT 1 ELSE   SELECT 0

When I query [email protected] or [email protected], it should return 1. I want to add the join condition for new_user_id also .

So either user (old or new) should be EXT in USERS, and should be INIT in HISTORY

Thanks in advance

Upvotes: 2

Views: 13981

Answers (3)

Sunny
Sunny

Reputation: 4809

Try this:

IF EXISTS (SELECT 1 
           FROM 
                history cmr (nolock) INNER JOIN 
                users au (nolock) 
           ON 
                (au.user_id = cmr.old_user_id or au.user_id = cmr.new_user_id)
           WHERE 
                  cmr.another_type = 'INIT' AND 
                  au.type = 'EXT' AND 
                  au.email_id = '[email protected]') 
  SELECT 1 
ELSE   
  SELECT 0

Upvotes: 2

bonCodigo
bonCodigo

Reputation: 14361

Use case when: BTW user is a reserved keyword. So you may want to use square brackets with it.

Query 1:

SELECT case when 
       EXISTS (Select * From historyT cmr 
       Left Join userT au  
       On (au.user_id = cmr.old_user_id  
       And cmr.another_type = 'INIT'
       And au.type = 'EXT'  
       And au.email_id = '[email protected]')) 
       Then 1
       Else 0
End  Status               
;

Query2:

SELECT case when 
   EXISTS (Select * From historyT cmr 
   Left Join userT au  
   On (au.user_id = cmr.old_user_id  
   And cmr.another_type = 'INIT'
   And au.type = 'EXT'  
   And au.email_id = '[email protected]') 
   Left Join userT au2  
   On (au2.user_id = cmr.new_user_id  
   And cmr.another_type = 'INIT' 
   And au2.type = 'EXT'  
   And au2.email_id = '[email protected]'
   And (au.user_id is Not Null) 
   or (au2.user_id is Not Null)))
   Then 1
   Else 0

End Status ;

Results:

STATUS
1

Upvotes: 2

bummi
bummi

Reputation: 27377

IF EXISTS 
(
SELECT * FROM   history cmr 
Left JOIN users au  ON au.user_id = cmr.old_user_id  AND cmr.another_type = 'INIT  'AND au.type = 'EXT'  AND au.email_id = '[email protected]' 
Left JOIN users au2  ON au2.user_id = cmr.new_user_id  AND cmr.another_type = 'INIT  'AND au2.type = 'EXT'  AND au2.email_id = '[email protected]'
Where (au.user_id is NOT NULL) or (au2.user_id is NOT NULL)
)
SELECT 1 ELSE   SELECT 0

Upvotes: 4

Related Questions