Help with a Select Statement 2

[kernal]

Event table

Person Event
1 Workshop

2 Workshop

3 Workshop

4 Workshop
4 Fair
4 Training

5 Fair

Class Table

Person Class
1 TEST 4

2 ART 5

3 TEST 1
3 TEST 2
3 FILM 1

Results needed:

I need the results to have any person that is in the Event table and Class table where the event is Workshop and the class has TEST or they are in the Event table where the event is Workshop so the output would be:

Person Event Class
1 Workshop TEST 4

2 Workshop (they are in the Class table but their class does not have TEST)

3 Workshop TEST 1
3 Workshop TEST 2 (only show 2 classes since the other class is FILM 1)

4 Workshop (they aren't in the Class table but they are in the Event table and the event is Workshop so I need them in the results)

Thanks for your help. It is really appreciated.

 

[feherke]

Hi

If I understand you correctly, something like this ( at least it seems to work for the test data generated from your sample ) :

[b]select[/b]
Person[teal],[/teal]
e[teal].[/teal]Event[teal],[/teal]
c[teal].[/teal]Class

[b]from[/b] Event e
[b]left join[/b] Class c [b]using[/b] [teal]([/teal]Person[teal])[/teal]
[b]left join[/b] [teal]([/teal]
    [b]select[/b] [b]distinct[/b]
    Person

    [b]from[/b] Class

    [b]where[/b] Class [b]like[/b] [i][green]'TEST %'[/green][/i]
[teal])[/teal] t [b]using[/b] [teal]([/teal]Person[teal])[/teal]

[b]where[/b] e[teal].[/teal]Event [teal]=[/teal] [i][green]'Workshop'[/green][/i]
[b]and[/b] [teal]([/teal]t[teal].[/teal]Person [b]is null or[/b] coalesce[teal]([/teal]c[teal].[/teal]Class[teal],[/teal] [i][green]'TEST 0'[/green][/i][teal])[/teal] [b]like[/b] [i][green]'TEST %'[/green][/i][teal])[/teal]

[b]order by[/b] Person

Feherke.
feherke.ga

 

[venkatpvc]

this below one will work for you need.

SELECT e.[Person], e.[Event], c.[Class]
FROM [Event] e
INNER JOIN (SELECT DISTINCT [Person], [Class] FROM [Class] WHERE [Class] LIKE 'TEST%') c
ON e.[Person] = c.[Person];

 

[feherke]

Hi

CODE --> [highlight]MySQL[/highlight]

[tt]SELECT e.[highlight pink][[/highlight]Person[highlight pink]][/highlight], e.[highlight pink][[/highlight]Event[highlight pink]][/highlight], c.[highlight pink][[/highlight]Class[highlight pink]][/highlight][/tt]

Are you sure ? The MySQL 5.6 I use complains for syntax error, because

The identifier quote character is the backtick (“`”):

( MySQL 5.6 Reference Manual | Language Structure | Schema Object Names )

But anyway, even if you remove all those pointless MSSQL-style identifier quoting, your query does not return Event.Person 2 and 4 as specified in the question at "Results needed".


Feherke.
feherke.ga

 

[kernal]

Thanks all for helping me.