Simple Join Issue.

... I hope...

[Tom_Cash]

I've been writing using SQL and PHP for ages now but I've never used joins. To make up for my lack of 'join-confidence' I've just programmed my way around them. However, I want to learn them now. I've been told it's faster, for a start...

So here's my problem!

I joined two tables to begin with and had no issues.

Here's my code:

SELECT manufacturers.ManufacturerName, equipment.ModelNumber, equipment.Description, equipment.CategoryID 
FROM jms.equipment 
JOIN jms.manufacturers 
ON jms.equipment.ManufacturerID = jms.manufacturers.ManufacturerID 
WHERE equipment.EquipmentID = $equipment_id

This works a treat. However, I want to join another table called "categories".

Categories relates to manufacturers via the CategoryID field and should relate.

However, I can't get it to work!

Here's my (broken) code:

SELECT manufacturers.ManufacturerName, equipment.ModelNumber, equipment.Description, categories.CategoryName 
FROM jms.equipment
AND FROM jms.categories
JOIN jms.manufacturers 
ON jms.equipment.ManufacturerID = jms.manufacturers.ManufacturerID 
AND JOIN jms.categories
ON jms.categories.CategoryID = jms.manufacturers.CategoryID
WHERE equipment.EquipmentID = $equipment_id

I know my attempt is horrid but I just can't figure it out...

Any help would be super!

Thanks,
Tom.

[Demaestro]

You are close. In my example I broke apart the query onto seperate lines so you can more easily follow the logic. Ignore the .. (dot dot) I used for spacing, I just used them to make it easier to read.

If you have any questions about why/what is happening below... ask.

SELECT
..manufacturers.ManufacturerName,
..equipment.ModelNumber,
..equipment.Description, categories.CategoryName
FROM
..jms.equipment
LEFT JOIN
..jms.manufacturers ON jms.equipment.ManufacturerID = jms.manufacturers.ManufacturerID
LEFT JOIN
..jms.categories ON jms.categories.CategoryID = jms.manufacturers.CategoryID
WHERE
..equipment.EquipmentID = $equipment_id

[Tom_Cash]

Thanks for the prompt reply!

This has worked fine mate. I really appreciate that, thanks. :D

Also - why use LEFT JOIN over JOIN?

[topr8]

left join, means that all the values in the 'left' table will be returned even if there are no values to return from the other table

the left table is the one before the left join statement.

fyi, there are...

LEFT JOINS, INNER JOINS,OUTER JOINS,RIGHT JOINS and UNIONS (and more)

outer is often just implied.
in your case LEFT OUTER jOIN and LEFT JOIN are synonyms - you could have used either

[Tom_Cash]

Okay, so typically, LEFT JOIN would be the most common I assume?

[Demaestro]

Tom,

Yes typically it is the most common, however there are reasons to use different joins.

There is also a Cartesian join which is very expensive as far as processing goes and it is horribly inefficient. It basically takes each row from table 1 and joins it to each row from table 2.

However I bring it up because once I saw and understood how a Cartesian join worked the other joins made more sense to me. It is worth checking out.

Glad it is all working for you. Joins most of the time are the best way to return related data from more than 1 table.

[Tom_Cash]

Okay, sounds interesting!

Well, I was told they were faster and being the big nerd I am, did some speed checks. My old query was using PHP to make up for joins and was performing a series of loops and sub-loops.

2000 results in PHP was taking 1.814 seconds.

Using JOIN I got the same result set in 0.155 seconds.

Very, very impressed. I should have learnt this much sooner.

Thanks for all your help guys!