Help with a join.

[Dashley]

Hi I need some help with a join.

The first query returns correct data. I want modify the query to pull data to add another colum called minamount.

The second query below pulls the correct minamount data.

shipmenid = shipmentid in both tables.
Also scac in tabel quotes is the same as custscac in table quoteaccscharge

Table quoteaccscharge may have data polulated for the shipmentid or it may not. It may have one or more rows of data populated for th shipmentid. If no data is present a return of zero would help.

the inner select in the second query is the same as the 1st query for the most part as far as the From, where, and clauses.

I hope there's enough info here. Thanks

SELECT Q.shipmentid , Q.quotedate, Q.scac,
convert(decimal(5,2), Q.totalCharge) as totalcharge
FROM quotes Q
WHERE Q.cid = '14'
AND Q.chosen = '1'
ORDER BY Q.quotedate desc

Shipmentid scac totalcharge
2010072400004 SEFL 88.00
2010072400003 SEFL 100.33
2010041300007 WTVA 80.07
2010041300004 WTVA 80.07
2010031700006 ODFL 124.91
2010031500002 VITR 170.46
2010030200004 VITR 96.47
2010021200006 WTVA 155.96
2010012100005 WTVA 122.96
2010011900005 VITR 131.45



select sum(minamt) as minamt,custscac, shipmentid
from quoteaccscharge where shipmentid in
(SELECT shipmentid
FROM quotes
WHERE cid = '14'
AND chosen = '1')
group by custscac, shipmentid
ORDER by custscac

minamt custscac shipmentid
146.00 FXFE 2010072400003
95.00 HMES 2010072400003
105.00 ODFL 2010072400003
65.60 RDWY 2010072400003
90.00 SEFL 2010072400003
81.00 WTVA 2010072400003
146.00 FXFE 2010072400004
95.00 HMES 2010072400004
105.00 ODFL 2010072400004
65.60 RDWY 2010072400004
90.00 SEFL 2010072400004
81.00 WTVA 2010072400004

 

[r937]

SELECT q.shipmentid 
     , q.quotedate
     , q.scac
     , CONVERT(DECIMAL(5,2),q.totalCharge) AS totalcharge 
     , s.minamt
     , s.custscac
     , s.shipmentid
  FROM quotes AS q
LEFT OUTER
  JOIN ( SELECT custscac
              , shipmentid
              , SUM(minamt) AS minamt
           FROM quoteaccscharge 
         GROUP 
             BY custscac
              , shipmentid ) AS s
    ON s.shipmentid = q.shipmentid
  FROM quotes
 WHERE q.cid  = 14
   AND q.chosen  = 1
ORDER 
    BY s.custscac

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Dashley]

r937 Thnak you for responding. I had a problem at line 7 stating:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'FROM'.

SELECT q.shipmentid,  q.scac, CONVERT(DECIMAL(5,2),q.totalCharge) AS totalcharge,
s.minamt,s.shipmentid 
FROM quotes as q 
LEFT OUTER JOIN (SELECT shipmentid, SUM(minamt) AS minamt
FROM quoteaccscharge 
GROUP BY shipmentid ) AS s ON s.shipmentid = q.shipmentid  
WHERE q.cid  = 14   
AND q.chosen  = 1
order by q.shipmentid

So I removed line 7, simce line 4 actualy targeted the table Quotes, changing it to :

SELECT q.shipmentid, q.quotedate, q.scac, CONVERT(DECIMAL(5,2),q.totalCharge) AS totalcharge,
s.minamt,s.shipmentid 
FROM quotes as q 
LEFT OUTER JOIN (SELECT shipmentid, SUM(minamt) AS minamt
FROM quoteaccscharge 
GROUP BY shipmentid ) AS s ON s.shipmentid = q.shipmentid  
WHERE q.cid  = 14   
AND q.chosen  = 1
order by q.shipmentid

The data returned, specifically in the minamt column is incorrect. Its retruning the sum of all rows in the select rater than the sun(minamt) for a specific shipmentid by custscac.

2010011900005	2010-01-19 13:48:01.000	VITR	131.45	NULL	NULL
2010012100005	2010-01-21 12:46:25.000	WTVA	122.96	NULL	NULL
2010021200006	2010-02-12 13:27:30.000	WTVA	155.96	NULL	NULL
2010030200004	2010-03-02 11:21:59.000	VITR	96.47	NULL	NULL
2010031500002	2010-03-16 10:04:19.000	VITR	170.46	NULL	NULL
2010031700006	2010-03-17 14:35:13.000	ODFL	124.91	NULL	NULL
2010041300004	2010-04-13 13:07:59.000	WTVA	80.07	NULL	NULL
2010041300007	2010-04-13 16:38:45.000	WTVA	80.07	NULL	NULL
2010072400003	2010-07-24 10:30:37.297	SEFL	100.33	582.60	2010072400003 
2010072400004	2010-07-24 10:40:39.450	SEFL	88.00	582.60	2010072400004

Note the last row. SEFL has two rows of data in the quoteaccscharge table shipmentid = 2010072400004

select custscac, minamt from quoteaccscharge where shipmentid = '2010072400004' and custscac = 'SEFL'

SEFL 51.00
SEFL 39.00

select sum(minamt) as minamt from quoteaccscharge where shipmentid = '2010072400004' and custscac = 'SEFL'

90.00

I've eliminated some of the col's to show just the basics. These two statement independantly work.

select totalcharge, shipmentid from quotes
WHERE cid = 14
AND chosen = 1


131.45 2010011900005
122.96 2010012100005
155.96 2010021200006
96.47 2010030200004
170.46 2010031500002
124.91 2010031700006
80.065 2010041300004
80.065 2010041300007
100.33 2010072400003
88.00 2010072400004

I dont really need custscac and shipment id in the select below but i do in the grouping

select sum(minamt) as minamt, custscac, shipmentid
from quoteaccscharge
where shipmentid in (select shipmentid from quotes
WHERE cid = 14
AND chosen = 1)
group by custscac, shipmentid
order by shipmentid

146.00 FXFE 2010072400003
95.00 HMES 2010072400003
105.00 ODFL 2010072400003
65.60 RDWY 2010072400003
90.00 SEFL 2010072400003
81.00 WTVA 2010072400003
146.00 FXFE 2010072400004
95.00 HMES 2010072400004
105.00 ODFL 2010072400004
65.60 RDWY 2010072400004
90.00 SEFL 2010072400004
81.00 WTVA 2010072400004

Hope this helps

 

[r937]

sorry, i'm totally lost

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Dashley]

Ha Me too. I think I'll mid teir this one through a couple of datasets

Thanks anyway