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 0000-00-00 > end date 0000-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 <[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 > > --------------------------------------------------------------------- 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
