homepage Welcome to WebmasterWorld Guest from 54.226.10.234
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Simple Join Issue.
... I hope...
Tom_Cash

5+ Year Member



 
Msg#: 4231428 posted 2:31 pm on Nov 17, 2010 (gmt 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.

 

Demaestro

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



 
Msg#: 4231428 posted 3:48 pm on Nov 17, 2010 (gmt 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

Tom_Cash

5+ Year Member



 
Msg#: 4231428 posted 4:17 pm on Nov 17, 2010 (gmt 0)

Thanks for the prompt reply!

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

Also - why use LEFT JOIN over JOIN?

topr8

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



 
Msg#: 4231428 posted 6:23 pm on Nov 17, 2010 (gmt 0)

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

5+ Year Member



 
Msg#: 4231428 posted 12:22 pm on Nov 18, 2010 (gmt 0)

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

Demaestro

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



 
Msg#: 4231428 posted 4:24 pm on Nov 18, 2010 (gmt 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.

Tom_Cash

5+ Year Member



 
Msg#: 4231428 posted 9:05 am on Nov 19, 2010 (gmt 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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved