CODEWITHSUNDEEP
mysqlsql
TPranara
TPranara

Reputation: 21

How do I add a NOT clause to my SQL query

I would like to add a NOT clause to this SQL query:

Select 
    members.Member_Id, 
    members.Title, 
    members.FirstName, 
    members.LastName, 
    members.Po_Box, 
    members.Street, 
    members.City, 
    members.Del, 
    members.Mobile, 
    members.eMail, 
    members.WFTD, 
    ship_info.Renewal_Date
From 
    members 
Left Join
    ship_info on (members.Member_Id = ship_info.Member_Id)
Order By
    ship_info.Renewal_Date

The NOT clause is this:

Where Member_Id Not Between 2000 And 3000;

I have tried to place this line in different places, but get an error each time, and since Wamp reports errors in french, find it unhelpful.

Yes there are similar questions like this, but they confuse me more, as I don't understand them enough to modify my script accordingly. I don't know much about arrays and complex code.

I export data from the database for mail merge purposes, and Members whose Ids are in the 2000s are deleted Members, whose Id's were moved from their original Id to that of higher numbers, as I don't like deleting people permanently in case they change their mind later down the track.

Thank you.

Upvotes: 1

Views: 92

Answers (4)

Joaquin G.
Joaquin G.

Reputation: 11

According to "Database Administration Fundamentals", the following would be the appropriate syntax: 

Select members.Member_Id
  ,members.Title
  ,members.FirstName
  ,members.LastName
  ,members.Po_Box
  ,members.Street
  ,members.City
  ,members.Del
  ,members.Mobile
  ,members.eMail
  ,members.WFTD
  ,ship_info.Renewal_Date
From members 
LEFT JOIN ship_info ON (members.Member_Id=ship_info.Member_Id)

WHERE NOT members.Member_Id BETWEEN 2000 AND 3000

ORDER BY ship_info.Renewal_Date ;

In the previously mentioned book, it states,

" JOIN statements can be specified in either the FROM or the WHERE clause, but it is recommended that you specify them in the FROM clause ".

As for clause precedence, here is the correct order to follow: 

SELECT     -- "what" we want
FROM       -- "where" to look for it
JOIN
WHERE      -- condition 
GROUP BY  
HAVING
ORDER BY   -- sorting order

Upvotes: 1

Aman Prajapati
Aman Prajapati

Reputation: 157

I think below should work, if not then please specify error which you got, you just need to prefix table name members (better if you use this table name as alias)

SELECT members.Member_Id
    ,members.Title
    ,members.FirstName
    ,members.LastName
    ,members.Po_Box
    ,members.Street
    ,members.City
    ,members.Del
    ,members.Mobile
    ,members.eMail
    ,members.WFTD
    ,ship_info.Renewal_Date
FROM members
LEFT JOIN ship_info ON (members.Member_Id = ship_info.Member_Id)
WHERE members.Member_Id NOT BETWEEN 2000
        AND 3000
ORDER BY ship_info.Renewal_Date

Upvotes: 0

NorthernMonkey
NorthernMonkey

Reputation: 34

You could try it like this:

Select members.Member_Id
  ,members.Title
  ,members.FirstName
  ,members.LastName
  ,members.Po_Box
  ,members.Street
  ,members.City
  ,members.Del
  ,members.Mobile
  ,members.eMail
  ,members.WFTD
  ,ship_info.Renewal_Date
From members 
LEFT JOIN ship_info ON (members.Member_Id=ship_info.Member_Id)

WHERE (members.Member_ID < 2000 OR members.member_ID > 3000)

ORDER BY ship_info.Renewal_Date

Upvotes: 2

apomene
apomene

Reputation: 14389

Since you are using a JOIN and member_id field resides in both tables of the JOIN, you must specify for which table you need the where statement. Try:

SELECT members.Member_Id
    ,members.Title
    ,members.FirstName
    ,members.LastName
    ,members.Po_Box
    ,members.Street
    ,members.City
    ,members.Del
    ,members.Mobile
    ,members.eMail
    ,members.WFTD
    ,ship_info.Renewal_Date
FROM members
LEFT JOIN ship_info ON (members.Member_Id = ship_info.Member_Id)
WHERE members.Member_Id NOT BETWEEN 2000
        AND 3000
ORDER BY ship_info.Renewal_Date

Upvotes: 1

Related Questions