Select from two tables by unique Id

Question

I have two tables calendar and team, to simplify : in calendar table i have Id, team_id1, team_id2 and date, and in team table i have Id, name and image. I have to select all columns of calendars and also team names and images associated, i used this query, it works but i think i execute more than 3 queries in one. Can you suggest me more effective please :

SELECT *, 
calendar.team_id1,calendar.team_id2,
(select team.Name from team where team.Id = calendar.team_id1 ) AS 'TeamName1',
(select team.Name from team where team.Id = calendar.team_id2 ) AS 'TeamName2',
(select team.Image from team where team.Id = calendar.team_id1 ) AS 'TeamImage1',
(select team.Image from team where team.Id = calendar.team_id2 ) AS 'TeamImage2'
FROM calendrier ORDER BY calendar.Date DESC

Thank you.

flyingfox · Answer 1 · 2022-10-23T11:12:01.967

Check below sql

SELECT c.team_id1,c.team_id2,
   IF(filter1,t.name,null) AS 'TeamName1',
   IF(filter1,null,t.name) AS 'TeamName2',
   IF(filter1,t.Image,null) AS 'TeamImage1',
   IF(filter1,null,t.Image) AS 'TeamImage2'
   (
    select team.Image，team.Name, (team.Id = calendar.team_id1) as filter1 
      FROM team 
    where team.Id = calendar.team_id1 OR team.Id = calendar.team_id2
    ) as t
JOIN calendrier c  ON c.Id = t.Id
ORDER BY c.Date DESC

In the sql above,we can use OR to combine all subquery,and expose a boolean flag called filter1,and in the outter sql,we can use IF with filter1 to do dynamic query

Note: the sql above is based on mysql,for more common case,we can use CASE WHEN instead

SELECT c.team_id1,c.team_id2,
   CASE WHEN filter1 t.name ELSE null 'TeamName1'
   CASE WHEN filter1 null ELSE t.name 'TeamName2',
   CASE WHEN filter1 t.name ELSE null 'TeamImage1',
   CASE WHEN filter1 null ELSE t.name 'TeamImage2'
   (
    select team.Image，team.Name, (team.Id = calendar.team_id1) as filter1 
      FROM team 
    where team.Id = calendar.team_id1 OR team.Id = calendar.team_id2
    ) as t
JOIN calendrier c  ON c.Id = t.Id
ORDER BY c.Date DESC

Thank you very much sir, but it didn't work when I tested – itdev Oct 24 '22 at 18:12 — itdev, Oct 24 '22 at 18:12

Mario Sergio · Accepted Answer · 2022-10-23T12:05:06.547

I really can´t see a reason to make use of subqueries to solve the related problem.

So that, the solution is a basic query with 2 JOINs on table "team", as follow sql:

SELECT calendar.*, 
team1.Name  AS "TeamName1", team1.Image AS "TeamImage1",
team2.Name  AS "TeamName2", team2.Image AS "TeamImage2"
FROM calendar 
LEFT JOIN team team1 on (team1.Id = calendar.team_id1)
LEFT JOIN team team2 on (team2.Id = calendar.team_id2)
ORDER  BY calendar.Date DESC

See that above I have replaced 4 subSelects for 2 joins clauses, it is more direct and even more efficient.

SubSelects are useful in many situations for select-fields (projection) and where-statament (selection), but not for this simple relation beetwen 2 tables that can be joined by its PK e FK fields.

note: in order to test the syntax of my sugested sql, I use follow DML in h2DB:

create table calendar(
Id integer, team_id1 integer, team_id2 integer, Date Date
);
create table team (
Id int, Name character(200), Image blob
);

You can see discussion about JOIN VS SubQuery here: Join vs. sub-query

Select from two tables by unique Id

2 Answers2