I have a database with two tables....one called categories and one called users. The categories table has a full list of business categories....the 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.
5 12424 Atli 5,058
Expert 4TB
You could do that in (at least) two ways.
First, and the one I would recommend, would be to join the tables.
For example: -
SELECT
-
cat.CatName,
-
COUNT(user.UserID) AS 'Count'
-
FROM Category AS cat
-
INNER JOIN user
-
ON user.CatID_FK = cat.CatID
-
GROUP BY cat.CatName
-
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: -
SELECT
-
cat.CatName,
-
( SELECT COUNT(*) FROM user
-
WHERE CatID_FK = cat.CatID
-
) AS 'Count'
-
FROM Category AS cat
-
Here I use the table in the FROM clause, named 'cat', in the subquery to filter the results.
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"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center">Electricians</td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center">Photographers</td>
<td align="center"> </td>
<td align="center"> </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 5,058
Expert 4TB
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: -
Category
-
----------
-
CatID Integer Primary Key
-
CatName Text Not Null
-
----------
-
-
User
-
----------
-
UserID Integer Primary Key
-
UserName Text Not Null
-
CatID_FK Integer References Category(CatID)
-
----------
-
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)
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 5,058
Expert 4TB
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: -
CREATE TABLE tblName (
-
RowNumber Int Primary Key Auto_Increment,
-
/* other rows */
-
)
-
You can add this to an existing table by using the ALTER TABLE syntax, like so: -
ALTER TABLE myTable
-
ADD COLUMN RowNumber Int Primary Key Auto_Increment
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: hokieghal99 |
last post by:
Hi,
I'd like to get user input from an html form into a mysql select
statement. Here's where I'm stumped:
$result = mysql_query("SELECT * FROM dept WHERE notes LIKE
'%search-string%'",$db);
...
|
by: Hayden Kirk |
last post by:
Hi
Currently im using this
$query = "select * from news, users where news.User_ID = users.User_ID order
by news.News_ID DESC"
only problem is both tables have User_ID, how can I make...
|
by: phpfrizzle |
last post by:
Hi there,
I have a site with products on it.
The site has a mysql backend.
All products belong to certain series (table series).
There can be up to 4 different products (table products)...
|
by: mike-nospam |
last post by:
Trying to write a php script where the user will enter the row number
(index) of a record. Say, for example, user wants record 55:
: select * from MyTable where Index = "55"
where Index is an...
|
by: worldcyclist |
last post by:
Hey folks!
I tried hunting for a MySQL specific NG and didn't find one but I did
see that MySQL questions get asked here. If this isn't appropriate and
you know of an appropriate NG then that...
|
by: Eric B. |
last post by:
Hi,
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+...
|
by: jossinet |
last post by:
Bonjour,
j'ai une table : matable
dans laquelle j'ai un champs : codepostal
Ce champs code postal est rempli d'enregistrements qui ont des valeurs
de 5 chiffres.
Je souhaite créer un bouton...
|
by: steve |
last post by:
I like to create some temprary tables (in-memory tables) when mysql
starts up. How do I do that.
Thanks.
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles...
|
by: starman |
last post by:
Hi,
This should be straightforward, but I can't seem to figure it out! I have two tables called members and clubs, and a session variable called thename which is the user's user name from the login...
|
by: abhishek1234321 |
last post by:
I there any way to select tables using wildcards?
like for example i have 100 tables in my database with names "1rn08cs001" "1rn08cs002"... etc
now i want a wild card method to select oly the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |