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 :wink:

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
:oops: Valid point. I should have read further. :)