-1

i have a user table with one attribute as habits that has valus like shopping,sports etc. Now when i log in to my application i get the username from the FORM tag and this is used in javascript for further use. I need a query that displays all the user table contents where habits=shopping but it shouldnt display the details of the currently logged in user. The query i used for this is,
select * from user where habits='shopping' except select * from user where username='niranjan';

But this line is generating an error stating that the EXCEPT identifier is not a valid input at this point.
pls correct my error or provide an alternative code for my issue.

nagraj h
  • 11
  • 3
  • 1
    What RDBMS do you use? Not all of them support `EXCEPT`, but if you post the structure of the `user` table we can assist in correcting the query without it (possibly as simple as `SELECT * FROM user WHERE habits='shopping' AND username <> 'niranjan'`) – Michael Berkowski May 28 '15 at 20:03
  • thanks a lot @micheal berkowski !!! it actually worked. I used the ' <> ' symbol that helped me solve my problem – nagraj h May 28 '15 at 20:09

3 Answers3

0
select * from user where habits='shopping' and username!='niranjan';
JustAPup
  • 1,720
  • 12
  • 19
0

No need for except here. Just add a condition to your where caluse:

And username <> 'niranjan'
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

The problem may be that user is a reserved word for SQL Server. I would suggest that you rename the table to users to get around this problem.

In the meantime, you can use square braces for the query:

select * from [user] where habits = 'shopping'
except
select * from [user] where username = 'niranjan';

However, it is bad form to use reserved words for table and column names.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Though I agree that using reserved words is a bad habit, why keep the except when it can clearly be replaced with adding a condition to the first select statment? Am I missing something here or are you? – Zohar Peled May 29 '15 at 08:16
  • @ZoharPeled . . . I think you are right, because of the `select *`. There are cases where the `select` could have a subset of columns, and the two versions would not be equivalent. – Gordon Linoff May 29 '15 at 14:12