Page 1 of 1

Count from more than one table

Posted: Thu Jul 29, 2004 10:31 am
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

Posted: Thu Jul 29, 2004 10:45 am
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

Posted: Thu Jul 29, 2004 11:26 am
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 ?

Posted: Thu Jul 29, 2004 11:29 am
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

Posted: Thu Jul 29, 2004 11:33 am
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