英文:
Join 3 tables in postgresql on different objects
问题
我有一个包含3个表的数据框,让我们称它们为People、Event和Outcome。这些表的设置如下:
People表包含:ID、Name、Age;
Outcome表包含:ID、EventID、EventTime和OutcomeID;
Event表包含:EventID、EventState、EventDate和EventTemp。
我需要运行一个查询,以获取"Sally"参加的所有事件,并输出EventName、Event Month(从EventDate中提取)、EventTemp和EventTime。但我遇到的问题是,我需要在Event和Outcome表上根据EventID进行连接,然后在People和Outcome表上根据ID进行连接。
以下是我上次尝试的查询(不起作用):
SELECT eventname, eventstate, EXTRACT(MONTH FROM eventdate), eventtemp
FROM event E JOIN outcome O ON E.eventid = O.eventid
FROM name N JOIN outcome O ON N.id = O.id
WHERE name = "Sally";
这没有输出任何内容,因为它引发了错误。我是PostgreSQL的新手。有人可以帮助吗?
英文:
I have a dataframe with 3 tables, lets call them People, Event, Outcome. The setup for these tables would look like this:
Name has: ID, Name, Age; Outcome has: ID, EventID, EventTime and OutcomeID; Event has: EventID, EventState, EventDate, EventTemp.
I need to run a query that pulls in all the Events that "Sally" competed in and output the EventName, Event Month (extracted from the EventDate), EventTemp, and EventTime. But this issue I'm running into is I need to join Event and Outcome on the EventID and then People and Outcome on the ID.
Here is what I last tried (which isn't working):
SELECT eventname, eventstate, EXTRACT(MONTH FROM eventdate), eventtemp
FROM event E JOIN outcome O ON E.eventid = O.eventid
FROM name N JOIN outcome O ON N.id = O.id
WHERE name = "Sally";
This is not outputting anything because it throws an error. I am new to postgresql. Can someone help?
答案1
得分: 2
只能有一个FROM子句,虽然它可以包含多个连接。我假设“name”字段在“name”表内:
SELECT E.eventname, E.eventstate, EXTRACT(MONTH FROM E.eventdate), E.eventtemp
FROM event E JOIN outcome O ON E.eventid = O.eventid
JOIN name N ON N.id = O.id
WHERE N.name = 'Sally';
英文:
There can only be one FROM clause, although it can contain multiple JOINs. I'm assuming that the "name" field is inside the "name" table:
SELECT E.eventname, E.eventstate, EXTRACT(MONTH FROM E.eventdate), E.eventtemp
FROM event E JOIN outcome O ON E.eventid = O.eventid
JOIN name N ON N.id = O.id
WHERE N.name = 'Sally';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论