CODEWITHSUNDEEP
sqlsql-server-2008-r2
user3191666
user3191666

Reputation: 159

SQL query to display list items

Okay, I am trying to write a query from a poor table structure.

Below is something that I want to achieve:

Table 1: List of Items

T1C1  T1C2   

  A     Fred
  B     Bart
  C     Carl

Table 2: Second list of Items

T2C1  T2C2

  1     Chocolate   
  2     Cake        
  3     Pie     
  4     Fish    
  5     Pizza

Table 3: Joining table

T3C1  T3C2   T3C3  

  1     A     Y
  4     A     Y 
  5     A     N
  1     B     N
  2     B     Y
  5     B     Y
  1     C     Y
  2     C     N
  3     C     Y

Result Select query based on a person in Table 1. However if the value is not in Table 3 then R1C4 should default to N

R1C1  R1C2          R1C3   R1C4

  1     Chocolate     A    Y
  2     Cake          A    N
  3     Pie           A    N
  4     Fish          A    Y
  5     Pizza         A    N

Upvotes: 1

Views: 1520

Answers (2)

Vikram Singh
Vikram Singh

Reputation: 56

declare @filter varchar(1)
select @filter= T1C1 from Table_1 where T1C2='Fred';

with CteResult (R1C1,R1C2,R1C3,R1C4)as
(select  T2C1 as R1C1,T2C2 as R1C2,T3C2 as R1C3, T3C3 as R1C4 from table_2 A
inner join Table_3 B on T2C1  =T3C1 
where T3C2=@filter)
select T2C1 as R1C1,T2C2 as R1C2,coalesce(R1C3,@filter) as R1C3,
case when R1C4 is null then 'N' else R1C4 end R1C4  
from table_2 A left outer join CteResult B
on B.R1C1=A.T2C1

Upvotes: 0

Kevin Hogg
Kevin Hogg

Reputation: 1781

I've assumed a few details to come up with the following:

DECLARE @UserId CHAR
SET @UserId = 'A'

SELECT T2.T2C1 AS R1C1,
       T2.T2C2 AS R1C2,
       COALESCE(T3.T3C2, @UserId) AS R1C3,
       COALESCE(T3.T3C3, 'N') AS R1C4
  FROM Table2 AS T2
       LEFT JOIN Table3 AS T3 ON T3.T3C1 = T2.T2C1 AND T3.T3C2 = @UserId

Assumption: example output is for user Fred.

To ensure that we see all entries from Table2 we use a LEFT JOIN on the link table (Table3).

We have to COALESCE the values for the two records that don't exist in the link table.

Note that this only works if we filter to one user, as per the expected output.

View my SQL Fiddle for full example.

Upvotes: 1

Related Questions