Sql Query 1

[bemoCannon]

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.

 

[r937]

  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

r937.com | rudy.ca

 

[bemoCannon]

I have made a mistake in my original post:
To detect expired accounts, the query should be:
(curdate() > enddate OR closed <> 0) //Expired accounts

Thanks r937,

There are two problems with this though.
Firstly, the account.memberid is a foreign key, so to maintain referential integrity, this cannot be null.
Secondly, this query will return all accounts that have expired. I want to return all members that have no active accounts. A member can have many expired accounts and an active account. This member should not be returned.

Thanks.

 

[r937]

i don't think you understand how a LEFT OUTER JOIN works

the IS NULL test will identify rows from the left table which have no matching row in the right table, for the particular join conditions specified

so the ON clause specifies the conditions for an active account

then the WHERE clause selects those rows where a match was not found

in short, look for active accounts, and if none is found, then that particular member is one of the ones you want

r937.com | rudy.ca

 

[bemoCannon]

Apologies r937 and thanks a lot. It worked perfectly.

 

[bemoCannon]

Hi and thanks in advance.

The code below returns the members who have accounts that are inactive, and works correctly thanks to r937.
It returns the memberid, but returns null values for the barcode values. How can I fix this problem?

Thanks

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
GROUP BY member.memberid;

 

[r937]

this is mysql, right? because no other database lets you run a query with an invalid GROUP BY clause

when you do this --

WHERE account.Memberid is null

you are 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 you want because of that WHERE condition

therefore account.barcode will always be NULL too


r937.com | rudy.ca

 

[bemoCannon]

Thanks again.
If I have a SUM, AVG.... or any of these functions in the SELECT statement, don't I need the GROUP BY clause? If there is a more refined way please let me know.

Also, I realise the reason for the null values, but what would be the most effecient way of solving it? I'm very new with SQL so the only way I know is to perform a seperate query with the list of memberid's attainted from the above to get the account fields?
There must be a better way?

Thanks again.

 

[r937]

If I have a SUM, AVG.... or any of these functions in the SELECT statement, don't I need the GROUP BY clause?

not if aggregate functions are the only expressions in the SELECT

as soon as you have a non-aggregate in the SELECT, then it must also be in the GROUP BY

okay, examples

the following are all good --

select a ...
select sum(m) ...
select a ... group by a
select a,sum(p) ... group by a
select a,b ... group by a,b
select a,b,sum(q) ... group by a,b

the following are also good but not useful --

select a ... group by a,b
select a,sum(p) ... group by a,b
select a,b ... group by a,b,c
select a,b,sum(r) ... group by a,b,c

the following are invalid --

select a,sum(p) ...
select a,b ... group by a
select a,b,sum(r) ... group by a

mysql actually will execute these, but to be fair they do warn you that this is non-standard and can produce unpredictable results

r937.com | rudy.ca

 

[bemoCannon]

what would be the most efficient way of solving the null return problem? I'm very new with SQL so the only way I know is to perform a separate query with the list of memberid's attainted from the above to get the account fields?

Doing this by obtaining just the memberid into a String and the requiring is not feasible as it takes far too long.

There must be a better way?

Possibly using a MySQL user defined function?
Thanks

 

[r937]

what "null return problem" are you talking about?

i already explained why the columns from the account table are going to be null in a LEFT OUTER JOIN

and the reason you're using a LEFT OUTER JOIN is because... "I want to query all the members who have no active accounts"

r937.com | rudy.ca