• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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
0
Lulu0
Asked:
Lulu0
  • 3
  • 2
2 Solutions
 
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
 
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now