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.ENTRY_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
