Question Query to select from 2 tables (info inside)

NoobCube

Member
Joined
Mar 2, 2014
Messages
12
Programming Experience
Beginner
Hello!

I don't even know what to search in google for this question, so I hope someone here can help.

What I want to do is:
I have two tables: Clients and Log
they both have ID field which is the same

Clients table has list of all clients with their details and there is field called IsActive that determine if client should be shown or not.
Log table got list of some Clients ID's but not all the Clients table ID's so if Clients table has 10 clients, Log table can have from 1 - 10 clients id's in it.

I want to do some kind of JOIN or something that will select all the clients from Clients Table and set their IsActive status to be T/F depends on if their ID is exist in the Log table.

For example:

Clients table:
IDNameIsActive
1DanTrue
2BruceTrue
3JohnTrue
4BobTrue
5ArthurFalse
6JackFalse

Log Table:
ID
1
3
5
6

Query result will be:
IDNameIsActive
1DanTrue
2BruceFalse
3JohnTrue
4BobFalse
5ArthurTrue
6JackTrue


1 exist in Log table
2 not exist in Log table so it set to False
3 exist in Log table
4 not exist in Log table so it set to False
5 exist in Log table so it became True even if it was False
6 exist in Log table so it became True even if it was False

It will return Clients table but IsActive values will change depends on if that ID exist in Log table.
You can see the red values that was changed because client id was in Log table.
The result table IsActive row has nothing to do with the Clients table IsActive, it will be created by the query.

I am using SQL CE, C# 4.5

Please help! I don't have a clue how to do it :confusion:

Thanks!
 

NoobCube

Member
Joined
Mar 2, 2014
Messages
12
Programming Experience
Beginner
After asking in C# IRC channel I got the answer:

C#:
[COLOR=#0000ff]SELECT[/COLOR] ID,[COLOR=#0000ff] CASE WHEN EXISTS[/COLOR] ([COLOR=#0000ff]SELECT[/COLOR] ID [COLOR=#0000ff]FROM[/COLOR] Log [COLOR=#0000ff]AS[/COLOR] b [COLOR=#0000ff]WHERE[/COLOR] b.ID = a.ID) [COLOR=#0000ff]THEN[/COLOR] 1 [COLOR=#0000ff]ELSE[/COLOR] 0 [COLOR=#0000ff]END[/COLOR] [COLOR=#0000ff]AS[/COLOR] IsActive [COLOR=#0000ff]FROM [/COLOR]Clients [COLOR=#0000ff]AS [/COLOR]a
 
Top Bottom