CODEWITHSUNDEEP
sqlms-accessms-access-2010
Kaw4Life
Kaw4Life

Reputation: 209

SubQuery Only First Match

Working with tables created by an idiot. Table in question has no record id's, with part number's and quantity's. Some part numbers will be repeated dozens of times all with no quantity's, some with. If a part number in ANY record has a quantity I want that part number JUST once so that the sub is not feeding the main more than one value at a time.

SELECT d046d, e024a
FROM 20121
WHERE d046d=(select sc.d046d from 20121 as sc where e024a >0)

This will fail because it will find more than one record in the subquery. I have tried DISTINCT and FIRST and ORDER BY's with TOP 1.

All fail when part of main query, work as needed by themselves.

Upvotes: 0

Views: 94

Answers (3)

Barry
Barry

Reputation: 3723

Use 

SELECT DISTINCT 
    d046d, 
    e024a
FROM 
    20121
WHERE 
    e024a >0

Upvotes: 1

radar
radar

Reputation: 13425

you don't need a sub query for WHERE . if you want only the first record you can get it with TOP clause , add order by

SELECT TOP 1 d046d, e024a
FROM 20121
WHERE  e024a >0
ORDER BY e024a desc

Upvotes: 1

Hituptony
Hituptony

Reputation: 2860

SELECT TOP 1 b.d046d, a.e024a
FROM 20121 a
inner join
(select sc.d046d as d046d from 20121 as sc where e024a >0) b
on a.d046d = b.d046d

This is not a suitable solution for the WHERE clause. I think you need to join to the sub query, as a self-join. From there you can just select the top 1 of the result set that comes up. You can play with the aliases as you like.

Upvotes: 1

Related Questions