I want to join to a table, but include All records if the table being joined to is a certain ID-CodePudding

I want to join to a table, but include All records if the table being joined to is a certain ID.

I have a list of records with a type_id:

RECORD
id | type_id
---|---    
1  | 1
2  | 1
3  | 2

TYPE
id | type_desc
---|---
1  | type1
2  | type2
3  | all

USER
id | type_id
---|---
1  | 1
2  | 3
3  | 2

Record to type is one to one, user to type is one to one, and the "Type" on a record has to be 1 or 2. User can be 1, 2 or 3. The way this would go with a normal join is

select * from record r
inner join user u on u.type_id = r.type_id
where u.user_id=:userId

But now I need to factor in that "All" type, and basically just ignore the join/return all results if the user's type is 3.

So if the user being queried is ID 1, only records 1 and 2 (type 1) would be returned. If userId is 3, only record 3 is returned. But if user ID is 2, corrresponding to the "All" type, then 1,2,3 should be returned.

CodePudding user response：

I hope this helps you:

With dtAll as (
  
   select * from user u
   where u.type_id = 3
   limit 1
)

select * from record r
inner join user u on u.type_id = r.type_id or exists( select * from dtAll )