Re: Table appears in multiple joins returns null column
Keith, I got it! What you said about replacing one JOIN with another is what did the trick. After a little trial and error (maybe a little more than a little) I got the results I needed. Thanks for the help. In case you're still curious I am modeling short track racing here in New York state. I used NASCAR in the example below of the tables because I thought it might be a bit more familiar. Thanks again. Well done! Sorry about the delay, but when the list went mad it was just too confusing to work out which msgs were 'real' and which were re-mails so wholesale deletion was the order of the day. Thanks for sending the explanations. I enjoy watching car racing/rallying from time-to-time, but am not a great fan. The info might have helped me get my head around the problem, but you beat me to it and so you get the chequered flag! There are several sites around offering tutorials on joins. A simple/trivial join is featured in just about every 'first steps' tutorial, so its not worth visiting too many of those. Go for an intermediate or even an advanced 'course'. The other source of such data is a decent SQL/MySQL book. Many have been recommended - check the list archives. It seems very school-bookish, but it is worth getting your head around some of the set theory behind this stuff, eg do I only want the join when there is data on 'both sides', or where data is 'missing' should the 'left' row be included with a NULL where the right row data would otherwise fit? (says he bravely attempting to repress an attack of the shudders) Originally joins in SQL were all done by adding multiple tables to the FROM clause and at least one (in)equality to the WHERE clause (note the 'two' entries'). The more recent SQL 'standard' introduced specific JOIN clauses, eg INNER JOIN, OUTER JOIN, and a few variants on each theme. Using this syntax the joins become much more obvious/better documented. I (and others from 'the old school') learned the original methods, and still don't seem to have firmly lodged the newer syntax in my tired brain. If you come across a good tutorial online, please share it with me too... Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table appears in multiple joins returns null column
I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table appears in multiple joins returns null column
Keith, I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname Obviously the syntax of the SQL command is ok, else the RDBMS would have told you. The answer may lie in the definitions of the races, types, and pointfund tables; or in the data contained in the join-ing fields (neither of which are shown here). Two suggestions: 1 simplify the query to deal with pointfund and one other first, then add another tbl/join, etc, until you have rebuilt the entire query - your formatting is 'clean' and allows the insertion of # comment symbols at the beginning of lines to remove/return then easily (and without loads of typing/retyping) 2 have you tried running EXPLAIN to see what MySQL thinks of it all? Let us know how you get on! =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table appears in multiple joins returns null column
Hi Keith, Thanks for the responses. The below query works fine: =no problem, but I'm feeling as if I'm blundering around in the dark here, and no one else has jumped in, so I'm back - just not so bright-eyed and bushy tailed as the new morning should have made me... =NB the below-quoted query is NOT the one you used - it has extra commas and ANDs in it! I proceed however on the basis of what you have said. =which tools are you using to work with MySQL? =did you 'reverse engineer' your own queries, by working 'backwards' from two tables only, adding a table at a time until you reached the full joins/query again? (as sugggested last time) =how many rows are present in POINTFUND and how many when you perform*** a query joining POINTFUND and TYPES? How many rows are produced from the currently working query, ie the join between TYPES and all the other tables? ***this pedantic approach because of your observation below (yes I did read it all !) but I'd like the machine to tell us what it is doing/thinking... =is it possible that the intersection of the above does not contain any of the rows produced by joining POINTFUND and TYPES? =if you replace the POINTFUND and TYPES join with a join between POINTFUND and RACES does that make any difference? (logically it should not, but...) =I'm playing with theories here... in fact, pardon my ignorance, what is a 'pointfund' - what sort of parties/races are you taking about here? How practical would it be to zip up some sample table data and the schema so that I could build a replica here and play around with queries and theories (such as above)? =Sorry, if there's something obvious, it hasn't stepped up and bopped me on the nose yet... =dn SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, FROM TYPES, RACES, SCHEDULE, PARTY, WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID AND RACES.TYPE_ID = TYPES.TYPE_ID AND ORDER BY SCHEDULE.date, PARTY.lname The below query returns no records: SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES, RACES, SCHEDULE, PARTY, POINTFUND WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID AND RACES.TYPE_ID = TYPES.TYPE_ID AND POINTFUND.TYPE_ID = TYPES.TYPE_ID ORDER BY SCHEDULE.date, PARTY.lname Here are descriptions of the tables in question. DESCRIBE TYPES Field Type Null Key Default Extra TYPE_ID int(11) PRI 0 typeshort char(3) typelong varchar(45) category tinytext YES NULL DESCRIBE POINTFUND; Field Type Null Key Default Extra POINTFUND_ID int(11) PRI NULL auto_increment TYPE_ID int(11) 0 begin date -00-00 end date -00-00 description varchar(255) There are values in POINTFUND.TYPE_ID that match TYPES.TYPE_ID. The obvious solution to this is to create a query that joins POINTFUND and TYPES and then use that query in the above query insead of the POINTFUND table (I hope that makes sense). Unfortunatelly there doesn't seem to be a mechanism in MySql to do this. Any other suggestions? -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 09, 2002 5:42 AM To: [EMAIL PROTECTED]; Subject: Re: Table appears in multiple joins returns null column Keith, I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname Obviously the syntax of the SQL command is ok, else the RDBMS would have told you. The answer may lie in the definitions of the races, types, and pointfund tables; or in the data contained in the join-ing fields (neither of which are shown here). Two suggestions: 1 simplify the query to deal with pointfund and one other first, then add another tbl/join, etc, until you have rebuilt the entire query - your formatting is 'clean' and allows the insertion of # comment symbols at the beginning of lines to remove/return then easily (and without loads of typing/retyping) 2 have you tried running EXPLAIN to see what MySQL thinks of it all? Let us know how you get on! =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail
RE: Table appears in multiple joins returns null column
DL, Thank you very much for the response I really appreciate it. I did follow your suggestions and then simplified things a bit more. I posted the next two queries below so you could see where the query goes wrong. I guess I'm not really looking for a 'quick-fix' solution but rather I am missing some high-level understanding of the relational schema and SQL. Perhaps you are telling me what is wrong and I am not seeing it. Sorry if that is the case. I did figure out the multiple join syntax. The query is below. DESCRIBE SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES INNER JOIN POINTFUND ON TYPES.TYPE_ID = POINTFUND.TYPE_ID INNER JOIN PARTY ON PARTY.PARTY_TYPE = TYPES.TYPE_ID INNER JOIN RACES ON TYPES.TYPE_ID = RACES.TYPE_ID INNER JOIN SCHEDULE ON SCHEDULE.SCHEDULE_ID = RACES.SCHEDULE_ID ORDER BY SCHEDULE.date, PARTY.lname table type possible_keys key key_len ref rows Extra POINTFUND ALLTYPE_IDNULLNULL NULL 2 Using temporary; Using filesort TYPES eq_ref PRIMARYPRIMARY 4POINTFUND.TYPE_ID 1 PARTY refPARTY_TYPE PARTY_TYPE 4TYPES.TYPE_ID 3 RACES ALLSCHEDULE_IDNULLNULL NULL 5 where used SCHEDULE eq_ref PRIMARY, SCHEDULE_ID, SCHEDULE_ID_2 PRIMARY 4RACES.SCHEDULE_ID 1 I've tried dozens of combinations of different types of joins and still haven't come to the correct results. After looking at the description of these queries I have come to believe the issue lies not in the query itself but how MySQL is using the index. It seems when the key field in the description is NULL that points to where the RDBMS is having trouble pulling out the correct records. All of the ID fields are int(11) so I don't think there should be a datatype problem. From the example below I am OK until POINTFUND is added to the query and then things go awry where I can't seem to pull the POINTFUND.description field I need in the query. I'll continue to plug away at this and see if I can do something with the index...I have already dropped and rebuilt every index in the DB! Thought someone might have some more info for me. Again, thank you for any time you spent on this I appreciate the value of your time. _ Thanks for the responses. The below query works fine: SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, FROM TYPES, RACES, SCHEDULE, PARTY, WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID AND RACES.TYPE_ID = TYPES.TYPE_ID AND ORDER BY SCHEDULE.date, PARTY.lname The below query returns no records: SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES, RACES, SCHEDULE, PARTY, POINTFUND WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID AND RACES.TYPE_ID = TYPES.TYPE_ID AND POINTFUND.TYPE_ID = TYPES.TYPE_ID ORDER BY SCHEDULE.date, PARTY.lname Here are descriptions of the tables in question. DESCRIBE TYPES Field Type Null Key Default Extra TYPE_ID int(11) PRI 0 typeshort char(3) typelong varchar(45) category tinytext YES NULL DESCRIBE POINTFUND; Field Type Null Key Default Extra POINTFUND_ID int(11) PRI NULL auto_increment TYPE_ID int(11) 0 begin date -00-00 end date -00-00 description varchar(255) There are values in POINTFUND.TYPE_ID that match TYPES.TYPE_ID. The obvious solution to this is to create a query that joins POINTFUND and TYPES and then use that query in the above query insead of the POINTFUND table (I hope that makes sense). Unfortunatelly there doesn't seem to be a mechanism in MySql to do this. Any other suggestions? -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 09, 2002 5:42 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Table appears in multiple joins returns null column Keith, I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname Obviously the syntax of the SQL command is ok, else the RDBMS would have told you. The answer may lie in the definitions of the races, types
Re: Table appears in multiple joins returns null column
Try to delete the index (drop index) of the table! I had the problem and solved this this way. regards, Andreas On Samstag, Februar 9, 2002, at 11:41 Uhr, DL Neil wrote: Keith, I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the=20 table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID =3D TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID =3D TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID =3D SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID =3D SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname Obviously the syntax of the SQL command is ok, else the RDBMS would=20 have told you. The answer may lie in the definitions of the races, types, and pointfund tables; or in the data=20= contained in the join-ing fields (neither of which are shown here). Two suggestions: 1 simplify the query to deal with pointfund and one other first, then=20= add another tbl/join, etc, until you have rebuilt the entire query - your formatting is 'clean' and allows the=20= insertion of # comment symbols at the beginning of lines to remove/return then easily (and without loads of=20= typing/retyping) 2 have you tried running EXPLAIN to see what MySQL thinks of it all? Let us know how you get on! =3Ddn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Andreas Habereder Kirchenstr. 17e 81675 M=FCnchen private: [EMAIL PROTECTED] fax: +49 1212 5 107 37 317 mobile: +49 172 838 7771 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table appears in multiple joins returns null column
Keith, I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname Obviously the syntax of the SQL command is ok, else the RDBMS would have told you. The answer may lie in the definitions of the races, types, and pointfund tables; or in the data contained in the join-ing fields (neither of which are shown here). Two suggestions: 1 simplify the query to deal with pointfund and one other first, then add another tbl/join, etc, until you have rebuilt the entire query - your formatting is 'clean' and allows the insertion of # comment symbols at the beginning of lines to remove/return then easily (and without loads of typing/retyping) 2 have you tried running EXPLAIN to see what MySQL thinks of it all? Let us know how you get on! =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table appears in multiple joins returns null column
Try to delete the index (drop index) of the table! I had the problem and solved this this way. regards, Andreas On Samstag, Februar 9, 2002, at 11:41 Uhr, DL Neil wrote: Keith, I have the below query that returns the correct records but the POINTFUND.description field is all NULL while it has values in the table. Any idea why? Thanks. SELECT RACES.RACE_ID, RACES.sequence, TYPES.typelong, SCHEDULE.event, SCHEDULE.date, PARTY.lname, POINTFUND.description FROM TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, SCHEDULE, PARTY WHERE RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND PARTY.PARTY_ID = SCHEDULE.TRACK_ID ORDER BY SCHEDULE.date, PARTY.lname Obviously the syntax of the SQL command is ok, else the RDBMS would have told you. The answer may lie in the definitions of the races, types, and pointfund tables; or in the data contained in the join-ing fields (neither of which are shown here). Two suggestions: 1 simplify the query to deal with pointfund and one other first, then add another tbl/join, etc, until you have rebuilt the entire query - your formatting is 'clean' and allows the insertion of # comment symbols at the beginning of lines to remove/return then easily (and without loads of typing/retyping) 2 have you tried running EXPLAIN to see what MySQL thinks of it all? Let us know how you get on! =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Andreas Habereder Kirchenstr. 17e 81675 München private: [EMAIL PROTECTED] fax: +49 1212 5 107 37 317 mobile: +49 172 838 7771 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
