如何选择两个事件中的参加者 [英] How to select an atendee that was in two events

问题描述

您好我有一个关于SQL语句的问题，这些问题一直困扰着我。我有一个应用程序，显示参加一个或多个事件的所有与会者。如果我在事件ID之间使用OR，则没有问题，查询工作正常。问题是当我使用AND时。



这是一个示例查询...

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...

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))

此查询不返回任何结果，但是有两个事件的申请人。 。



任何答案都会被评定。



谢谢

B

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

Any answer will be appriceated.

Thanks
B

推荐答案

试试这个：

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)
)

WHERE（a.id_event =（event-id）和a.id_event =（another-event-id））



a.id_event 只能等于两个 事件-id 和 another-event-id 如果所有三个都相同。





你可能可以简化它通过从查询中删除事件表并计算联系人拥有的应用程序数量。

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.

嗯...除非WHERE子句中的两个事件ID值相同，否则它将始终返回没有结果 - 因为您在AND的两边比较了 a.id_event 。

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.

这篇关于如何选择两个事件中的参加者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！