SQL Query assiff79 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query assiff79

Author

Topic

assiff79
Starting Member

7 Posts

Posted - 2014-06-20 : 04:00:09

Hi,
I have two tables; one contains all books data and second contain
students data with books Id. How to get those books that are not rented to a student with say ID 9 from table one? book id column contains repeating values of book id as this table contain data of students data with book id.

asif

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-20 : 04:48:24

[code]
SELECT *
FROM all_books_data b
WHERE NOT EXISTS
(
SELECT *
FROM students_data s
WHERE s.ID = 9
AND s.books_id = b.books_id
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-06-21 : 05:02:49

there are multiple ways of doing this
some of the alternatives are


SELECT b.*
FROM all_books_data b
LEFT JOIN students_data s
ON s.books_id = b.books_id
AND s.ID = 9
WHERE s.books_id IS NULL

SELECT *
FROM   all_books_data b
WHERE b.books_id NOT IN
       (
           SELECT books_id
           FROM   students_data s
           WHERE  s.ID = 9
       )

SELECT *
FROM   all_books_data b
WHERE  b.books_id NOT IN
       (
           SELECT books_id
           FROM   all_books_data
           EXCEPT
           SELECT books_id
           FROM students_data 
           WHERE  ID = 9
       )
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources