SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query

Author

Topic

bemoCannon
Starting Member

3 Posts

Posted - 2007-02-03 : 08:30:34

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

(curdate() <= enddate AND closed = 0) //Current accounts
(curdate() > enddate AND closed <> 0) //Expired accounts

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-02-03 : 09:06:29

"I want to query all the members who have no active accounts"

I understand that to mean:

Member has NO accounts
OR Member has NO accounts which are Active.
Active means that the Start and End Date are "current", AND the account has not been marked as closed.

However, your definition seems to be ambiguous: "This could me they have one active account, or no accounts at all"

Based on my interpretation:


SELECT ...
FROM member 
    LEFT JOIN address 
        ON address.MemberID = account.Memberid
WHERE NOT EXISTS
(
    SELECT *
    FROM account
    WHERE     account.Memberid = member.MemberID
          AND MyStartDate <= GetDate()
          AND enddate  >= GetDate()
          AND closed = 0
)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources