I have two tables calendar and team, to simplify : in
calendar table i have
date, and in
team table i have
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
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