SQL returning null - 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 returning null

Author

Topic

bemoCannon
Starting Member

3 Posts

Posted - 2007-02-13 : 23:50:51

Thanks to everyone in advance

The code below returns the members who have accounts that are inactive, and works correctly.
It returns the memberid, but returns null values for the barcode values.
I am explicitly looking for unmatched rows, i.e. the LEFT OUTER JOIN will return joined member-address
rows which do not have a matching account, according to the ON conditions, and these are the ones I want
because of that WHERE condition.
Therefore account.barcode will always be NULL too.

Is there to perform the same query and return account details?
I have tried running a query to return just the memberid values and then perform a second query to match the account
details, but this is very ineffecient and slow.

select member.memberid, account.barcode
FROM member
INNER
JOIN address
ON address.MemberID = member.Memberid
LEFT OUTER
JOIN account
ON account.Memberid = member.MemberID
AND account.enddate >= current_date
AND account.closed = 0
WHERE account.Memberid is null AND member.isDeleted = 0

Special thanks to r937 for help.
James

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-02-14 : 02:19:33

You want to return account details of those members who are not deleted, whos account has expired or closed?
I'd write a normal query approaching it as above (assuming that's correct). Outer joins need to drive a query down a particular path so you get more flexibility if you can avoid them.
If what I understand is correct then join account to members and add a where clause for the isDeleted, closed and enddate. (Oh - and throw address in there for good measure).

bemoCannon
Starting Member

3 Posts

Posted - 2007-02-14 : 06:58:37

Id like to be able to do a 'regular' join as you mentioned. However, as far as I can see it isn't possible.

If we just look at the two tables member and account. Every member can have multiple accounts, but generally only one active current account. The state of the account is determined by both not being expired by endDate, and by not being closed early with the closed value.

I want to return all members who have no active accounts. You cannot simply say, WHERE account.endDate < CURDATE() as this will return any member who has at least one account expired, but they may still have an active account.

Appreciate a solution.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-02-14 : 18:24:35

select * from members m
where
m.isDeleted=0 and
(select count(*) from account a where a.memberID=m.memberID and
account.endDate > CURDATE() and closed=0) = 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 04:02:36

[code]SELECT m.MemberID,
MAX(x.Barcode) AS Barcode
FROM Member AS m
LEFT JOIN (
SELECT MemberID
FROM Account
WHERE Closed = 0
GROUP BY MemberID
HAVING MAX(EndDate) >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
) AS a ON a.MemberID = m.MemberID
INNER JOIN Account AS x ON x.MemberID = m.MemberID
WHERE a.MemberID IS
GROUP BY m.MemberID
ORDER BY m.MemberID[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources