0

Can someone suggest a better way to write this query? The goal is to get all active members, except members with member type of EXH or AUX, no Canada members or members with NULL in the country.

I have tried many examples from this site but still getting too many results.

SELECT
    *
FROM
    `members`
WHERE
    `STATUS` = 'A' AND `MEMBER_TYPE` <> 'EXH' AND `MEMBER_TYPE` <> 'AUX' AND 
    `COUNTRY` <> 'Canada' AND `COUNTRY` IS NOT NULL
CDspace
  • 2,639
  • 18
  • 30
  • 36
HSMedia
  • 1
  • 1
  • You mentioned you are getting too many results... are the results correct though? Based on your description, your query does exactly that. – J. D. Jul 31 '17 at 18:41
  • you can add a few improvements - to limit the results for example if you only want 10 results, do Select TOP 10 *, you can also add with(nolock) to your table to prevent locking rows. to return less results though, you will need more criteria for the where – Josh Adams Jul 31 '17 at 18:42
  • 2
    as an aside, you should list the columns you want to select, rather than *. see https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select – Kai Jul 31 '17 at 18:43
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Jul 31 '17 at 18:54
  • Are you getting results with member type EXH or AUX, or Canada members or members with nulls in the country? – isaace Jul 31 '17 at 19:00
  • I changed the code to :SELECT ID, PREFIX, FIRST_NAME, LAST_NAME, FULL_ADDRESS, EMAIL FROM `members_name` WHERE `STATUS` = 'A' AND `MEMBER_TYPE` <> 'EXH' AND `MEMBER_TYPE` <> 'AUX' AND `COUNTRY` <> 'Canada' AND `COUNTRY` IS NOT NULL – HSMedia Jul 31 '17 at 19:16
  • @JoshAdams, you are giving Microsoft SQL Server proprietary syntax, not supported by MySQL. – Bill Karwin Jul 31 '17 at 19:21
  • @BillKarwin thanks for the catch, out of habit. Should use Limit for mysql. – Josh Adams Jul 31 '17 at 19:32

2 Answers2

0

By default, a SQL query will return all records that match.

If you want to return a smaller set of records, you need to explicitly state that. The methods for doing so vary from one DB to another; for MySQL, you'll need to add LIMIT [n], where [n] is the number of results you want.

You should be a bit careful doing this, though. If you are going to want the rest of them - like, maybe you want "the first 100 results, but then later you want the next 100", you'll need to track an offset in addition. This can introduce other complexities depending on the ordering of the records... but from your question, it sounds like such concerns may be outside the scope of your present issue. (If not, feel free to edit your question with more detail about your use case.)

autophage
  • 196
  • 1
  • 9
0

i think this does the work too with slight short handingSELECT * FROMmembersWHERESTATUS= 'A' ANDMEMBER_TYPEnot in ('EXH','AUX') ANDCOUNTRYIS NOT NULL ANDCOUNTRY<> 'Canada'

Chris Spil
  • 21
  • 2