sql logic

I am querying for a unique key in Table A called column X.  Table Column X joins to Table B which contains 3 records.  If column Z  of Table B contains the word "BAD" for all three records, I want to select the unique key from Table A.  However, if just one of those fields contains the word "GOOD", I do NOT want to select the unique key from Table A.  

How do I write this query?  Since the logic looks at a per record basis, I cannot just say "give me column X where column Z != "Good"

Ryan
Lulu0Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

onlyaymieCommented:
select b.x from tableA a join tableB b on a.x=b.x where b.z = 'BAD' group by b.x having count(*) = 3
0
Lulu0Author Commented:
=)

Well - I'm definitely not going to know the count of what I am trying to fetch.  And, the query is a bit more complicated that what I was trying to explain.  Here is the actual query:

select distinct ld.load_id
from transportation:load as ld
inner join transportation:frt_shipment_load as fsl on fsl.load_id = ld.load_id
inner join transportation:freight_shipment as fs on fs.shipment_id = fsl.shipment_id
inner join transportation:dcs2000_po as dcs on dcs.po_nbr = fs.po_nbr
where ld.frt_move_catg_code = 'IGDC'
and ld.orig_loc_type = 'VNDR'
and ld.dest_loc_type = 'DC'
and dcs.received_ts >= current
and dcs.trans_method_code = 'B'
and dcs.dcs_dlvry_type_cd in ('N')
and dcs.dcs_dlvry_type_cd not in ('N')
and dcs.dcs_pymt_mthd_code = 'D'  
and dcs.std_message_txt in('CLLCT', 'CRMX')
and ld.load_id in (70441108)

The table load joins to freight_shipment_load, which joins to freight_shipment, which finally joins to dsc2000_po.  dcs2000_po has multiple records.  If just one of these for the field dcs_dlvry_type_cd is 'T' instead of 'N', then I do not want to select the 1 load id that relates to these three records in the table dcs2000_po.

Ryan
0
onlyaymieCommented:
so I'm assuming that this part of the query is not working the way you want, b/c it seems like it would return no rows in all cases:

and dcs.dcs_dlvry_type_cd in ('N')
and dcs.dcs_dlvry_type_cd not in ('N')

You could try an exists and see how that performs.  I'm guessing that you have multiple rows for each dcs.po_nbr:

select distinct ld.load_id
from transportation:load as ld
inner join transportation:frt_shipment_load as fsl on fsl.load_id = ld.load_id
inner join transportation:freight_shipment as fs on fs.shipment_id = fsl.shipment_id
inner join transportation:dcs2000_po as dcs on dcs.po_nbr = fs.po_nbr
where ld.frt_move_catg_code = 'IGDC'
and ld.orig_loc_type = 'VNDR'
and ld.dest_loc_type = 'DC'
and dcs.received_ts >= current
and dcs.trans_method_code = 'B'
and dcs.dcs_dlvry_type_cd = 'N'
and not exists (select dcs2.dcs_dlvry_type_cd from transportation.dcs2000_po dcs2 where dcs_dlvry_type_cd = 'T' and dcs2.po_nbr = dcs.po_nbr)
and dcs.dcs_pymt_mthd_code = 'D'  
and dcs.std_message_txt in('CLLCT', 'CRMX')
and ld.load_id in (70441108)



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
Lulu0Author Commented:
doesn't work.  when you do the "not exists" query, you are still looking at the po level.  that level wraps up to the load level.

I got it working with the following query:

set isolation dirty read;
select distinct ld.load_id
from transportation:load as ld
inner join transportation:frt_shipment_load as fsl on fsl.load_id = ld.load_id
inner join transportation:freight_shipment as fs on fs.shipment_id = fsl.shipment_id
inner join transportation:carr_load_assign as cla on cla.load_id = ld.load_id
inner join transportation:dcs2000_po as dcs on dcs.po_nbr = fs.po_nbr
WHERE  (cla.carrier_id not like 'W-%'  and cla.carrier_id not like 'Z-%')
and  dcs.trans_method_code = 'B'
and dcs.dcs_dlvry_type_cd in ('N')
and not exists (
select dcs2.dcs_dlvry_type_cd
from transportation:load as ld2
inner join transportation:frt_shipment_load as fsl2 on fsl2.load_id = ld2.load_id
inner join transportation:freight_shipment as fs2 on fs2.shipment_id = fsl2.shipment_id
inner join transportation:dcs2000_po as dcs2 on dcs2.po_nbr = fs2.po_nbr
where  dcs2.dcs_dlvry_type_cd in ('T')
and ld2.load_id in (ld.load_id)
)
and dcs.dcs_pymt_mthd_code = 'D'  
and dcs.std_message_txt in('CLLCT', 'CRMX')
and ld.load_id in (70441108, 74871370)
0
Lulu0Author Commented:
The "not exists" helped guide me in the right direction, but did not solve the problem.  My query solved the problem, which I posted above...
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
Databases

From novice to tech pro — start learning today.