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 ALL TYPE_ID NULL NULL NULL 2 Using
temporary;
Using filesort
TYPES eq_ref PRIMARY PRIMARY 4 POINTFUND.TYPE_ID 1
PARTY ref PARTY_TYPE PARTY_TYPE 4 TYPES.TYPE_ID 3
RACES ALL SCHEDULE_ID NULL NULL NULL 5 where used
SCHEDULE eq_ref PRIMARY,
SCHEDULE_ID,
SCHEDULE_ID_2 PRIMARY 4 RACES.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 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]; [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
