LiveCode Forums

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

Maybe something like this:

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

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

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

SparkOut wrote: ↑

Sun Mar 29, 2020 10:40 am

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/

Yeah!
Whenever I need to work with databases, I first visit these pages to refresh my wimpy SQL knowledge!