sql query

Question

I have 2 tables..

A

ID      key
123    24
123    23
123    22
    
B

ID    KEY2
123    23
123    22
123    -1

Hi, could any one help me to write a query . Please.
I need to find all those ID from table A where key = 24 is not present in table key for same id. Key = 24 is constant.
Query need to be generic as i have many records in both table.

---

Abhishek

Answer 1

Sorry cannot test it

select  * from a where not exists (select * from b where b.id=a.id )

and a.key=24

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Live example:

use AdventureWorks2012;
select  * from Purchasing.PurchaseOrderDetail a 
where not exists (select * from Production.ProductReview b where b.ProductID=a.ProductID )
and a.OrderQty=3;
-- (4136 row(s) affected)

---

Kalman Toth Database & OLAP Architect Free T-SQL Scripts
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 3

Hi URI, Its not giving me any result :(

---

Abhishek

Answer 4

Hi Toth,

Ur query giving me no result.

Although its working

select distinct(ID)from A
where key = 24
--and effective_dw_enddate IS NULL
and ID NOT in
(

select distinct  ID from B
where key2= 24)

Could you tell me the difference???

---

Abhishek

Answer 5

Try following simple query,

Select a.ID, a.Key From a inner join b on a.id = b.id and a.key <> b.key2
Where a.Key = 24

Answer 6

Shenoy , something is missing. I checked , the result is not appropriate

---

Abhishek

Answer 7

i think this

SELECT *
FROM TableB b
WHERE NOT EXISTS (SELECT 1
FROM TableA
WHERE ID = b.ID
AND  Key = 24
)

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 8

Visakh

Ur query Not giving me right result

Although its working

select distinct(ID)from A
where key = 24
--and effective_dw_enddate IS NULL
and ID NOT in
(

select distinct  ID from B
where key2= 24)

Could you tell me the difference???

---

Abhishek

Answer 9

How can we use Join To implement this??

---

Abhishek

Answer 10

What results you are getting with the query?

Answer 11

I need those id from A which are present in table B but dont have key = 24.

acc to my values , i should get 14 rows.. with ur query i am getting 3900 rows.

---

Abhishek

Answer 12

Any body can help?

---

Abhishek

Answer 13

Then you must b missing something in your conditions.

Answer 14

Try

select A.ID

FROM TableA A

INNER JOIN TableB B ON A.ID = B.ID

WHERE NOT EXISTS (SELECT 1 FROM TableB B1 WHERE B1.ID = B.ID and B1.Key = 24)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 15

Hi Naomi,

Your  query returns 0 rows.

I am able to do this by subquery but unable to implement it in joins.

Subquery(working fine)

select distinct(ID)from A
where key = 24
--and effective_dw_enddate IS NULL
and ID NOT in
(

select distinct  ID from B
where key2= 24)

---

Abhishek

Answer 16

I need those id from A which are present in table B but dont have key = 24.

acc to my values , i should get 14 rows.. with ur query i am getting 3900 rows.

---

Abhishek

You have discarded every solution. Might be that you have not been able to explain your actual requirement. Tell us that with the sample data you have provided (Not DDL, not DML, plain English), what is the expected output. Use below provided DDL & DML if you need.

CREATE TABLE #AbhishekForum_A(	AF_ID SMALLINT NOT NULL, 
				AF_a_Key SMALLINT NOT NULL) 
INSERT #AbhishekForum_A
SELECT 123,24 UNION 
SELECT 123,23 UNION 
SELECT 123,22 
CREATE TABLE #AbhishekForum_B(	AF_ID SMALLINT NOT NULL, 
				AF_b_Key SMALLINT NOT NULL)
INSERT #AbhishekForum_B
SELECT 123,23 UNION 
SELECT 123,22 UNION 
SELECT 123,-1 

According to me, with what I have been able to understand so far, below script would gve you the required results.

SELECT * 
FROM #AbhishekForum_A A
WHERE A.AF_ID IN (SELECT DISTINCT B.AF_ID FROM #AbhishekForum_B B WHERE AF_b_Key<>24 AND A.AF_ID=B.AF_ID)

Above code will result in 3 rows as 123 is the ID where Key<>24. But might be your requirement is different. Check below code.

CREATE TABLE #AbhishekForum_A(AF_ID SMALLINT NOT NULL, AF_a_Key SMALLINT NOT NULL) 
INSERT #AbhishekForum_A
SELECT 123,24 UNION 
SELECT 123,23 UNION 
SELECT 123,22 
CREATE TABLE #AbhishekForum_B(AF_ID SMALLINT NOT NULL, AF_b_Key SMALLINT NOT NULL)
INSERT #AbhishekForum_B
SELECT 123,23 UNION 
SELECT 123,22 UNION 
SELECT 123,-1 UNION 
SELECT 123,24

SELECT A.*
FROM #AbhishekForum_A A JOIN #AbhishekForum_B B ON A.AF_ID=B.AF_ID 
WHERE B.AF_ID NOT IN (SELECT DISTINCT AF_ID FROM #AbhishekForum_B C WHERE C.AF_b_Key=24)

In this code, I have added 24 as one of the row in Table B having ID as 123. Now since this ID has 24 as a key, thus this should not be selected from Table A.

These are the only 2 ways, I could interpret your problem statement.

 

---

Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.