>Background info: One main table with basic entry info joined to
>other tables with additional info about the entry. The additional
>info about the entry is based on the entry type and is located in
>single other table (which other table depends on the entry type).
>
> - table LOG_LOG (this is the main table that is joined to other tables.)
>Fields Type Null Key
>LOG_ID bigint(20) PRI auto_increment
>LOGGED_DATE datetime yes
>ENTRY_DATE datetime yes
>CREW_POSITION varchar(10) yes
>LOGGER varchar(5) yes
>TBL_NAME varchar(255)
>REMARKS text yes
>
>Keys
>PRIMARY Unique Field = LOG_ID
>LOG_LOG Field = LOG_ID
>
> - table SAT_GEN (this is one of the tables that has more
>information about that type of entry)
>Fields Type Null Key
>SAT_GEN_ID int(11) PRI auto_increment
>LOGID bigint(20) Unique
>SAT_GEN_FLT varchar(10) yes
>
>Keys
>PRIMARY Unique Field = SAT_GEN_ID
>SAT_GEN Field = SAT_ID
>LOGID Unique Field = LOGID
>
> - table SAT_CMD (this is another table that has more information
>about this type of entry)
>Fields Type Null Key
>SAT_CMD_ID int(11) PRI auto_increment
>LOGID bigint(20) Unique
>SAT_CMD_FLT varchar(10) yes
>SAT_CMD_CMD varchar(255) yes
>SAT_CMD_T varchar(8) yes
>SAT_CMD_V varchar(8) yes
>SAT_CMD_GO char(1) yes
>
>Keys
>PRIMARY Unique Field = SAT_CMD_ID
>SAT_CMD_ID Unique Field = SAT_CMD_ID
>LOGID Unique Field = LOGID
>
>Now, to get the data and display it correctly I use a select to get
>all the data (it will be limited by date range) and display it
>correctly. Since each entry can be of a different type I first
>check the value of the TBL_NAME field and then know what type of
>entry it is so I can look at the correct field names and display it
>correctly.
>
>Finaly the problem. The SQL select below puts the joins in the wrong order.
>
>SELECT LOG_LOG.LOG_ID, LOG_LOG.ENTRY_DATE,
>CONCAT(DATE_FORMAT(LOG_LOG.ENTRY_DATE,'%Y'),':',DATE_FORMAT(LOG_LOG.EN
>TRY_DATE,'%j')) AS ENTRY_JULIAN,
>DATE_FORMAT(LOG_LOG.ENTRY_DATE,'%T') AS ENTRY_TIME,
>LOG_LOG.CREW_POSITION, LOG_LOG.LOGGER, LOG_LOG.TBL_NAME,
>LOG_LOG.REMARKS, SAT_GEN.LOGID, SAT_GEN.SAT_GEN_FLT, SAT_CMD.LOGID,
>SAT_CMD.SAT_CMD_FLT, SAT_CMD.SAT_CMD_CMD, SAT_CMD.SAT_CMD_T,
>SAT_CMD.SAT_CMD_V, SAT_CMD.SAT_CMD_GO
>FROM LOG_LOG, SAT_GEN, SAT_CMD
>WHERE LOG_LOG.LOG_ID=SAT_GEN.LOGID AND LOG_LOG.LOG_ID=SAT_CMD.LOGID;
>
>This is the EXPLAIN output for the select statement. LOG_LOG should
>be the first table as it is the main table that all others are
>joined to.
>
> table type possible_keys key key_len ref rows Extra
> SAT_CMD ALL LOGID NULL NULL NULL 8
> LOG_LOG eq_ref PRIMARY,LOG_LOG PRIMARY 8 SAT_CMD.LOGID 1
> SAT_GEN eq_ref LOGID LOGID 8 LOG_LOG.LOG_ID 1
>
>
>I can't figure out how to force the correct join order to get all
>entries in LOG_LOG and matching entries in the other two tables
>(there will be many more tables later). I tried various STRAIT_JOIN
>syntaxes but everything I tried gives me an error.
>
>Question: How do I use STRAIT_JOIN to force the correct order?
>
>Bigger Question: Am I using a reasonable method of accomplishing
>this task in the first place i.e. is there a better schema? (this
>will be a web-based intranet application.
>
>Thanks and sorry about the lenght of the question.
>
>Chuck Murison
Sir, you're asking about two different, unrelated things. The first
is the order of joins. Since you're using an inner join, the order of
joins has no effect on the output. It's purely an optimization issue.
The other thing you're asking about is getting all rows from one
table and matching rows from other tables. The answer is to use LEFT
JOIN, with the main table as the left table.
(FROM log_log LEFT JOIN another_table ON ...) LEFT JOIN
yet_another_table ...
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
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
