MYSQL: select values when found in join, placeholder otherwise

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:

M_ID TimeSec
1 30.95
3 29.33

Desired result:

M_ID TimeSec
1 30.95
2 0 (or NULL)
3 29.33

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!

Answer

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' 

Leave a Reply

Your email address will not be published. Required fields are marked *