Zymic Webmaster Forums

Full Version: Sql Help

Zymic Webmaster Forums > Web Design & Development > Server Side Scripting > Other Languages

LimitedEdition

Oct 8 2007, 02:39 PM

Anyone here good with SQL ? ... i need some help understanding how UNIONS work.

This is what i have atm ...

CODE

My Query:

SQL

(

SELECT fixture_datetime AS fixture_datetime_a, team_id AS team_id_a , team_name AS team_name_a, NULL AS fixture_datetime_b, NULL AS team_id_b , NULL AS team_name_b

FROM scoreboard_fixtures JOIN scoreboard_teams ON scoreboard_teams.team_id = scoreboard_fixtures.fixture_team_a

)

UNION

(

SELECT NULL AS fixture_datetime_a, NULL AS team_id_a , NULL AS team_name_a, fixture_datetime AS fixture_datetime_b, team_id AS team_id_b , team_name AS team_name_b

FROM scoreboard_fixtures JOIN scoreboard_teams ON scoreboard_teams.team_id = scoreboard_fixtures.fixture_team_b

)

When i run this query i get 2 rows insted of one row, any ideas where im going wrong ... all i basically want is one row per fixture where in that row i have info about both teams and the fixture date.

Oct 8 2007, 03:52 PM

I'd be very much inclined to use a left join rather than a union, something like:

SQL

SELECT fixtures.date, t1.name AS teama, t2.name AS teamb
FROM fixtures LEFT JOIN teams t1 ON fixtures.team_a = t1.id
LEFT JOIN teams t2 ON fixtures.team_b = t2.id

That should do the trick.

LimitedEdition

Oct 9 2007, 08:31 AM

Thanks, im not sure about how that would work, would t1 and t2 be the team table ? ... i havent come across LEFT JOINs before.

Ive update my first post with the actualy table cols.

I did manage to get what i wanted the long way around like so ...

SQL

SELECT *

FROM ( SELECT fixture_id, fixture_datetime AS fixture_datetime_a, team_id AS team_id_a, team_division AS team_division_a, team_name AS team_name_a, team_captain AS team_captain_a

FROM scoreboard_fixtures INNER JOIN scoreboard_teams ON scoreboard_teams.team_id = scoreboard_fixtures.fixture_team_a

)

AS team_a,

(

SELECT fixture_id, fixture_datetime AS fixture_datetime_b, team_id AS team_id_b, team_division AS team_division_b, team_name AS team_name_b, team_captain AS team_captain_b

FROM scoreboard_fixtures INNER JOIN scoreboard_teams ON scoreboard_teams.team_id = scoreboard_fixtures.fixture_team_b

)

AS team_b

WHERE team_a.fixture_id = team_b.fixture_id

Ok, so i had a play with what you suggested and works perfectly, thankyou, but i have no idea how it works lol

this is the final query.

SQL

SELECT scoreboard_fixtures.fixture_datetime, t1.team_name AS team_name_a, t2.team_name AS team_name_b

FROM scoreboard_fixtures LEFT JOIN scoreboard_teams t1 ON scoreboard_fixtures.fixture_team_a = t1.team_id

LEFT JOIN scoreboard_teams t2 ON scoreboard_fixtures.fixture_team_b = t2.team_id

LimitedEdition

Oct 9 2007, 09:30 AM

so now that works all good, i now need to include a results table to that same query :/ ....

Results table looks like this

CODE

I had a go working from the previous query ...

SQL

SELECT

scoreboard_results.result_id,

scoreboard_fixtures.fixture_datetime,

t1.team_name AS team_name_a,

scoreboard_results.result_team_a_score AS team_a_score,

t2.team_name AS team_name_b,

scoreboard_results.result_team_b_score AS team_b_score

FROM scoreboard_results INNER JOIN scoreboard_fixtures ON scoreboard_fixtures.fixture_id = scoreboard_results.result_match

LEFT JOIN scoreboard_teams t1 ON scoreboard_fixtures.fixture_team_a = t1.team_id

LEFT JOIN scoreboard_teams t2 ON scoreboard_fixtures.fixture_team_b = t2.team_id

works! ... thought it post the final stuff just in case anyone else was stuck with something similar.

Oct 9 2007, 04:20 PM

You probably want to use an 'INNER JOIN' for the other bits I used LEFT JOIN on. A LEFT JOIN would include results that did not have a matching row from the LEFT TABLE, hence the 'LEFT JOIN', 'RIGHT JOIN' is the opposite, while highly unlikely, best to avoid it.

Yeah, the 't1' and 't2' are aliases, if they weren't provided you'd get complaints of it being not unique / ambiguous. You can use 'AS' here if you think it would make it clearer:

example:

SQL

SELECT fixtures.date, t1.name AS teama, t2.name AS teamb
FROM fixtures INNER JOIN teams AS t1 ON fixtures.team_a = t1.id
INNER JOIN teams AS t2 ON fixtures.team_b = t2.id

If you wanted an even shorter form:

SQL

SELECT fixtures.date, t1.name as teama, t2.name AS teamb
FROM fixtures INNER JOIN(teams t1, teams t2) ON (fixtures.team_a = t1.id AND fixtures.team_b = t2.id)

Couple of pages on different joins:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php
http://dev.mysql.com/doc/refman/5.0/en/join.html
http://dev.mysql.com/doc/refman/5.0/en/nested-joins.html

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.