Help with MySQL JOIN please.
Moderator: General Moderators
Help with MySQL JOIN please.
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.
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.
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());
i need to join them somehow.
please help
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Help with MySQL JOIN please.
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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Help with MySQL JOIN please.
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'.
Hope this helps
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")
*/
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Help with MySQL JOIN please.
Why not (just an example, may not apply to the OP)?social_experiment wrote:(you can't join one table).
Code: Select all
SELECT c1.categoryID, c1.masterCategoryID, c1.categoryName, c1.categoryLink
FROM categories c1
LEFT JOIN categories c2 ON c1.categoryID = c2.masterCategoryIDmysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Help with MySQL JOIN please.
http://en.wikipedia.org/wiki/Join_(SQL)An SQL JOIN clause combines records from two or more tables in a database
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");
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Help with MySQL JOIN please.
You can take a look at the article that you posted
http://en.wikipedia.org/wiki/Join_(SQL)#Self-join
http://en.wikipedia.org/wiki/Join_(SQL)#Self-join
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Help with MySQL JOIN please.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering