Welcome to WebmasterWorld Guest from 54.198.93.179

Forum Moderators: open

Message Too Old, No Replies

Simple Join Issue.

... I hope...

     
2:31 pm on Nov 17, 2010 (gmt 0)

5+ Year Member



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.
3:48 pm on Nov 17, 2010 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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
4:17 pm on Nov 17, 2010 (gmt 0)

5+ Year Member



Thanks for the prompt reply!

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

Also - why use LEFT JOIN over JOIN?
6:23 pm on Nov 17, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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
12:22 pm on Nov 18, 2010 (gmt 0)

5+ Year Member



Okay, so typically, LEFT JOIN would be the most common I assume?
4:24 pm on Nov 18, 2010 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
9:05 am on Nov 19, 2010 (gmt 0)

5+ Year Member



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!
 

Featured Threads

Hot Threads This Week

Hot Threads This Month