homepage Welcome to WebmasterWorld Guest from 54.197.110.151
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL Table JOIN Problem
Trying to write query but what sort of JOIN do I use?
snowweb

5+ Year Member



 
Msg#: 3483897 posted 1:00 pm on Oct 22, 2007 (gmt 0)

Hi, I'm trying to do this...


$query = "SELECT HIGH_PRIORITY products.prod_name, products.prod_vend1_id, products.prod_vend2_id, products.prod_category_id, categories.prod_cat_name, categories.prod_cat_desc, vendors.company_name, FROM products, categories, vendors
WHERE
products.id = '$id'
AND
products.prod_category_id = categories.id
AND
products.prod_vend1_id = vendors.id
AND
products.prod_vend2_id = vendors.id
";

The product table contains the stock items and the categories table contains the category of each item and the vendors table... you guessed it!

I'm trying to display product details for a specific product. Each product can have up to 2 suppliers, hence products.prod_vend1_id & products.prod_vend2_id.

I'm trying to look up the vendor's name in the vendor table from the vendor's ID stored in the product table record.

The above query seems to work ok for vendor1, until I add the last bit...


AND
products.prod_vend2_id = vendors.id

I can see what the problem is (actually I would have been surprised if the above method had worked), but can't figure out how to solve it with a table JOIN.

My first request is, can someone help me out of trouble on this please?

My second request is, can anyone recommend a tutorial or resource to help me learn and understand about JOINs? I've read several books on MySQL and tried to understand the help file but can't get my head around it without a more detailed explanation.

Thanks in advance.

peter

 

Demaestro

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



 
Msg#: 3483897 posted 8:47 pm on Oct 22, 2007 (gmt 0)

I am going to assume you are getting some null values back and that is why you are missing data... if that is the case then a left join should be what you need....

**syntax should be right, but check if it doesn't work**

SELECT HIGH_PRIORITY
products.prod_name,
products.prod_vend1_id,
products.prod_vend2_id,
products.prod_category_id,
categories.prod_cat_name,
categories.prod_cat_desc,
vendors.company_name,

FROM

products left join categories on products.prod_category_id = categories.id left join vendors on products.prod_vend1_id = vendors.id
AND
products.prod_vend2_id = vendors.id

WHERE
products.id = '$id'

snowweb

5+ Year Member



 
Msg#: 3483897 posted 2:07 am on Oct 23, 2007 (gmt 0)

OK, thanks Demaestro, I've updated my code as you suggest. I'm now getting results returned for all except the vendor company name ( 'vendors.company_name' ) field.

I think the problem is to do with the fact that I only have one vendors.company_name field listed in my query, but I'm expecting it to hold the results for both vendor1 and vendor2.

How do I do this please?

Demaestro

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



 
Msg#: 3483897 posted 5:32 pm on Oct 23, 2007 (gmt 0)

I need to see your table structures with about 5 rows of data for each and I will be able to help you

snowweb

5+ Year Member



 
Msg#: 3483897 posted 1:28 am on Oct 24, 2007 (gmt 0)

Hi Demaestro,


I need to see your table structures with about 5 rows of data for each and I will be able to help you

Products - table 

id prod_name prod_vend1_id prod_vend2_id prod_category_id
1 Green Widget 2 null 2
2 Red Rag 4 1 1
3 Broom 4 null 1
4 Red Widget 2 3 2
5 Paint Brush 5 null 3

Vendors - table

id company_name
1 Cleaner Supplies
2 Widgets r Us
3 Widgets n Wotsits
4 Cleaneze Inc.
5 DIY Supplies

Categories - table

id prod_cat_name prod_cat_desc
1 Cleaning Cleaning products
2 Widgets All kinds of widgets
3 Decorating Decorating tools and materials
4 Building Builders Supplies
5 Electrical Electrical Supplies

Sorry about the formating (the primitive tools supplied for formating don't seem to work properly - 'PRE' and 'FIXED')

Really appreciate your help. (This is first PHP project).

Regards

Peter

Demaestro

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



 
Msg#: 3483897 posted 2:26 pm on Oct 24, 2007 (gmt 0)

The formating was fine thanks....

The problem is that you want to get results from the vendor table twice. That is ok. But you have to reference and alias the table twice

So............

SELECT HIGH_PRIORITY
products.prod_name,
products.prod_vend1_id,
products.prod_vend2_id,
products.prod_category_id,
categories.prod_cat_name,
categories.prod_cat_desc,
vendors1.company_name,
vendors2.company_name,

FROM

products left join categories on products.prod_category_id = categories.id

left join vendors as venders1 on products.prod_vend1_id = vendors1.id

left join vendors as venders2 on products.prod_vend2_id = vendors2.id

WHERE
products.id = '$id'

....................

Check your results closely though and make sure everything you want is there and nothing you don't want.

Not sure if you are able to but a schema change might make future queries easier.

Something like..........

Products - table

id prod_name
1 Green Widget
2 Red Rag
3 Broom
4 Red Widget
5 Paint Brush

Vendors - table

id company_name
1 Cleaner Supplies
2 Widgets r Us
3 Widgets n Wotsits
4 Cleaneze Inc.
5 DIY Supplies

Categories - table

id prod_cat_name prod_cat_desc
1 Cleaning Cleaning products
2 Widgets All kinds of widgets
3 Decorating Decorating tools and materials
4 Building Builders Supplies
5 Electrical Electrical Supplies

TABLE product_has_vendor

product_id vendor_id
1 2
1 3
2 2
2 6
8 3
8 6
8 9
8 12
8 15
8 98

TABLE product_has_category

product_id category_id
1 3
1 7
2 8
2 2
8 3
8 98

This way you can have a true "one to many" relationship and you don't have to tack on a new field every time you want to add a vendor which will result in tables looking like vendor_id_1 vendor_id_2 vendor_id_3 vendor_id_4 vendor_id_5 vendor_id_6

That way your SQL would look like

SELECT
*
from
product_has_vendor, vendor, product
where
product_has_vendor.product_id = product.product_id
and product_has_vendor.vendor_id = vendor.vendor_id
and product.product_id = '$product_id'

There could be a million vendors for that product and they would all be returned here... the way you have it now it would be 1 'AND' statement per vendor_id_X in the product table.

Hope this helps... post back if not.

[edited by: Demaestro at 2:27 pm (utc) on Oct. 24, 2007]

snowweb

5+ Year Member



 
Msg#: 3483897 posted 4:48 pm on Oct 24, 2007 (gmt 0)

Thanks for all the info. Looks good to me. Will give it all a go tomorrow (nearly 1am here now).

Will let you know how I got on tomorrow or next day. Thanks again.

peter

Demaestro

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



 
Msg#: 3483897 posted 9:11 pm on Oct 24, 2007 (gmt 0)

Ya dude get a good sleep and let me know how it goes.

snowweb

5+ Year Member



 
Msg#: 3483897 posted 10:37 am on Oct 26, 2007 (gmt 0)

It's all working now. Thanks for your help.

I understand your idea about the product_has_vendor table and I did initially implement that and was very pleased with myself, until I realised that my 'Add a Product' page would also need to be ammended so that it will write into both tables when a new product is added.

Unfortunately, that page, like most of them, is using AJAX and a generic Javascript to send the new record to the database and I never thought I would need to write to two databases in order to store one new record, when I originally wrote it. (You can see that I'm still green around the edges!)

I contemplated ammending the script, but it is used by many other parts of the website, and really don't want to start rebuilding them too!

In the end I decided just to go back to my original idea using the SQL code you provided.

I've added the rewrite of the offending script and many to many relationship method that you suggested, to my task list for the future rebuild, since I'm on a tight schedule with this (must go live by Christmas).

Thanks again for all your effort. I learnt two or three important things there :)

Regards

Peter

Demaestro

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



 
Msg#: 3483897 posted 4:58 pm on Oct 26, 2007 (gmt 0)

Sounds like a very sound plan of action.... I know once you have something in production and working it is hard to put a bunch of effort making it efficient.

My mantra has always been

Step One: Make it work

Step Two: Make it work well

Step Three: Make it work fast

And if you only ever get past step one at least you have something that works.

Take care and let me know if you need any help.

snowweb

5+ Year Member



 
Msg#: 3483897 posted 2:03 am on Oct 27, 2007 (gmt 0)

Thanks for that Demaestro.

I like your plan. That's pretty much my intention with this. Steps 2+ will be easier to achieve once there is some money coming in (as I can hire someone then who knows more than I do!). I have a bunch of people already pre-registered and waiting to use and pay for my application, so can't hold things up for too long trying to make it perfect!

Must go. Take care buddy!

pete

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