Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with JOIN and getting multiple rows for record 1

Status
Not open for further replies.
patrussell

patrussell

Technical User
May 14, 2001
71
US
Using this query I am very close to getting the results I want but cannot find where I am going wrong. I will post the initial query with results and then explain what I am looking for.

Code: 
SELECT		 	b.CoilID, MIN(a.[Entry Tower]) as [Min Tower Hgt], AVG(a.Grade) as [Grade], 
			AVG(a.Gauge) as [Gauge],AVG(a.Width) as [Width]

FROM         		dbo.BA_ALL_COILS a LEFT OUTER JOIN
                 	dbo.Coil_List b ON a.CoilID = b.CoilID

Where 			a.[Entry Tower] is not null

Group BY 		b.CoilID
Order By		b.CoilID

gives this result

CoilID Min Tower Hgt Grade Gauge Width
--------------- ------------- ----------- ----------- -----------
1035n743 49 304 62 39
1045n033 38 304 46 49
1045n034 34 304 46 49
10514c158 34 430 73 49
10534c191 32 430 73 49
10554n036a 49 304 58 48
1065n516 61 304 57 38
12034n084 48 304 46 49
12064n441 50 305 49 49
12114n784 63 201 48 31
12114n785 66 201 48 31
12144n280 73 201 48 31
12144n296 71 201 48 31
12174n347 35 304 34 49
12204n894 54 304 46 37
12224n198 36 304 57 49
12234n502 63 201 48 31
12524c504 48 430 73 49
8424n114 46 301 90 49
8424n115 46 301 90 49


This is great but I need two other columns to get my desired data. I need to add a [Time Stamp] and [Footage] column but each time I try to add one or the other I no longer have one row per CoilID.

This is the query as I am trying it
Code: 
SELECT     b.CoilID, MIN(a.[Entry Tower]) AS [Min Tower Hgt], AVG(a.Grade) AS Grade, 
	   AVG(a.Gauge) AS Gauge, AVG(a.Width) AS Width, a.Footage, a.[Time Stamp]
FROM       dbo.BA_ALL_COILS a LEFT OUTER JOIN
               dbo.Coil_List b ON a.CoilID = b.CoilID
WHERE      (a.[Entry Tower] IS NOT NULL)
GROUP BY   b.CoilID, a.Footage, a.[Time Stamp]
ORDER BY   b.CoilID

This give the following results (snipped for space)

CoilID Min Tower Hgt Grade Gauge Width Footage Time Stamp
--------------- ------------- ----------- ----------- ----------- ----------- ------------------------------------------------------
1035n743 96 304 62 39 1 2005-01-18 04:17:22.687
1035n743 96 304 62 39 11 2005-01-18 04:17:39.780
1035n743 96 304 62 39 21 2005-01-18 04:17:56.920
1035n743 96 304 62 39 31 2005-01-18 04:18:17.297
1035n743 96 304 62 39 42 2005-01-18 04:18:37.203
1035n743 96 304 62 39 52 2005-01-18 04:19:00.797
1035n743 95 304 62 39 62 2005-01-18 04:19:21.217
1035n743 96 304 62 39 73 2005-01-18 04:19:45.077
1035n743 96 304 62 39 83 2005-01-18 04:20:05.420
1035n743 96 304 62 39 93 2005-01-18 04:20:29.407
1035n743 96 304 62 39 103 2005-01-18 04:20:49.890
.
.
.

I only need the [Time Stamp] and [Footage] for the minimum tower height, not every record. If there are multiple records at the same minimum the earliest timestamp should decide which to use.

Thanks for any help you can give...




Pat Russell
 
Sort by date Sort by votes
I haven't tested this, but maybe somwthing like this will work.

select A.CoilID, A.[Entry Tower], A.[Min Tower Hgt], A.Grade, A.Guage, A.Width, B.Footage, B.[Time Stamp]
from
(SELECT b.CoilID, MIN(a.[Entry Tower]) AS [Min Tower Hgt], AVG(a.Grade) AS Grade,
AVG(a.Gauge) AS Gauge, AVG(a.Width) AS Width
FROM dbo.BA_ALL_COILS a LEFT OUTER JOIN
dbo.Coil_List b ON a.CoilID = b.CoilID
WHERE (a.[Entry Tower] IS NOT NULL)
GROUP BY b.CoilID) A
LEFT JOIN
(SELECT CoilID, [Entry Tower], Footage, [Time Stamp]
FROM dbo.BA_ALL_COILS) B
ON A.CoilID=B.CoilID AND A.[Min Tower Hgt]=B.[Entry Tower]
ORDER BY b.CoilID

Tim
 
Upvote 0 Downvote
Tim,

That's pretty close but I'm still getting some duplicates when there are multiple rows with the same minimum height.

Code: 
SELECT 	A.CoilID, A.[Min Tower Hgt], A.Grade, A.Gauge, A.Width, B.Footage, B.[Time Stamp]
FROM
	(SELECT    	b.CoilID, MIN(a.[Entry Tower]) AS [Min Tower Hgt], AVG(a.Grade) AS Grade, 
       			AVG(a.Gauge) AS Gauge, AVG(a.Width) AS Width
 	FROM       	dbo.BA_ALL_COILS a LEFT OUTER JOIN
               		dbo.Coil_List b ON a.CoilID = b.CoilID
 	WHERE      	a.[Entry Tower] IS NOT NULL
 	GROUP BY   	b.CoilID) A
LEFT JOIN
	(SELECT CoilID, [Entry Tower], Footage, [Time Stamp]
 	FROM dbo.BA_ALL_COILS) B

  ON 	A.CoilID=B.CoilID AND A.[Min Tower Hgt]=B.[Entry Tower]

ORDER BY   	b.CoilID

Results:

CoilID Min Tower Hgt Grade Gauge Width Footage Time Stamp
--------------- ------------- ----------- ----------- ----------- ----------- ------------------------------------------------------
1035n743 49 304 62 39 1620 2005-01-18 05:16:36.780
1035n743 49 304 62 39 1670 2005-01-18 05:18:28.170
1035n743 49 304 62 39 1760 2005-01-18 05:21:45.547
1035n743 49 304 62 39 1770 2005-01-18 05:22:09.780
1035n743 49 304 62 39 1790 2005-01-18 05:22:51.360
1045n033 38 304 46 49 2476 2005-01-17 17:21:22.297
1045n034 34 304 46 49 2445 2005-01-17 18:34:18.937
10514c158 34 430 73 49 0 2005-01-18 10:44:24.420
10534c191 32 430 73 49 1307 2005-01-18 10:44:00.687
10554n036a 49 304 58 48 0 2005-01-18 08:49:00.687
10554n036a 49 304 58 48 77 2005-01-18 09:08:24.077
10554n036a 49 304 58 48 97 2005-01-18 09:09:25.390
10554n036a 49 304 58 48 144 2005-01-18 09:11:33.827
10554n036a 49 304 58 48 154 2005-01-18 09:11:54.280
10554n036a 49 304 58 48 164 2005-01-18 09:12:15.000
10554n036a 49 304 58 48 224 2005-01-18 09:14:17.767
10554n036a 49 304 58 48 234 2005-01-18 09:14:38.437
10554n036a 49 304 58 48 264 2005-01-18 09:15:40.157
10554n036a 49 304 58 48 325 2005-01-18 09:17:46.077
10554n036a 49 304 58 48 405 2005-01-18 09:20:29.217
1065n516 61 304 57 38 0 2005-01-17 20:44:27.907
12034n084 48 304 46 49 2492 2005-01-17 16:05:59.420
12064n441 50 305 49 49 0 2005-01-17 19:29:20.187
12114n784 63 201 48 31 0 2005-01-18 04:07:43.733
12114n785 66 201 48 31 0 2005-01-18 05:07:36.203
12144n280 73 201 48 31 2194 2005-01-18 01:42:41.500
12144n296 71 201 48 31 2159 2005-01-18 02:54:35.670
12174n347 35 304 34 49 0 2005-01-17 18:34:32.547
12204n894 54 304 46 37 2808 2005-01-17 14:47:33.407
12204n894 54 304 46 37 2817 2005-01-17 14:47:51.610
12204n894 54 304 46 37 2828 2005-01-17 14:48:11.453
12204n894 54 304 46 37 2837 2005-01-17 14:48:24.860
12204n894 54 304 46 37 2848 2005-01-17 14:48:42.217
12204n894 54 304 46 37 2858 2005-01-17 14:48:59.517
12204n894 54 304 46 37 2868 2005-01-17 14:49:16.640
12204n894 54 304 46 37 2878 2005-01-17 14:49:37.327
12204n894 54 304 46 37 2888 2005-01-17 14:49:54.390
12204n894 54 304 46 37 2898 2005-01-17 14:50:11.627
12204n894 54 304 46 37 2908 2005-01-17 14:50:29.110
12204n894 54 304 46 37 2918 2005-01-17 14:50:46.267
12204n894 54 304 46 37 2928 2005-01-17 14:51:03.390
12204n894 54 304 46 37 2938 2005-01-17 14:51:20.250
12204n894 54 304 46 37 2948 2005-01-17 14:51:40.640
12204n894 54 304 46 37 2958 2005-01-17 14:51:57.907
12204n894 54 304 46 37 2968 2005-01-17 14:52:15.157
12204n894 54 304 46 37 2978 2005-01-17 14:52:31.843
12204n894 54 304 46 37 2988 2005-01-17 14:52:48.327
12204n894 54 304 46 37 2998 2005-01-17 14:53:05.577
12204n894 54 304 46 37 3009 2005-01-17 14:53:26.267
12204n894 54 304 46 37 3018 2005-01-17 14:53:43.517
12204n894 54 304 46 37 3029 2005-01-17 14:54:01.030
12204n894 54 304 46 37 3038 2005-01-17 14:54:18.233
12204n894 54 304 46 37 3049 2005-01-17 14:54:35.563
12204n894 54 304 46 37 3058 2005-01-17 14:54:53.170
12204n894 54 304 46 37 3069 2005-01-17 14:55:10.577
12204n894 54 304 46 37 3079 2005-01-17 14:55:28.030
12204n894 54 304 46 37 3089 2005-01-17 14:55:44.967
12224n198 36 304 57 49 692 2005-01-18 07:00:58.437
12234n502 63 201 48 31 2197 2005-01-18 04:07:13.407
12234n502 63 201 48 31 2207 2005-01-18 04:07:30.327
12234n502 63 201 48 31 2217 2005-01-18 04:07:47.000
12444c344 46 430 70 49 0 2005-01-18 13:08:01.547
12524c504 44 430 73 49 1881 2005-01-18 13:07:34.467
8424n114 46 301 90 49 1137 2005-01-18 06:17:53.657
8424n114 46 301 90 49 1147 2005-01-18 06:18:23.827
8424n115 46 301 90 49 1118 2005-01-18 08:48:33.733


I've tried playing with the code you gave to see if I could figure it out but I don't use TSQL enough to be that good.



Pat Russell
 
Upvote 0 Downvote
Well then I guess the next question is which footage and time stamp do you want, the minimum for each, maximum for each?

Tim
 
Upvote 0 Downvote
I would need the row with the earliest timestamp.

Pat Russell
 
Upvote 0 Downvote
How about this then:

select A.CoilID, A.[Entry Tower], A.[Min Tower Hgt], A.Grade, A.Guage, A.Width, B.Footage, B.[Time Stamp]
from
(SELECT b.CoilID, MIN(a.[Entry Tower]) AS [Min Tower Hgt], AVG(a.Grade) AS Grade,
AVG(a.Gauge) AS Gauge, AVG(a.Width) AS Width, MIN([Time Stamp]) AS [Min Time Stamp]
FROM dbo.BA_ALL_COILS a LEFT OUTER JOIN
dbo.Coil_List b ON a.CoilID = b.CoilID
WHERE (a.[Entry Tower] IS NOT NULL)
GROUP BY b.CoilID) A
LEFT JOIN
(SELECT CoilID, [Entry Tower], Footage, [Time Stamp]
FROM dbo.BA_ALL_COILS) B
ON A.CoilID=B.CoilID AND A.[Min Tower Hgt]=B.[Entry Tower] AND A.[Min Time Stamp]=B.[Time Stamp]
ORDER BY b.CoilID

There may be something more elegant out there, I don't know

Tim
 
Upvote 0 Downvote
Down to a single row per record but here's what I got.

CoilID Min Tower Hgt Grade Gauge Width Footage Time Stamp
--------------- ------------- ----------- ----------- ----------- ----------- ------------------------------------------------------
1035n743 49 304 62 39 NULL NULL
1045n033 38 304 46 49 NULL NULL
1045n034 34 304 46 49 NULL NULL
10534c191 32 430 73 49 NULL NULL
12114n784 63 201 48 31 NULL NULL
12114n785 66 201 48 31 NULL NULL
12144n280 73 201 48 31 NULL NULL
12144n296 71 201 48 31 NULL NULL
12224n198 36 304 57 49 NULL NULL
12234n502 63 201 48 31 NULL NULL
12444c344 25 430 70 49 NULL NULL
12524c504 44 430 73 49 NULL NULL
8424n114 46 301 90 49 NULL NULL
8424n115 46 301 90 49 NULL NULL
12034n084 48 304 46 49 NULL NULL
10514c158 34 430 73 49 0 2005-01-18 10:44:24.420
10554n036a 49 304 58 48 0 2005-01-18 08:49:00.687
1065n516 61 304 57 38 0 2005-01-17 20:44:27.907
11444c015 27 430 58 49 0 2005-01-18 14:09:01.530
12064n441 50 305 49 49 0 2005-01-17 19:29:20.187
12174n347 35 304 34 49 0 2005-01-17 18:34:32.547
12204n894 54 304 46 37 2808 2005-01-17 14:47:33.407



There should be a footage and time stamp for every row. I know the JOIN function is putting the NULL values in when it doesn't see a match but there is something there.

I really appreciate all the help.


Pat Russell
 
Upvote 0 Downvote
I removed the footage condition from the final JOIN statement and got the results I was looking for.

Code: 
SELECT 	A.CoilID, A.[Min Tower Hgt], A.Grade, A.Gauge, A.Width, B.Footage, B.[Time Stamp]
FROM
	(SELECT    	b.CoilID, MIN(a.[Entry Tower]) AS [Min Tower Hgt], AVG(a.Grade) AS Grade, 
       			AVG(a.Gauge) AS Gauge, AVG(a.Width) AS Width, MIN(a.[Time Stamp]) AS [Time]
 	FROM       	dbo.BA_ALL_COILS a LEFT OUTER JOIN
               		dbo.Coil_List b ON a.CoilID = b.CoilID
 	WHERE      	a.[Entry Tower] IS NOT NULL
 	GROUP BY   	b.CoilID) A
LEFT JOIN
	(SELECT CoilID, [Entry Tower], Footage, [Time Stamp]
 	FROM dbo.BA_ALL_COILS) B

  ON 	A.CoilID=B.CoilID AND A.[Time]=B.[Time Stamp]

ORDER BY   	b.CoilID

Results

CoilID Min Tower Hgt Grade Gauge Width Footage Time Stamp
--------------- ------------- ----------- ----------- ----------- ----------- ------------------------------------------------------
1035n743 49 304 62 39 1 2005-01-18 04:17:22.687
1045n033 38 304 46 49 0 2005-01-17 16:06:06.280
1045n034 34 304 46 49 0 2005-01-17 17:21:32.530
10514c158 34 430 73 49 0 2005-01-18 10:44:24.420
10534c191 32 430 73 49 0 2005-01-18 09:49:08.843
10554n036a 49 304 58 48 0 2005-01-18 08:49:00.687
1065n516 61 304 57 38 0 2005-01-17 20:44:27.907
11444c015 27 430 58 49 0 2005-01-18 14:09:01.530
12034n084 48 304 46 49 2 2005-01-17 14:53:19.140
12064n441 50 305 49 49 0 2005-01-17 19:29:20.187
12114n784 63 201 48 31 0 2005-01-17 22:00:10.937
12114n785 66 201 48 31 0 2005-01-17 23:15:07.063
12144n280 73 201 48 31 0 2005-01-18 00:29:49.733
12144n296 71 201 48 31 0 2005-01-18 01:42:48.453
12174n347 35 304 34 49 0 2005-01-17 18:34:32.547
12204n894 54 304 46 37 2808 2005-01-17 14:47:33.407
12224n198 36 304 57 49 0 2005-01-18 06:18:34.217
12234n502 63 201 48 31 9 2005-01-18 03:05:08.077
12444c344 25 430 70 49 0 2005-01-18 13:08:01.547
12524c504 44 430 73 49 0 2005-01-18 11:55:42.640
8424n114 46 301 90 49 2 2005-01-18 05:20:29.313
8424n115 46 301 90 49 0 2005-01-18 07:39:29.063

Thanks Tim! Have a star...



Pat Russell
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
884
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
1K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
2K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
400
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top