complicated select query

[lead2gold]

i'm having a problem with my select call.... i feel i'm doing to many selects to something that could be merged into one.
here is my current main select statement:

[code]
SELECT team.*,ca.uid, ca.name_f AS capt_f, ca.name_l AS capt_l,co.uid, co.name_f AS cocapt_f, co.name_l AS cocapt_l FROM team LEFT JOIN users AS ca ON ca.uid=team.c_uid LEFT JOIN users AS co ON co.uid=team.co_uid[/code]

Here is the 2 individual selects i preform on every result found from the first one (above):
[code]

/* returns 2006 for the year (this year) and for example purposes, lets say the sid (season identifier) = 1
   and the tid (team identifier) = 4 */
SELECT *,MAX(season.year) as max_year FROM season GROUP BY sid

/* pass the 2 results from the previous select into this one */
SELECT team2season.* FROM team2season WHERE team2season.sid='1' AND team2season.tid='4'
[/code]

If it's possible to merge these into one that would be great. It is very likely that a team isn't associated with a season... so i still need to list these. MAX(season.year) might just return 2005 or 2004 (the last year the team participated). I still need to always list this.

Is what i'm asking doable?

[fenway]

I don't see why not .... but the query that return the session.year could return multiple rows, which is confusing, because you're passing a single sid into the subsequent query. Please clarify.

[lead2gold]

[!--quoteo(post=367228:date=Apr 21 2006, 12:21 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 21 2006, 12:21 PM) [snapback]367228[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I don't see why not .... but the query that return the session.year could return multiple rows, which is confusing, because you're passing a single sid into the subsequent query. Please clarify.
[/quote]

Your right, this select statement doesn't makes sense:
[code]
SELECT *,MAX(season.year) as max_year FROM season GROUP BY sid
[/code]

The table is simple:
[code]
TABLE season
sid BIGINT(20) (auto_increment)
year INT
-- other stuff that isnt important --
END TABLE

TABLE team2season // Many to Many Relational
sid BIGINT(20)
tid BIGINT(20)
-- other stuff that isnt important --
END TABLE

TABLE team
tid BIGINT(20) (auto_increment)
-- other stuff that isnt important --
END TABLE

[/code]

Each year, 1 new entry is entered into this table.
The year of course increments by 1, and the sid number is the record identifier. Depending on whether or not the team comes back the following year, that team may simply just have last years season id in the team2season.

The users will only see the teams that are currently active for the current season. The administrator however can view all the teams and see the last season they played at.

i want the sid # to be the most current year MAX(year).
[code]
SELECT season.sid FROM season WHERE season.year = (SELECT MAX(season.year) FROM season GROUP BY sid)
[/code]
I'm not at home, but is that what i want? (i'm not good once table start getting complicated :))

or should i assume that since the rin has the auto_increment, i can get the latest year by doing this:
[code]
/* pass the 2 results from the previous select into this one [USERS OUTPUT] */
SELECT team2season.* FROM team2season WHERE team2season.sid=(SELECT season.sid FROM season WHERE season.year = (SELECT MAX(season.year) FROM season GROUP BY sid)) AND team2season.tid='4'

/* pass the 2 results from the previous select into this one [ADMIN OUTPUT] */
SELECT team2season.* FROM team2season WHERE team2season.sid=(SELECT MAX(team2season.sid) FROM team2season GROUP BY sid)) AND team2season.tid='4'
[/code]

(assume the tid=4 i got from team.tid on the first major select... eventually i will just merge this all into one select)

I dont' even know if what i wrote will styntatically work or not :) *crosses fingers*

thanks for your help though!

[fenway]

[!--quoteo(post=367281:date=Apr 21 2006, 02:33 PM:name=lead2gold)--][div class=\'quotetop\']QUOTE(lead2gold @ Apr 21 2006, 02:33 PM) [snapback]367281[/snapback][/div][div class=\'quotemain\'][!--quotec--]
i want the sid # to be the most current year MAX(year).
[code]
SELECT season.sid FROM season WHERE season.year = (SELECT MAX(season.year) FROM season GROUP BY sid)
[/code]
I'm not at home, but is that what i want? (i'm not good once table start getting complicated :))
[/quote]
Close... what you want is a single year back for the each session record examined by the outer query:

[code]
SELECT s.sid FROM season AS sWHERE s.year = (SELECT MAX(year) FROM season WHERE sid = s.sid GROUP BY sid )
[/code]

[lead2gold]

Playing around alot i ALMOST got what i wanted:
[code]
$sql =  "SELECT t.*, s.year,s.sid,ca.uid, ca.name_f AS capt_f, ca.name_l AS capt_l,";
$sql .= "co.uid, co.name_f AS cocapt_f, co.name_l AS cocapt_l,";
$sql .= "tier.name AS tier_name, trans.code_trans as div_name";        
$sql .= " FROM team AS t";
$sql .= " LEFT JOIN users AS ca ON ca.uid=t.c_uid";
$sql .= " LEFT JOIN users AS co ON co.uid=t.co_uid";
$sql .= " LEFT JOIN trans ON trans.trans_name = 'division' AND trans.code = ";
$sql .= " (SELECT tsb.division FROM team2season AS tsb WHERE tsb.sid = s.sid AND tsb.tid = t.tid)";
$sql .= " LEFT JOIN tier ON tier.tier_id = ";
$sql .= " (SELECT tsa.tier_id FROM team2season AS tsa WHERE tsa.sid = s.sid AND tsa.tid = t.tid)";    
$sql .= " INNER JOIN season AS s ON s.year = ";
$sql .= " (SELECT MAX(s2.year) FROM season AS s2,team2season AS ts WHERE s2.sid = ts.sid AND ts.tid = t.tid GROUP BY s2.sid)";
[/code]

I need the INNER join at the bottm for 2 of my left joins to work (because they reference the season (s.sid) that i get.

My problem is, i don't know how to have the function still find the ones that don't have a season assigned.
Is this possible?

for example:
Team A -> Season 2006
Team B -> Season 2006
Team C -> Season 2005 (has not renewed yet for 2006)
Team D -> <no season assigned yet>


Assuming the above, only Team A,B and C display, i need D to display as well. I'm not totally sure what INNER join really means, i was just playing around with the different syntaxes. I want the INNER join to behave as a LEFT JOIN basically... but when i make it a LEFT JOIN it complains on 2 other LEFT joins i have that reference information that isn't there yet... (the INNER Join however makes this behave properly) is this making any sense? :)

[fenway]

Well, you definitely want a LEFT JOIN is you want the "non-matching" rows to display no matter what; could you be specific on the error message your getting back?

[lead2gold]

[!--quoteo(post=367876:date=Apr 24 2006, 03:10 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 24 2006, 03:10 AM) [snapback]367876[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Well, you definitely want a LEFT JOIN is you want the "non-matching" rows to display no matter what; could you be specific on the error message your getting back?
[/quote]

Thanks for your help, and your right, my problem was "where" i actually used the left join. (that and it should NOT be an inner join)

[code]
SELECT t.*, s.year,s.sid,ca.uid, ca.name_f AS capt_f, ca.name_l AS capt_l,co.uid, co.name_f AS cocapt_f, co.name_l AS cocapt_l,tier.name AS tier_name, trans.code_trans as div_name
FROM team AS t
LEFT JOIN season AS s ON s.year = (SELECT MAX(s2.year) FROM season AS s2,team2season AS ts WHERE s2.sid = ts.sid AND ts.tid = t.tid GROUP BY s2.sid)
LEFT JOIN users AS ca ON ca.uid=t.c_uid
LEFT JOIN users AS co ON co.uid=t.co_uid
LEFT JOIN trans ON trans.trans_name = 'division' AND trans.code = (SELECT tsb.division FROM team2season AS tsb WHERE tsb.sid = s.sid AND tsb.tid = t.tid)
LEFT JOIN tier ON tier.tier_id = (SELECT tsa.tier_id FROM team2season AS tsa WHERE tsa.sid = s.sid AND tsa.tid = t.tid) ORDER BY s.year DESC,div_name,tier_name
[/code]

This works as long as i aquire the s.sid above all the other left joins that require it already be defined.

[code]
SELECT t.*, s.year,s.sid,ca.uid, ca.name_f AS capt_f, ca.name_l AS capt_l,co.uid, co.name_f AS cocapt_f, co.name_l AS cocapt_l,tier.name AS tier_name, trans.code_trans as div_name
FROM team AS t
LEFT JOIN users AS ca ON ca.uid=t.c_uid
LEFT JOIN users AS co ON co.uid=t.co_uid
LEFT JOIN trans ON trans.trans_name = 'division' AND trans.code = (SELECT tsb.division FROM team2season AS tsb WHERE tsb.sid = s.sid AND tsb.tid = t.tid)
LEFT JOIN tier ON tier.tier_id = (SELECT tsa.tier_id FROM team2season AS tsa WHERE tsa.sid = s.sid AND tsa.tid = t.tid)
LEFT JOIN season AS s ON s.year = (SELECT MAX(s2.year) FROM season AS s2,team2season AS ts WHERE s2.sid = ts.sid AND ts.tid = t.tid GROUP BY s2.sid)
ORDER BY s.year DESC,div_name,tier_name
[/code]
This code here produces this error message : Cross dependency found in OUTER JOIN; examine your ON conditions.

Again.... this was fixed by just moving the final join to become the first join.

Anyways, thanks for your help...
What exactly is the difference between an OUTER , INNER, joins anyways? Through trial an error i have only found uses for LEFT join. Inner join seems to restrict your results to only ones containing data. But the reason why i question, is you can get the same effect by just referencing more tables after the FROM clause.

I mean, whats the difference between
[code]
SELECT a.* FROM a,b WHERE a.rin = b.rin
[/code]
and
[code]
SELECT a.* FROM a INNER JOIN b ON a.rin = b.rin
[/code]

[fenway]

Glad you got it working.

For all intents and purposes, the comma operator is equivalent to JOIN, which is the same as INNER JOIN. That is, it returns on rows from both tables if the condition is met. Thus, the two examples you gave are "equivalent", though I never use the former. It's much cleaner to have an explicit ON clause, and the comma operator can get you into precedence troubles.

LEFT JOIN is LEFT OUTER JOIN... outer joins only with return rows even when only one "side" (left or right, as requested) of the join condition is satisfied, but produce a NULLed out row for the other "side" of the relation. This allows you to find non-matching rows, which is quite useful.

Here's what the manual has to say about this:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

The ON conditional is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table.
[/quote]

Hope that makes sense.