Join
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
Join
Hi All
I hope everyone is staying safe and making most of the extra time they have at home.
I am trying to write an SQL query with little luck:
Aim:
To retrieve data from two tables e.g.
Table: PERSONS
ID......Name......Age
1.......Ben.........12
2.......Peter........21
3.......Paul.........32
Table: SPORTS
Person_ID......Sport
1................Football
2................Climbing
2................Swimming
3................Tennis
From table 1 I would like to select Peters Name and Age
From Table 2 I would like to select Peters Sport
Ideally the data will finish up as:
Peter, 21, Climbing
Peter, 21, Swimming
I believe a Select and Join statement is required but I cannot workout how it is formed.
Any help on how to do this would be very much appreciated.
Many Thanks
I hope everyone is staying safe and making most of the extra time they have at home.
I am trying to write an SQL query with little luck:
Aim:
To retrieve data from two tables e.g.
Table: PERSONS
ID......Name......Age
1.......Ben.........12
2.......Peter........21
3.......Paul.........32
Table: SPORTS
Person_ID......Sport
1................Football
2................Climbing
2................Swimming
3................Tennis
From table 1 I would like to select Peters Name and Age
From Table 2 I would like to select Peters Sport
Ideally the data will finish up as:
Peter, 21, Climbing
Peter, 21, Swimming
I believe a Select and Join statement is required but I cannot workout how it is formed.
Any help on how to do this would be very much appreciated.
Many Thanks
-
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: Join
Maybe something like this:
Code: Select all
SELECT PERSONS.ID,PERSONS.Name, PERSONS.Age, SPORTS.Sport
FROM PERSONS
INNER JOIN SPORTS ON PERSONS.id=SPORTS.Persons_id
WHERE PERSONS.Name = "Peter";
-
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: Join
..it occurs to me that you might be trying to do a many-to-many relationship in your database: where ideally there should be one peter, and one Football, you might be trying to do something like having many Footballs for all of the people (Football/Person_id.Ben, Football/Person_id.Peter, etc)
One solution is to create an additional table to use as a join table. You could name it "registrations" or "participation" or whatever you like. It could look like this:
Table: PARTICIPATION
ID......sport_id......person_id
1.........1............12
2.........2............21
3.........2............12
One solution is to create an additional table to use as a join table. You could name it "registrations" or "participation" or whatever you like. It could look like this:
Table: PARTICIPATION
ID......sport_id......person_id
1.........1............12
2.........2............21
3.........2............12
Re: Join
There are some possibilities to help, but the best bet is to read up about join types (and the rest of sql generally) here https://www.w3schools.com/sql/sql_join.asp
Re: Join
Thank you very much guys
ghettocottage you are correct, I was already using a 'linking table' but just used the example given on here as a simplified example.
I was previously picking data from each table and combining the data which would become a problem once I have many lines of data... hence looking at the INNER JOIN option, I could not for the life of me work out how to add a WHERE statement to the INNER JOIN which your example illustrates perfectly.
Thank you very much!!!!
ghettocottage you are correct, I was already using a 'linking table' but just used the example given on here as a simplified example.
I was previously picking data from each table and combining the data which would become a problem once I have many lines of data... hence looking at the INNER JOIN option, I could not for the life of me work out how to add a WHERE statement to the INNER JOIN which your example illustrates perfectly.
Thank you very much!!!!
Re: Join
Yeah!SparkOut wrote: ↑Sun Mar 29, 2020 10:40 amThere are some possibilities to help, but the best bet is to read up about join types (and the rest of sql generally) here https://www.w3schools.com/sql/
Whenever I need to work with databases, I first visit these pages to refresh my wimpy SQL knowledge!