Jump to content


Photo

complicated select query


  • Please log in to reply
7 replies to this topic

#1 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 19 April 2006 - 08:07 PM

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:

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

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

/* 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'

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 April 2006 - 04:21 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 21 April 2006 - 07:33 PM

[!--quoteo(post=367228:date=Apr 21 2006, 12:21 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 21 2006, 12:21 PM) View Post[/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:
SELECT *,MAX(season.year) as max_year FROM season GROUP BY sid

The table is simple:
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


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).
SELECT season.sid FROM season WHERE season.year = (SELECT MAX(season.year) FROM season GROUP BY sid) 
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:
/* 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'

(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!

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 April 2006 - 06:19 PM

[!--quoteo(post=367281:date=Apr 21 2006, 02:33 PM:name=lead2gold)--][div class=\'quotetop\']QUOTE(lead2gold @ Apr 21 2006, 02:33 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
i want the sid # to be the most current year MAX(year).
SELECT season.sid FROM season WHERE season.year = (SELECT MAX(season.year) FROM season GROUP BY sid) 
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:

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 24 April 2006 - 02:06 AM

Playing around alot i ALMOST got what i wanted:
$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)";

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? :)

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 April 2006 - 07:10 AM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 26 April 2006 - 02:57 AM

[!--quoteo(post=367876:date=Apr 24 2006, 03:10 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 24 2006, 03:10 AM) View Post[/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)

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

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

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
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
SELECT a.* FROM a,b WHERE a.rin = b.rin
and
SELECT a.* FROM a INNER JOIN b ON a.rin = b.rin


#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 April 2006 - 08:48 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users