Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL QUERY HELP

timothymannahtimothymannah Member Posts: 1
I have 2 tables tbMembers and tbMembersAccessLvl

I know that a person may have more that one accesslevel in the
tbMemberAccessLvl, I only want to retrieve the highest number (the access
level can only be 0,1,2).
The statement below is retrieveing the lowest one because its the first one
it comes across.

SELECT tbMembers.MemberId, tbMembers.MemberPass, tbMembers.email,
tbMemberAccessLvl.AccessLvl
FROM tbMembers CROSS JOIN tbMemberAccessLvl
WHERE (tbMembers.MemberId = '402195') AND (tbMembers.MemberPass =
'ebony')

How can i get the record that supports the above statement but also gets the
highest tbMemberAccessLvl.AccessLvl.

There might be 3 records that have MemberId = '402195' and MemberPass =
'ebony' but i want the one with the highest value in
tbMemberAccessLvl.AccessLvl. Do i need to use the MAX prefix??

I tried SELECT tbMembers.MemberId, tbMembers.MemberPass,
tbMembers.email, MAX(tbMemberAccessLvl.AccessLvl) but the server said I need
a "Group by" not sure how any ideas.

Comments

  • The OschThe Osch Member Posts: 30
    [b][red]This message was edited by The Osch at 2005-4-4 22:59:25[/red][/b][hr]
    : I know that a person may have more that one accesslevel in the
    : tbMemberAccessLvl, I only want to retrieve the highest number (the access
    : level can only be 0,1,2).
    : The statement below is retrieveing the lowest one because its the first one
    : it comes across.
    :
    : SELECT tbMembers.MemberId, tbMembers.MemberPass, tbMembers.email,
    : tbMemberAccessLvl.AccessLvl
    : FROM tbMembers CROSS JOIN tbMemberAccessLvl
    : WHERE (tbMembers.MemberId = '402195') AND (tbMembers.MemberPass =
    : 'ebony')
    :
    : How can i get the record that supports the above statement but also gets the
    : highest tbMemberAccessLvl.AccessLvl.
    :
    : There might be 3 records that have MemberId = '402195' and MemberPass =
    : 'ebony' but i want the one with the highest value in
    : tbMemberAccessLvl.AccessLvl. Do i need to use the MAX prefix??
    :
    : I tried SELECT tbMembers.MemberId, tbMembers.MemberPass,
    : tbMembers.email, MAX(tbMemberAccessLvl.AccessLvl) but the server said I need
    : a "Group by" not sure how any ideas.
    :
    :
    'ebony'? Are you Russian? :-)
    The correct query can be like this:
    [code]SELECT tbMembers.MemberId, tbMembers.MemberPass, tbMembers.email, MAX(tbMemberAccessLvl.AccessLvl)
    FROM tbMembers LEFT JOIN tbMemberAccessLvl ON tbMembers.MemberId = tbMemberAccessLvl.MemberId
    /* or what field in tbMemberAccessLvl identifies a member? */
    WHERE tbMembers.MemberId = '402195' AND tbMembers.MemberPass = 'ebony'
    GROUP BY tbMembers.MemberId, tbMembers.MemberPass, tbMembers.email[/code]


Sign In or Register to comment.