在PostgreSQL中连接三个不同对象的表格。

huangapple go评论59阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年2月18日 09:22:15
  • 转载请务必保留本文链接:https://go.coder-hub.com/75490594.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定