How to select an atendee that was in two events

Question

0.00/5 (No votes)

See more:

Hello I have a question about an SQL statement thats been buggin me. I have an app that displays all attendees that attened one or multiple events. If I use 'OR' between the event-ids there is no problem and the query works fine. The problem is when I use AND.

Here is a sample query...

SQL

SELECT c.id_contact, c.name FROM contact c
INNER JOIN applications a ON a.id_contact = c.id_contact
INNER JOIN events e ON e.id_event= a.id_event
WHERE (a.id_event = (event-id) and a.id_event = (another-event-id))

This query returns no results, but there shuld be 1 applicant that was on both events..

Any answer will be appriceated.

Thanks
B

Posted 24-Feb-15 8:06am

pykos

Add a Solution

Comments

Tomas Takac 24-Feb-15 14:15pm

Why do you want AND in the condition? This will always give zero rows because a.id_event cannot have two different values at the same time. What I don't understand is when OR in the conditions gives the results you want why do you try to put AND there in the first place?

pykos 24-Feb-15 14:50pm

Or does not return the result that I want. If there is an or in the query I get all the atendees that were in atlest 1 event. What I want is to get only the atendees that were in two or more events (the user of the app selects wich evnt he wants to filter atendees by)... Im building this query dynamicaly and there are other fields and filters that user can apply.

Shuld I be using a UNION operator and split the query so that there are no AND operators in the WHERE clause?

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2015-02-24T19:42:00

Solution 3

Try this:

SQL

SELECT c.id_contact, c.name FROM contact c
INNER JOIN applications a ON a.id_contact = c.id_contact
WHERE a.id_event = (event-id) and
EXISTS
(
SELECT * FROM applications a2 WHERE a2.id_event = (another-event-id) AND a2.id_contact = a.id_contact)
)

Posted 24-Feb-15 19:42pm

Peter Leow

Comments

pykos 25-Feb-15 2:19am

Yep this works... is there a cleaner/better way to achive the same result?

PIEBALDconsult · Answer 2 · 2015-02-24T08:12:00

Solution 1

WHERE (a.id_event = (event-id) and a.id_event = (another-event-id))

a.id_event can only equal both event-id and another-event-id if all three are the same.

You probably can simplify that by removing the events table from the query and counting how many applications a contact has.

Posted 24-Feb-15 8:12am

PIEBALDconsult

OriginalGriff · Answer 3 · 2015-02-24T08:15:00

Solution 2

Um...unless the two event ID values in your WHERE clause are the same, it will always return no results - since you have a.id_event being compared on both sides of the AND.

Posted 24-Feb-15 8:15am

OriginalGriff

Comments

pykos 24-Feb-15 14:58pm

So how do I solve this problem? I do need this result as I dont think that this imposible to achive? Multiple statements and UNION between them?

OriginalGriff 24-Feb-15 15:52pm

You need *a* result, but it probably isn't that one! :laugh:
I don't know your system, or exactly what you are trying to do, so I can't just say "do this" - you need to look at your data closely and work out what condition you need. But you can't ask for the same column value to be two different values at the same time.

aravinth04 · Answer 4 · 2015-02-25T19:55:00

Solution 4

You can use IN contion also. Please try this

SELECT c.id_contact, c.name FROM contact c
INNER JOIN applications a ON a.id_contact = c.id_contact
WHERE a.id_event in (event-id,another-event-id)

Posted 25-Feb-15 19:55pm

aravinth04