sqlsybasesqlanywhereisql

Dual results in Query

I have a query where I am getting double the results in my line items. Here is a copy of what I have. Do I have to do the joins differently? Here is also a screen shot of what the results look like. Every line item is doubled up. enter image description here

SELECT 
  ds_id as "TMP", 
  ds_ship_date as "Ship Date", 
  ds_ref1_text as "Container Number", 
  o.co_name as "Origin", 
  o.co_city as "Origin City", 
  o.co_state as "Origin State", 
  de_arrdate as "Deliver Date", 
  de_arrtime as "Arrival Time", 
  de_deptime as "Departure Time", 
  ds_bill_charge as "TMP Total Charges",
  (CASE ds_ship_type WHEN '2201' THEN 'MONTREAL'
    WHEN '2202' THEN 'DRYVAN'
    WHEN '2203' THEN 'BROKERAGE'
    WHEN '2204' THEN 'OLD BROKERAGE'
    WHEN '2205' THEN 'LIFTING'
    WHEN '2206' THEN 'WAREHOUSE'
  END) AS "Division",
  dba.disp_items.di_qty,
  dba.disp_items.RateCodename 
FROM dba.disp_ship 
  JOIN dba.disp_events ON de_shipment_id = ds_id 
  JOIN dba.disp_items ON DBA.disp_items.di_shipment_id = dba.disp_ship.ds_id
  JOIN dba.companies o ON o.co_id = ds_origin_id 
WHERE de_site = ds_findest_id 
  AND TMP = '70189'
ORDER BY ratecodename desc
Solution

  • Just use a GROUP BY. Try like this :

    SELECT      ds_id AS TMP, ds_ship_date AS ShipDate, ds_ref1_text AS ContainerNumber, o.co_name AS Origin, o.co_city AS OriginCity, o.co_state AS OriginState, de_arrdate AS DeliverDate, de_arrtime AS ArrivalTime, de_deptime AS DepartureTime, 
            ds_bill_charge AS TMPTotalCharges,
            CASE ds_ship_type
                    WHEN '2201' THEN 'MONTREAL'
                    WHEN '2202' THEN 'DRYVAN'
                    WHEN '2203' THEN 'BROKERAGE'
                    WHEN '2204' THEN 'OLD BROKERAGE'
                    WHEN '2205' THEN 'LIFTING'
                    WHEN '2206' THEN 'WAREHOUSE'
            END
            AS Division,
            dba.disp_items.di_qty, dba.disp_items.RateCodename 

FROM        dba.disp_ship
JOIN        dba.disp_events ON de_shipment_id = ds_id
JOIN        dba.disp_items ON DBA.disp_items.di_shipment_id = dba.disp_ship.ds_id
JOIN        dba.companies o ON o.co_id = ds_origin_id
WHERE       de_site = ds_findest_id
AND         TMP = '70189'
GROUP BY    TMP, ShipDate, ContainerNumber, Origin, OriginCity, OriginState, DeliverDate, ArrivalTime, DepartureTime, TMPTotalCharges, Division, di_qty, RateCodename
ORDER BY    ratecodename DESC