Welcome to WebmasterWorld Guest from 54.146.221.231

Forum Moderators: open

Message Too Old, No Replies

Simple Join Issue.

... I hope...

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

Junior Member

5+ Year Member

joined:June 26, 2008
posts:175
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


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)

Junior Member

5+ Year Member

joined:June 26, 2008
posts:175
votes: 0


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)

Senior Member

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

joined:Apr 19, 2002
posts:3171
votes: 8


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)

Junior Member

5+ Year Member

joined:June 26, 2008
posts:175
votes: 0


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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


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)

Junior Member

5+ Year Member

joined:June 26, 2008
posts:175
votes: 0


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!