INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with a Select Statement

Help with a Select Statement

(OP)
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.

RE: Help with a Select Statement

Hi

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

CODE --> MySQL

select
Person,
e.Event,
c.Class

from Event e
left join Class c using (Person)
left join (
    select distinct
    Person

    from Class

    where Class like 'TEST %'
) t using (Person)

where e.Event = 'Workshop'
and (t.Person is null or coalesce(c.Class, 'TEST 0') like 'TEST %')

order by Person 

Feherke.
feherke.ga

RE: Help with a Select Statement

this below one will work for you need.

CODE --> MySQL

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]; 

RE: Help with a Select Statement

Hi

Quote (venkatpvc)

CODE --> MySQL

SELECT e.[Person], e.[Event], c.[Class]

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

Quote (Schema Object Names)

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

RE: Help with a Select Statement

(OP)
Thanks all for helping me.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close