Page 1 of 1
Help with MySQL JOIN please.
Posted: Mon Dec 14, 2009 12:21 pm
by ianhull
Hi all, I am trying tolink sub categories with master categories with just one query.
my table structure is.
categoryID | masterCategoryID | categoryName | categoryLink
I would like to select all categories and sub categories and join them somehow.
I know it can be done, i just don't know how and i can't find any good examples on the net.
thanks in advance.
Re: Help with MySQL JOIN please.
Posted: Mon Dec 14, 2009 2:56 pm
by ianhull
Code: Select all
$q = mysql_query("SELECT
categories.masterCategoryID,
categories.categoryID,
categories.categoryName,
categories.categoryLink,
(SELECT count(*) FROM items
WHERE items.categoryID = categories.categoryID
) AS itemCount
(SELECT
masterCategoryID AS subMasterCategoryID,
categoryID AS subCategoryID,
categoryName AS subCategoryName,
categoryLink AS subCategoryLink FROM categories
WHERE subMasterCategoryID = categories.categoryID
) AS subCategories
FROM categories ORDER BY
categories.categoryName ASC")or die(mysql_error());
is what i have tried but i cant get it to work.
i need to join them somehow.
please help
Re: Help with MySQL JOIN please.
Posted: Mon Dec 14, 2009 3:26 pm
by AbraCadaver
What do you mean by join them somehow? What is the desired result? Your second post shows 2 different tables in your query, not just the 1 categories table.
Re: Help with MySQL JOIN please.
Posted: Mon Dec 14, 2009 3:45 pm
by social_experiment
From the code you posted let's assume you want to use 2 tables (you can't join one table). To inner join them you need a field from each table that will have a similar value . For the example i used 'categoryid'.
Code: Select all
<?php
$ijq = mysql_query("SELECT * FROM item INNER JOIN categories WHERE categories.categoryid = item.categoryid")
/*
$query = mysql_query("SELECT * FROM table1 INNER JOIN table2 WHERE table2.fieldWithValue = table1.fieldWithValue")
*/
?>
Hope this helps
Re: Help with MySQL JOIN please.
Posted: Mon Dec 14, 2009 4:02 pm
by AbraCadaver
social_experiment wrote:(you can't join one table).
Why not (just an example, may not apply to the OP)?
Code: Select all
SELECT c1.categoryID, c1.masterCategoryID, c1.categoryName, c1.categoryLink
FROM categories c1
LEFT JOIN categories c2 ON c1.categoryID = c2.masterCategoryID
Re: Help with MySQL JOIN please.
Posted: Tue Dec 15, 2009 1:21 am
by social_experiment
An SQL JOIN clause combines records from two or more tables in a database
http://en.wikipedia.org/wiki/Join_(SQL)
I think if you could join one table onto itself it would be a bit counter-productive.
Code: Select all
<?php
$query = mysql_query("SELECT * FROM table1");
?>
The above code lets you access all the information in said table.
Re: Help with MySQL JOIN please.
Posted: Tue Dec 15, 2009 8:40 am
by AbraCadaver
You can take a look at the article that you posted
http://en.wikipedia.org/wiki/Join_(SQL)#Self-join
Re: Help with MySQL JOIN please.
Posted: Tue Dec 15, 2009 10:23 am
by social_experiment

Valid point. I should have read further.
