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