Complex JOIN, Subquery

[JipThePeople]

I get an error "#1241 - Operand should contain 1 column(s) " when executing this code:

 

SELECT `game_datetime`, `home_team_temp`, `visiting_team_temp`, (SELECT CONCAT(`mascot`, ' ', `city`) AS `home_team`, `logo_url` AS `home_logo_url` FROM `teams`, `logos`, `schedules` WHERE `teams`.`id` = `logos`.`team_id` AND `teams`.`id` = `schedules`.`home_team_id`), (SELECT CONCAT(`mascot`, ' ', `city`) AS `visiting_team`, `logo_url` AS `visiting_logo_url` FROM `teams`, `logos`, `schedules` WHERE `teams`.`id` = `logos`.`team_id` AND `teams`.`id` = `schedules`.`visiting_team_id`) FROM `schedules`, `teams` WHERE `teams`.`id` = `schedules`.`visiting_team_id` OR `teams`.`id` = `schedules`.`home_team_id`

 

Any suggestions?

[mikosiko]

Error is due to that you are selecting more than 1 column in your sub-queries... that is not allowed in that context.

[JipThePeople]

Error is due to that you are selecting more than 1 column in your sub-queries... that is not allowed in that context.

 

Thx for the reply. I am trying to query multiple tables

 

I have a 'teams' table that has columns for 'id' and 'city' + 'mascot' = team name. I have another table, 'schedules' that has records containing the two teams (id's) that are to play. I am trying to accomplish this in a single query rather than two. Any recommendations? Thx again for your time.

 

[fenway]

I am so very confused -- why not simply join the team table twice, one for home, once for away/

[JipThePeople]

I am so very confused -- why not simply join the team table twice, one for home, once for away/

 

Can you please give me an example? Won't two JOINS = 2 queries? I am trying to keep it to a single query.

[fenway]

SELECT game_datetime, home_team_temp, visiting_team_temp
, CONCAT(h.mascot, ' ', h.city) AS home_team, hl.logo_url AS home_logo_url
, CONCAT(v.mascot, ' ', v.city) AS visiting_team, vl.logo_url AS visiting_logo_url
FROM schedules
INNER JOIN teams AS h ON ( h.id = schedules.home_team_id )
INNER JOIN logos AS hl ON (  hl.id  = schedules.home_team_id )
INNER JOIN teams AS v  ( h.id = schedules.visiting_team_id )
INNER JOIN logos AS hl ON (  hl.id = schedules.visiting_team_id )

[JipThePeople]

SELECT game_datetime, home_team_temp, visiting_team_temp
, CONCAT(h.mascot, ' ', h.city) AS home_team, hl.logo_url AS home_logo_url
, CONCAT(v.mascot, ' ', v.city) AS visiting_team, vl.logo_url AS visiting_logo_url
FROM schedules
INNER JOIN teams AS h ON ( h.id = schedules.home_team_id )
INNER JOIN logos AS hl ON (  hl.id  = schedules.home_team_id )
INNER JOIN teams AS v  ( h.id = schedules.visiting_team_id )
INNER JOIN logos AS hl ON (  hl.id = schedules.visiting_team_id )

 

Thx, that was exactly what I needed. I had to tweak a things (h1.id, h1 duplicated - changed to v1, v1.id) but it works great. Thanks for the solution!