How to write sql statement for this kind of condition?

i have a table that keep record of all available items and another table to keep record of used items.
i need to write statement that display unused item.
may i know how to write the sql statement for this condition?
thanks for any help, i really appreciate it very much.

what is the structure of your database ?
table names, columns, etc.

my tablename is benefits, benefitusage.
the columns that involve are
benefit table:
benefitid
benifitname

benefitusage table:
benefitid
benefitname

So i want to display items from table benefit where the ID does not apprear in benefitusage.
thanks a lot for your help.

Try this one
select * from beniftis where benfitid not in (select * from benifitusage)
......or somthing like this

[MSSQL]You can have a single table by adding a boolean column in the Benefit Table

Code:

---

benefit
---------
benefitid
benifitname
benefitUsage

---

Then get the unused

Code:

---

SELECT BenefitName FROM benefit
WHERE BenefitUsage=0

---

Anishkan's answer is the most sensible; Your database design is flawed.. If an item is usable or not, it should be present in only one table and a flag indicate whether it is used or not.

At the moment you are essentially storing everything upt-to twice, needlessly!

Typically, to find everything in one table that does not exist in another, we perform a LEFT join from the solid table to the fragile table, and apply a WHERE filter of fragile.pk IS NULL

If you dont know about left joins, I can provide some links

Quote:

---

Originally Posted by kamran123

Try this one
select * from beniftis where benfitid not in (select * from benifitusage)
......or somthing like this

---

No no no no no... Never ever ever ever use the IN keyword for a selection list longer than what youre prepared to type by hand (less than 100 items)

Additionally, this wont work because the nested subquery returns an incorrect number of columns.

Please, read some articles on how to write SQL that performs well, not just rubbish that gets the job done slowly, with high resource usage