1

Can anyone help a newbie to SQL out please with this situation.

Given a membership database where there can be more than one member per membership account, is it possible to return all records from the membership table where no member (in the membername table) is over a specified age.

The tables look like this:

membership.id membership.membershipnumber membername.id membername.membershipnumber membername.name membername.age

Many thanks

Improve this question
2
  • 2
    I would hope that you're actually storing date of birth, rather than age. An age column requires constant maintenance, whereas dob should remain fixed (aside from error correction) Commented Feb 9, 2012 at 10:11
  • Yes, your correct, this is simpler test on though Commented Feb 9, 2012 at 10:57

2 Answers 2

Reset to default
1

There are probably faster methods but this is a straightforward way of doing it.

Select membershipnumber From membership Where membershipnumber Not In ( Select membershipnumber From membername Where age > @pAge )
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1 
Select distinct m.membershipnumber from membership s inner join (select membershipnumber from membername where age > 18) aux on aux.membershipnumber = m.membershipnumber

You can replace 18 by a @variable.

Improve this answer

4 Comments

That's not what the OP is asking, he's asked for any memberships (groups) that have NO member greater than a certain age.
@SteveHomer indeed, it's rectified. You still don't need two selects.
No still not right I think. If you have a membership (group) with 2 members one 17 and one 19 then this will be returned by your query. The OP is asking for a query that would return only the group where ALL members are less than 18 not where SOME members are less than 18.
@SteveHomer then I don't recall a way of doing avoiding the inner select :(

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.