Join

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
hrcap
Posts: 93
Joined: Mon Jan 14, 2019 5:20 pm

Join

Post by hrcap » Sat Mar 28, 2020 3:35 pm

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

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 362
Joined: Tue Apr 10, 2012 9:18 am

Re: Join

Post by ghettocottage » Sat Mar 28, 2020 7:24 pm

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

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 362
Joined: Tue Apr 10, 2012 9:18 am

Re: Join

Post by ghettocottage » Sun Mar 29, 2020 1:59 am

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

SparkOut
Posts: 2408
Joined: Sun Sep 23, 2007 4:58 pm

Re: Join

Post by SparkOut » 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/sql_join.asp

hrcap
Posts: 93
Joined: Mon Jan 14, 2019 5:20 pm

Re: Join

Post by hrcap » Sun Mar 29, 2020 3:56 pm

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

Klaus
Posts: 12207
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Join

Post by Klaus » Sun Mar 29, 2020 7:01 pm

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

Post Reply

Return to “Databases”