please CC me as I am on digest
-------------------------------
I have three tables, simplified for, well, simplicity :-)
CREATE TABLE Usrs (
usr_id serial primary NOT NULL,
name text NOT NULL,
login text NOT NULL,
CONSTRAINT PK_Usrs PRIMARY KEY (usr_id)
);
CREATE TABLE EmailAddrs (
email_addr_id SERIAL NOT NULL,
email_addr VARCHAR(255) NOT NULL UNIQUE,
CONSTRAINT PK_EmailAddrs PRIMARY KEY (email_addr_id)
);
CREATE TABLE UsrEmails (
usr_id INT4 NOT NULL,
email_addr_id INT4 NOT NULL,
CONSTRAINT PK_UsrEmails PRIMARY KEY (usr_id, email_addr_id)
);
ALTER TABLE UsrEmails
ADD CONSTRAINT EmailAddrs11_0MUsrEmail
FOREIGN KEY (email_addr_id)
REFERENCES EmailAddrs (email_addr_id);
ALTER TABLE UsrEmails
ADD CONSTRAINT UsrEmailTypes11_0MUsrEmails
FOREIGN KEY (usr_email_type_id)
REFERENCES UsrEmailTypes (usr_email_type_id);
multiple 'Usrs' can have the same name, but different logins.
I want to find the count of usrs that:
have the name 'some_name'
and
have the email 'some_email'
-----------------------------
Should I use a JOIN or an IN?
If the 'IN' example below is right, and there is either:
NO Usr with name='some_name'
OR
NO email with email='some_email'
will it return a NULL, or a '0' count?
my thought for an IN:
----------------------
SELECT COUNT(*)
FROM UsrEmails
WHERE
usr_id IN
(SELECT usr_id
FROM Usrs
WHERE name='some_name'::text)
AND
email_addr_id=(SELECT email_addr_id
FROM Emails
WHERE email='some_email'::text);
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
