I need a Query for it.

[essa2000]

Dears ;
I need a query:
Conditions :

I have three tables T1 , T2 and T3

Structure :
T1 :
ID1 Text1
1 ABC
2 XYZ

T2 :
ID2 ID1 ID3
1 1 2
2 1 3

T3 :

ID3 Text3
1 Snap
2 Stud
3 Custom
4 Knote

T1 is a master table and T2 is a table to make many to many relations. I need a query which gives me following results :
I want to populate T3 in a grid which has a first column as checkbox and I want to check that record which are present in T2. I want results as :

CheckBit Text3 ID3
0 Snap 1
1 Stud 2
1 Custome 3
0 Knote 4


Please help me out to solve this problem. I have tried all joins and Select Case statement but I am failed to solve this problem.

essa2000

 

[Guest_imported]

select id2, text3, id3
from (t3 left join t2 on t3.id3 = t2.id3)

this should give you

null Snap 1
1 Stud 2
2 Custome 3
null Knote 4

then you can code the app to check the box on a non null character. It's possible that the checkbox column will equate null to false and not null to true. if your using ado try to clip text into the grid.

 

[essa2000]

Dear keithaymar
Thanks for help me out but I have already checked that Query and is working fine but When I used where cluase with this query it gives only two record :

I used

select id2, text3, id3
from (t3 left join t2 on t3.id3 = t2.id3)
where T2.ID1 = 1

It gives only two records :

CheckBit Text3 ID3
1 Stud 2
1 Custome 3

And I need all four records the rest with 0 bit , with that where clause . Please help in this regard.

I used it with Select Case as

Select 'CheckBit' =
case
When T2.ID2 is null Then cast(0 as bit)
Else cast(1 as bit)
End ,
Text3 , ID3
From T3 left join T2 on T3.ID3 = T2.ID3

It gives me my required result but when I used where cluase with that above query as

Select 'CheckBit' =
case
When T2.ID2 is null Then cast(0 as bit)
Else cast(1 as bit)
End ,
Text3 , ID3
From T3 left join T2 on T3.ID3 = T2.ID3
Where T2.ID1 = 1

It gives me only 2 records. I need all four records with where clause.


essa2000

 

[MalcolmW]

try
select id2, text3, id3
from (t3 left join t2 on t3.id3 = t2.id3
and t2.ID1 = 1)
Malcolm