mysql select from two tables

[ukfusion]

I have a database with two tables....one called categories and one called users. The categories table has a full list of business categories....t he users table has all the usual fields plus a category field which relates to the business category, i need to output the full list of category names from the categories table but also have how many rows there are in the users table for each category so...

builders (3)
web designers (4)

etc....

I can get them both to display seperately but i need them in 1 select statement if possible, or if there is another way that would be great.

Thanks for any help in advance.

[Atli]

You could do that in (at least) two ways.

First, and the one I would recommend, would be to join the tables.
For example:
[code=mysql]
SELECT
cat.CatName,
COUNT(user.User ID) AS 'Count'
FROM Category AS cat
INNER JOIN user
ON user.CatID_FK = cat.CatID
GROUP BY cat.CatName
[/code]
That would essentially join the two tables together using the ON clause of the JOIN to filter out those rows we don't need, which you could then show in whichever way you need.

Second, and usually the slower method, would be to use a Correlated Subquery. That, in essence, is simply a subquery that references a table from the outer query.
Like:
[code=mysql]
SELECT
cat.CatName,
( SELECT COUNT(*) FROM user
WHERE CatID_FK = cat.CatID
) AS 'Count'
FROM Category AS cat
[/code]
Here I use the table in the FROM clause, named 'cat', in the subquery to filter the results.

[ukfusion]

Hi, sorry if im a numpty, im not really an expert at sql statements so most of that goes past me .i.e the _fk part.

here is a table to illustrate the structure i have.... all letter casing is correct.....i understand the field/table naming is probably crap but i can alter that after.

[HTML]<table width="100%" border="1" cellspacing="5" cellpadding="10 ">
<tr>
<th align="center"> Table = category</th>
<th colspan="2" align="center"> Tabel = users</th>
</tr>
<tr>
<td align="center"> <strong>Field = Category_Name</strong></td>
<td align="center"> <strong>Field = category</strong></td>
<td align="center"> <strong>Field = contactname</strong></td>
</tr>
<tr>
<td align="center"> Builders</td>
<td align="center"> Website Designers</td>
<td align="center"> John</td>
</tr>
<tr>
<td align="center"> Website Designers</td>
<td align="center"> Builders</td>
<td align="center"> Jack</td>
</tr>
<tr>
<td align="center"> Plumbers</td>
<td align="center"> &nbsp;</td>
<td align="center"> &nbsp;</td>
</tr>
<tr>
<td align="center"> Electricians</td>
<td align="center"> &nbsp;</td>
<td align="center"> &nbsp;</td>
</tr>
<tr>
<td align="center"> Photographers</td>
<td align="center"> &nbsp;</td>
<td align="center"> &nbsp;</td>
</tr>
</table>[/HTML]

Using this info i need to present it like this

Website Designers (1)
Builders (1)
Plumbers (0)

i tried applying your method, it didnt give me any errors but it just didnt display anything either so i dont know if i just hadnt named the fileds correctly.

Hope the table makes it clearer.

Thanks again

[Atli]

Did you use a CREATE statement to create the tables?
If so, can we see them?

Are these the only fields in the tables?
No ID fields like a unique integer column?

The standard method to create a database for stuff like this would be to create two tables, like you did, but put a unique column in both (a primary key). Then you could reference the primary key of the category table in your user table rather then writing the name of the category again.

Consider these tables:
[code=mysql]
Category
----------
CatID Integer Primary Key
CatName Text Not Null
----------

User
----------
UserID Integer Primary Key
UserName Text Not Null
CatID_FK Integer References Category(CatID)
----------
[/code]
Each table has a Primary Key column which will always have a unique value for each row. In the user table, rather then write the name of the category the user belongs to, I simply store the ID of the category as listed in the category table. (This is referred to as a Foreign Key... The FK in the field name is meant to underline that)

[ukfusion]

Hi, i used the create statement to create the basic fields, but since then i've added a few manually, theres a user id field in my users table but its a random 32 var character, my category table doesnt yet have 1 and was created manually.

is it too late to add a primary key field in now, does it need to be done from the start.

I'm new to mysql etc so still learning a lot.

Using this method i managed to get them to display sepperately but it was getting them both in the same statement that was the trouble.

http://www.tizag.com/mysqlTutorial/mysqlcount.php

Thx

[Atli]

It's always, with very few exceptions, a very good idea to have a integer column in each table acting as a primary key. If you create it with the AUTO_INCREMENT clause, it will automatically insert the next available number, so it will basically act as a row number column.

To do that when creating a table, you can do this:
[code=mysql]
CREATE TABLE tblName (
RowNumber Int Primary Key Auto_Increment,
/* other rows */
)
[/code]
You can add this to an existing table by using the ALTER TABLE syntax, like so:
[code=mysql]
ALTER TABLE myTable
ADD COLUMN RowNumber Int Primary Key Auto_Increment
[/code]