Count from more than one table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Count from more than one table

Post by anjanesh »

Is there any way to count like this :

Code: Select all

SELECT
count(a.UserID)
FROM Users a,DisabledEntries b
WHERE a.UserID<>b.ID
This always produces 0.
By the way, field ID in table DiabledEntries HAS to named ID and not UserID since the id in the DisabledEntries can be a userid or picid or some other id.

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

this may work better

Code: Select all

SELECT COUNT(a.UserID) `num`
FROM Users a
LEFT JOIN DisabledEntries b
ON a.UserID = b.ID
WHERE b.ID = NULL
untested
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

num still returned 0

Im trying to return all rows except those that are disabled by the admin in the DisabledEntries table. I didn't want to enter a separate field in User table called Disabled because in most cases it'll be no. Only rarely will a user will disabled so I thought of having disabled users in a separate table. Same applies for info like text content, pic etc etc.
Guess I'll have to do it from PHP side ?
Last edited by anjanesh on Thu Jul 29, 2004 11:30 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

hmm maybe

Code: Select all

SELECT COUNT(a.UserID) `num`
FROM Users a
LEFT JOIN DisabledEntries b
ON a.UserID != b.ID
WHERE b.ID = NULL
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

num still 0 feyd.

This is giving some result - not the right one though

Code: Select all

SELECT COUNT(a.UserID) `num`
FROM Users a
LEFT JOIN DisabledEntries b
ON a.UserID != b.ID
Just to give an example :

Users has 13 rows (numbered 1 to 13)
DisabledEntries has 0 rows
num gives 13

Users has 13 rows (numbered 1 to 13)
DisabledEntries has 2 rows (6,10)
num gives 24
Post Reply