Inner Joins

quick check please

[knighty]

OK I may be doing this completley wrong but is 'seems' ok but I only get one row back

the JOBLOGS table just contains userID,Date started, date finished, jobID

the other tables contain more details about the taks or project

SELECT somefileds
FROM joblogs
INNER JOIN ON PROJECTS (joblogs.jobID=projects.jobID)
INNER JOIN ON TASKS (joblogs.jobID=tasks.taskID)
INNER JOIN ON ARCHIVE (joblogs.jobID=archive.archID)
WHERE userID='$userID'

RESULT - Should show list of taks,projects or archived jobs where my userID is present

ie:

1. Updating Site (Task)
2. Buy blank CD's (Task)
3. Redesign (Project)
4. Create Mocks (Project)

etc.

[edited by: knighty at 12:54 pm (utc) on Mar. 8, 2004]

[coopster]

Looks like it may be a syntax issue. Try using

ON

in your join condition:

SELECT somefileds 
FROM joblogs 
INNER JOIN projects ON (joblogs.jobID=projects.jobID) 
INNER JOIN tasks ON (joblogs.jobID=tasks.taskID) 
INNER JOIN archive ON (joblogs.jobID=archive.archID) 
WHERE userID='$userID'

[knighty]

nah, thats just me cut & pasting badly :(

not convinced I should be using INNER JOIN

[Robber]

With the inner join it will only return records that have a corresponding entry in every table used in the joins. ie your user will need to have all of these - project, task, and archive.

If say the user only has a project and a task, but no archive, they wont get returned.

[knighty]

Robber, thats great, now how do I get around that problem?

[coopster]

LEFT JOIN [mysql.com]

If there is no matching record for the right table in the

ON

or

USING

part in a

LEFT JOIN

, a row with all columns set to

NULL

is used for the right table.

[knighty]

coopster, I did try using left joins but it took ages to execute.

I might just do 3 seperate queries instead :(

[Netizen]

Do you have indexes on all the fields used in the ON clauses?

[coopster]

In addition to the advice from Netizen have a look at the benchmarking and optimization tips/tools that the database you are using offers. If you are using MySQL, start here [mysql.com...]

[knighty]

Do you have indexes on all the fields used in the ON clauses?

I do now! Much quicker,Thanks :)

pffft these MySQL n00bs ;)

[Netizen]

No problem :-)