Discuss SQL query in the General SQL Development forum on Dev Articles. SQL query General SQL Development forum to discuss Oracle, PostgreSQL, and platform independent SQL related questions. Learn to utilize the power of SQL to manipulate relational databases.
Posts: 1
Time spent in forums: 1 m 42 sec
Reputation Power: 0
SQL query
Hi everyone and thanks in advance.
I have two tables, one for members and one for accounts. Every member can have multiple accounts. Accounts have a lifespan, which is specified by start and end dates, and can be terminated early with a closed tinyint variable.
I want to query all the members who have no active accounts. This could me they have one active account, or no accounts at all.
I join the two tables using this:
FROM (account LEFT JOIN member ON account.Memberid = member.MemberID)
LEFT JOIN address ON account.Memberid = address.MemberID
Posts: 16
Time spent in forums: 3 h 19 m 57 sec
Reputation Power: 0
Hi,
I hope one of the following queries may help you
Code:
-- Members With No Accounts
SELECT
Members.MemberId
FROM Members
LEFT JOIN Accounts ON Accounts.MemberId = Members.MemberId
WHERE
Accounts.MemberId IS NULL
-- Members With Only Passive Accounts
SELECT
Members.MemberId, Accounts.Active
FROM Members
INNER JOIN Accounts ON Accounts.MemberId = Members.MemberId
WHERE
Accounts.Active = 0
AND Members.MemberId NOT IN (
SELECT
Members.MemberId, Accounts.Active
FROM Members
INNER JOIN Accounts ON Accounts.MemberId = Members.MemberId
WHERE
Accounts.Active = 1
)
-- Members With Active & Passive Accounts
SELECT
Members.MemberId, Accounts.Active
FROM Members
INNER JOIN Accounts ON Accounts.MemberId = Members.MemberId
WHERE
Accounts.Active = 0
AND Members.MemberId IN (
SELECT
Members.MemberId, Accounts.Active
FROM Members
INNER JOIN Accounts ON Accounts.MemberId = Members.MemberId
WHERE
Accounts.Active = 1
)
-- Members With Only Active Accounts
SELECT
Members.MemberId, Accounts.Active
FROM Members
INNER JOIN Accounts ON Accounts.MemberId = Members.MemberId
WHERE
Accounts.Active = 1
AND Members.MemberId NOT IN (
SELECT
Members.MemberId, Accounts.Active
FROM Members
INNER JOIN Accounts ON Accounts.MemberId = Members.MemberId
WHERE
Accounts.Active = 0
)
Last edited by MadCowDzz : February 6th, 2007 at 08:07 AM.
Reason: added [code] tags to preserve formatting
Dev Articles Community Forums Sponsor:
February 3rd, 2007, 07:13 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
