Distinct SQL results

I'm trying to query parts where there is no holds on them

The order number (sopnumbe) and parts itemnmbr) are in a table sop10200
and the holds (delete1) are in table sop10104. A hold in sop10104 is where delete1='0'

Since the holds table (sop10104) can have multiple holds I need to join on the distinct results of sop10104 where delete1<>'0'

when I run the below query I get a "The multi-part identifier "sop2.sopnumbe" could not be bound."

For the life of me I can't see where the issue lies.


select sop2.*
from sop10200 sop2
join (select distinct(sopnumbe) from sop10104 sop4a
            where delete1<>'0'
            and sop4a.sopnumbe=sop2.sopnumbe) sop4
on sop4.sopnumbe=sop2.sopnumbe
where sop2.itemnmbr='00-539'
jdr0606Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Remove line 9 (seen below) that is where you are referring to sop2 but sop2 is NOT available inside that subquery. Just remove the line it isn't needed.
SELECT
      sop2.*
FROM sop10200 sop2
      JOIN (
            SELECT DISTINCT
                  sopnumbe
            FROM sop10104 sop4a
            WHERE delete1 <> '0'
-- >> HERE                       AND sop4a.sopnumbe = sop2.sopnumbe
      ) sop4 ON sop4.sopnumbe = sop2.sopnumbe
WHERE sop2.itemnmbr = '00-539'

Open in new window


However this query might NOT be accurate, can the table sop10104  have records for a single item like this?

sopnumbe delete1
123              0
123              1

i.e. if that table has BOTH zero and one (0 and 1) in that column what should happen?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Try using EXISTS and/or NOT EXISTS instead.
Lines 11 to 16 are "optional" I don't know if you need it or not.
SELECT
      sop2.*
FROM sop10200 sop2
WHERE sop2.itemnmbr = '00-539'
      AND EXISTS (
            SELECT NULL
            FROM sop10104 sop4a
            WHERE delete1 <> '0'
                  AND sop4a.sopnumbe = sop2.sopnumbe
      )
      AND NOT EXISTS (
            SELECT NULL
            FROM sop10104 sop4a
            WHERE delete1 = '0'
                  AND sop4a.sopnumbe = sop2.sopnumbe
      )

Open in new window

0
Olaf DoschkeSoftware DeveloperCommented:
Just about the logic of it:

When you look for parts having a hold on them its sufficient to find a related record with a hold (delete1='0'), but if you look for parts having no hold the opposite of that is not simply delete1<>'0', you need to count grouped by part itemnmbr and delete1 and find no records with delete1='1', so your whole logic needs to be addressed differently, like this:

declare @parts as Table (itemnmbr int IDENTITY)
declare @holds as Table (holdnmbr int IDENTITY, itemnmbr int, delete1 char(1))

Insert Into @parts default Values;
Insert Into @parts default Values;
Insert Into @parts default Values;

--using h for hold and - for no hold. Only part number 1 has no holds and should be in the result.
Insert Into @holds Values (1, '-'), (1, '-'), (2,'h'), (2,'h'), (3,'h'), (3,'-');


--this is round about what you do now
select p.*
 from @parts p
 join (select distinct(itemnmbr) as itemnmbr from @holds where delete1<>'h') h
 on h.itemnmbr = p.itemnmbr 
-this includs partno 3, as there is one record without hold.
 
--the solution is to count holds with condition to find 0
 select p.* 
 from @parts p
 join ( select itemnmbr from @holds group by itemnmbr 
         having count(case delete1 when 'h' then 1 ELSE NULL END) = 0) h
 on h.itemnmbr = p.itemnmbr

Open in new window



Bye, Olaf.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.