November 12, 2007 - 4:33pm
sKunKbad @ Nov 12 2007,
I've got a SELECT statement that works fine in my new WAMP5 MySQL, but when uploaded to yahoo's server, which has MySQL version 4.1.12 it doesn't work. I've gone around in circles trying to make this work, and hoping some MySQL guru can help. Here it is:
SELECT name, category, scent, size
FROM general_widgets
LEFT JOIN specific_widgets USING (gw_id)
LEFT JOIN scents USING (scent_id)
LEFT JOIN sizes USING (size_id)
LEFT JOIN categories USING (category_id)
WHERE sw_id=$sw_id
The result should only be one row (one product), but everything I have tried is returning many rows. To me this is a severely complex SELECT statement. All of the books I have read, and all of the online tutorials I have seen only show LEFT JOINS with one other table, so I really need some help! I got this SELECT statement from a book I am reading about advanced PHP, and like I said, it definately works on my newer MySQL installation, but not on version 4.1.12.
Thanks,
Brian
Look here
A left join is generally in the format of
Left Join table1name on (table2name.id = table1name.id)
November 12, 2007 - 6:46pm
Mairving, I did end up fixing
Mairving, I did end up fixing it by doing the following:
SELECT name, category, scent, size
FROM general_widgets
LEFT JOIN specific_widgets ON ( general_widgets.gw_id = specific_widgets.gw_id )
LEFT JOIN scents ON ( specific_widgets.scent_id = scents.scent_id )
LEFT JOIN sizes ON ( specific_widgets.size_id = sizes.size_id )
LEFT JOIN categories ON ( general_widgets.category_id = categories.category_id )
WHERE specific_widgets.sw_id = 1
So you were right. Apparently newer versions of MySQL allow for the other type of LEFT JOIN structure. Thanks for your advice and the link!
God bless,
Brian
November 13, 2007 - 10:26am
USING has been valid in older
USING has been valid in older versions of MySQL. But older versions of MySQL are not so good at working out where the column should come from when you have more than 2 tables, and if those columns were present in more than 2 of the tables, it caused errors. I am interested in what error message you received.
November 13, 2007 - 2:00pm
You might also look at your
You might also look at your joins. A good portion of the time a simple (INNER or EQUAL) JOIN will work.
Basically a simple INNER JOIN includes records from both tables only when the joining fields are equal.
A LEFT (OUTER) JOIN includes all records from the left table and records from the right table only the the joining fields are equal.
There is also a RIGHT (OUTER) JOIN that isn't used much and a FULL (OUTER) JOIN that is used occasionally.
November 13, 2007 - 2:21pm
November 14, 2007 - 1:02pm
November 14, 2007 - 11:11pm
November 15, 2007 - 3:08am
November 15, 2007 - 3:40am
bobbymac @ Nov 14 2007,
Can you post your table structures?
categories table ->
category_id
category
description
general_widgets table ->
gw_id
category_id
name
default_price
description
scents table ->
scent_id
scent
sizes_table ->
size_id
size
specific_widgets table ->
sw_id
gw_id
scent_id
size_id
price
in_stock
November 15, 2007 - 11:44am
Ok, in this case since you
Ok, in this case since you are joining 2 tables to the same table (on the same side), you need to specify the second of those joins as an ON in the older MySQL. And one RIGHT JOIN for the category.
So this should work:
CODE
SELECT name, category, scent, size
FROM cwm_categories c
RIGHT JOIN cwm_general_widgets g
USING ( category_id )
LEFT JOIN cwm_specific_widgets s
USING ( gw_id )
LEFT JOIN cwm_scents sc
USING ( scent_id )
LEFT JOIN cwm_sizes sz ON ( sz.size_id = s.size_id )
FROM cwm_categories c
RIGHT JOIN cwm_general_widgets g
USING ( category_id )
LEFT JOIN cwm_specific_widgets s
USING ( gw_id )
LEFT JOIN cwm_scents sc
USING ( scent_id )
LEFT JOIN cwm_sizes sz ON ( sz.size_id = s.size_id )
I've got a SELECT statement that works fine in my new WAMP5 MySQL, but when uploaded to yahoo's server, which has MySQL version 4.1.12 it doesn't work. I've gone around in circles trying to make this work, and hoping some MySQL guru can help. Here it is:
SELECT name, category, scent, size
FROM general_widgets
LEFT JOIN specific_widgets USING (gw_id)
LEFT JOIN scents USING (scent_id)
LEFT JOIN sizes USING (size_id)
LEFT JOIN categories USING (category_id)
WHERE sw_id=$sw_id
The result should only be one row (one product), but everything I have tried is returning many rows. To me this is a severely complex SELECT statement. All of the books I have read, and all of the online tutorials I have seen only show LEFT JOINS with one other table, so I really need some help! I got this SELECT statement from a book I am reading about advanced PHP, and like I said, it definately works on my newer MySQL installation, but not on version 4.1.12.
Thanks,
Brian
Brian's Web Design - Temecula, CA