I have a MYSQL database with three tables: MEETS, SWIMMERS, SWIMS, which I use to store a swim team’s results.
What I am trying to do is write a select query which, for a given swimmer and event, generates a column which has one entry for each meet in the database – if the swimmer actually swam the event at the meet, select the time, if not, select a placeholder. Basically, I’m trying to de-raggedize my data:
I’m able to join the tables successfully, but what’s below only returns the Meets where the swimmer HAS swum the event, not all meets. (I realize this is kind of a silly thing to need, but the graphing control I’m trying to feed it into is really finicky about what it will accept)
edit: Attempting to do better with providing a miniminal example. Here are the CREATE and INSERT queries for the three tables:
CREATE TABLE MEETS ( M_ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT ); CREATE TABLE SWIMMERS ( SwimmerId INTEGER PRIMARY KEY AUTOINCREMENT, FirstName TEXT, LastName TEXT ); CREATE TABLE SWIMS ( SwimID INTEGER PRIMARY KEY AUTOINCREMENT, SwimmerID INTEGER, MeetID INTEGER, Event TEXT, TimeSec REAL ); INSERT into MEETS values (null,'Meet1'); INSERT into MEETS values (null,'Meet2'); INSERT into MEETS values (null,'Meet3'); INSERT into SWIMMERS values(null,'Fred','Barnes'); INSERT into SWIMS values(null,1,1,'50 Free',30.95); INSERT into SWIMS values(null,1,2,'100 Free',66.25); INSERT into SWIMS values(null,1,4,'50 Free',29.33);
Here’s my test query for a particular swimmer and event:
SELECT B.M_ID , A.TimeSec FROM SWIMS AS A LEFT JOIN MEETS AS B ON B.M_ID = A.MeetId WHERE A.SwimmerID = 1 and A.Event = '50 Free'
this results in:
|2||0 (or NULL)|
I think the problem is that the where clause is false on the missing row, but I’m not sure how to solve it. Maybe a union, but I can’t figure out how to structure it. Any help greatly appreciated!
If you need all meets you need to fetch from meets then join the others.
SELECT A.M_ID , B.TimeSec FROM MEETS AS A LEFT JOIN SWIMS AS B ON A.M_ID = B.MeetId AND B.SwimmerID = 1 and B.Event = '50 Free'