Nested select problem? or just me :)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nested select problem? or just me :)

Author

Topic

cardona
Starting Member

10 Posts

Posted - 2006-11-13 : 02:09:07

I have two tables

tblMain & tblItinerary

I want to be able to select the top 1 of each tblItinerary where the PK = FK

so if "A. User" has 3 days (each row is a day) for the first trip and 4 days for another trip in the itinerary table, return the first day of each trip.

Any help or pointers will be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 02:15:46

select tblmain.user, min(tblitenerary) minrow
from tblmain inner join tblitenerary on tblitenerary.pk = tblmain.pk
group by tblmain.user

Peter Larsson
Helsingborg, Sweden

cardona
Starting Member

10 Posts

Posted - 2006-11-13 : 20:38:57

Thanks very much for your reply!

Of course your example worked, but I've now tried playing with it to add more colums from both tables into the mix, and am getting varied results...

Here is my mash up at the moment:

SELECT tblMain.RequestingStaff, tblMain.CurrentStatus, tblMain.TANumber, 
   min(tblItinerary.DateOfTravel) dateTravel, 
   min(tblItinerary.ItineraryTANumber) ItineraryTA,
   min(tblItinerary.DepartPlace) dptPlace, 
   min(tblItinerary.ArrivePlace) arrPlace
FROM tblMain INNER JOIN tblItinerary 
ON tblitinerary.ItineraryTANumber = tblMain.TANumber 
   AND (tblMain.CurrentStatus=6 OR tblMain.CurrentStatus=8 OR tblMain.CurrentStatus=9) 
   AND tblMain.RequestingStaff ='the user'
GROUP BY tblMain.RequestingStaff , tblMain.CurrentStatus, tblMain.TANumber

The problems I am now having is with the DepartPlace & ArrivePlace; if the user has 3 days travel from 'asp'(depart place) to 'adl'(arrive place) the table may look like this:

TANumber  Datetravel  Depart  Arrive  + other columns
  1       14/11/2006   asp     adl     ...
  1       15/11/2006   adl     adl     ...
  1       16/11/2006   adl     asp     ...

  2       20/11/2006   asp     dwn     ...
  2       21/11/2006   dwn     asp     ...

If I use the 'min' function for depart I'll get 'adl' and for arrive I'll get 'adl', what I need returned is just the top row of each trip i.e. 14/11/2006 asp adl & 20/11/2006 asp dwn

Thanks again in advance for any help/tips/pointers

Cheers!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-11-13 : 21:52:08

You'll need to get the PK from the min record and then do a join back to that entire row so you can get the other columns. Of course you might need to handle when the min row returns multiple records unless there is a unique constraint on it.

cardona
Starting Member

10 Posts

Posted - 2006-11-13 : 23:12:04

Thanks Lozt,

I understand what your saying; I need to get 'ItineraryID' (PK on tblItinerary) of the 'min' fields. Then join it back .... I'm now lost :) how do I implement that? Do I need to do two Select statements? or does it get incorporated some how into what I've already put together?

Again thanks for the replies!

cardona
Starting Member

10 Posts

Posted - 2006-11-15 : 18:46:52

*BUMP!*

This is where I am up to, with the SQL statement:

SELECT tblMain.RequestingStaff, tblMain.CurrentStatus, tblMain.TANumber, 
   min(tblItinerary.DateOfTravel) datetravel, 
   min(tblItinerary.itineraryTAnumber) ItineraryTA,
   min(tblItinerary.DepartPlace) dptPlace, 
   min(tblItinerary.ArrivePlace) arrPlace
FROM tblMain INNER JOIN tblItinerary 
ON tblitinerary.ItineraryTANumber = tblMain.TANumber 
AND tblItinerary.ItineraryID = (SELECT TOP 1 ItineraryID 
                                   FROM tblItinerary INNER JOIN tblMain
				   ON ItineraryTANumber = tblMain.TANumber  
				   WHERE requestingStaff = 'the user' 
				   AND (tblMain.CurrentStatus=6 OR tblMain.CurrentStatus=8 OR tblMain.CurrentStatus=9))
GROUP BY tblMain.RequestingStaff , tblMain.CurrentStatus, tblMain.TANumber

This returns the right values for dptPlace and arrPlace, but because of the "TOP 1" it is only returning one row, and if I remove "TOP 1" it causes an error: "Subquery returned more than 1 value...[etc]"

Any further help/tips/pointers will be appreciated.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 21:22:36

It doesn't look like you need that subquery, what happens if you try this?

SELECT tblMain.RequestingStaff, tblMain.CurrentStatus, tblMain.TANumber, 
   min(tblItinerary.DateOfTravel) datetravel, 
   min(tblItinerary.itineraryTAnumber) ItineraryTA,
   min(tblItinerary.DepartPlace) dptPlace, 
   min(tblItinerary.ArrivePlace) arrPlace
FROM tblMain 
INNER JOIN tblItinerary ON tblitinerary.ItineraryTANumber = tblMain.TANumber 
WHERE requestingStaff = 'the user' 
    AND (tblMain.CurrentStatus=6 OR tblMain.CurrentStatus=8 OR tblMain.CurrentStatus=9)
GROUP BY tblMain.RequestingStaff , tblMain.CurrentStatus, tblMain.TANumber

cardona
Starting Member

10 Posts

Posted - 2006-11-16 : 02:00:22

hey snSQL, I have tried that before (see my second post)

the results are as follows (taken from my second post):

quote:
... if the user has 3 days travel from 'asp'(depart place) to 'adl'(arrive place) the table may look like this:

TANumber Datetravel Depart Arrive + other columns
1 14/11/2006 asp adl ...
1 15/11/2006 adl adl ...
1 16/11/2006 adl asp ...

2 20/11/2006 asp dwn ...
2 21/11/2006 dwn asp ...

If I use the 'min' function for depart I'll get 'adl' and for arrive I'll get 'adl', what I need returned is just the top row of each trip i.e. 14/11/2006 asp adl & 20/11/2006 asp dwn ...

i.e. its taking the min (alphabetical order) of all the depart/arrive places

cardona
Starting Member

10 Posts

Posted - 2006-11-16 : 02:01:06

hey snSQL, I have tried that before (see my second post)

the results are as follows (taken from my second post):

quote:
... if the user has 3 days travel from 'asp'(depart place) to 'adl'(arrive place) the table may look like this:

TANumber Datetravel Depart Arrive + other columns
1 14/11/2006 asp adl ...
1 15/11/2006 adl adl ...
1 16/11/2006 adl asp ...

2 20/11/2006 asp dwn ...
2 21/11/2006 dwn asp ...

If I use the 'min' function for depart I'll get 'adl' and for arrive I'll get 'adl', what I need returned is just the top row of each trip i.e. 14/11/2006 asp adl & 20/11/2006 asp dwn ...

i.e. its taking the min (alphabetical order) of all the depart/arrive places

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 02:36:47

Did you try this?

SELECT		tblMain.RequestingStaff,
		tblMain.CurrentStatus,
		tblMain.TANumber, 
   		tblItinerary.DateOfTravel dateTravel, 
   		tblItinerary.ItineraryTANumber ItineraryTA,
   		tblItinerary.DepartPlace dptPlace, 
   		tblItinerary.ArrivePlace arrPlace
FROM		tblMain
INNER JOIN	tblItinerary ON tblitinerary.ItineraryTANumber = tblMain.TANumber 
INNER JOIN	(
			SELECT		MIN(ItineraryTANumber) TANumber
			FROM		tblItinerary
			WHERE		CurrentStatus IN (6, 8, 9)
					AND RequestingStaff = 'the user'
		) q ON q.TANumber = tblMain.Number

Peter Larsson
Helsingborg, Sweden

cardona
Starting Member

10 Posts

Posted - 2006-11-16 : 19:37:39

I hadn't tried that, but...

The results are returning the user's 1st lot of travel, not the 1st line of each travel.
So, if the user's travel was:
TANumber Datetravel Depart Arrive + other columns
1 14/11/2006 asp adl ...
1 15/11/2006 adl adl ...
1 16/11/2006 adl asp ...

2 20/11/2006 asp dwn ...
2 21/11/2006 dwn asp ...

I would like returned:
TANumber Datetravel Depart Arrive + other columns
1 14/11/2006 asp adl ...

2 20/11/2006 asp dwn ...

The code now (bold words I have added to Peter's code):

SELECT		tblMain.RequestingStaff,
		tblMain.CurrentStatus,
		tblMain.TANumber, 
   		tblItinerary.DateOfTravel dateTravel, 
   		tblItinerary.ItineraryTANumber ItineraryTA,
   		tblItinerary.DepartPlace dptPlace, 
   		tblItinerary.ArrivePlace arrPlace
FROM		tblMain
INNER JOIN	tblItinerary ON tblitinerary.ItineraryTANumber = tblMain.TANumber 
INNER JOIN	(
			SELECT		MIN(ItineraryTANumber) TANumber
			FROM		tblItinerary,tblMain
			WHERE		tblMain.CurrentStatus IN (6, 8, 9)
					AND tblMain.RequestingStaff = 'the user'
		) q ON q.TANumber = tblMain.TANumber

cardona
Starting Member

10 Posts

Posted - 2006-11-21 : 18:42:11

*BUMP!*

Sorry people, I still don't have an answer :(

Any help will be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 03:13:35

[code]select * from calendar

SELECT tblMain.RequestingStaff,
tblMain.CurrentStatus,
tblMain.TANumber,
tblItinerary.DateOfTravel dateTravel,
tblItinerary.ItineraryTANumber ItineraryTA,
tblItinerary.DepartPlace dptPlace,
tblItinerary.ArrivePlace arrPlace
FROM tblMain
INNER JOIN tblItinerary ON tblitinerary.RequestingStaff = tblMain.RequestingStaff
AND tblitinerary.ItineraryTANumber = tblMain.TANumber
INNER JOIN (
SELECT RequestingStaff,
ItineraryTANumber,
MIN(DateOfTravel) dot
FROM tblItinerary
WHERE CurrentStatus IN (6, 8, 9)
GROUP BY RequestingStaff,
ItineraryTANumber
) q ON q.RequestingStaff = tblitinerary.RequestingStaff
AND q.ItineraryTANumber = tblitinerary.ItineraryTANumber
AND q.dot = tblItinerary.DateOfTravel[/code]

Peter Larsson
Helsingborg, Sweden

cardona
Starting Member

10 Posts

Posted - 2006-11-22 : 20:34:59

Thanks Peso for the reply, I know I'm a ding bat for not listing what table has what fields etc. :) but there is no tblItinerary.RequestingStaff field.

I have figured it out though...not too sure how efficient it is.
Again, maybe I didn't supply enough detail at the start, but the SQL is part of an asp.net page, to fill a datagrid.
Firstly,

SELECT DISTINCT ItineraryTANumber FROM tblItinerary INNER JOIN tblMain
      ON ItineraryTANumber=tblMain.TANumber
      WHERE requestingStaff='A User' AND CurrentStatus IN (6,8,9)

Gets each distinct ItineraryTANumber.
Added the results to an array (strTAs())
Then looping through the array run this on each loop:
(Where "j" is the loop counter.)

SELECT TOP 1 ItineraryID FROM tblItinerary INNER JOIN tblMain 
      ON ItineraryTANumber=" & strTAs(j)

For each TAnumber returned from the first lot of results, select the top 1 ItineraryID of each of the user's travel, and read those results into another array (strItineraryID())
Then looping through the strItineraryID() array run this on each loop:
(Where "l" (letter [L]) is the loop counter.)

SELECT RequestingStaff,TotalTA,tblMain.TANumber,tblItinerary.DateOfTravel, 
             tblItinerary.DepartPlace, tblItinerary.ArrivePlace, tblMain.CurrentStatus 
      FROM tblMain INNER JOIN tblItinerary 
      ON ItineraryTANumber=TANumber AND tblitinerary.ItineraryID =" & strItineraryID(l)

retrieves the required columns based on ItineraryID.
Reading these results into a dataset object and binding it to the datagrid, Whew!

I would appreciate any tips/pointers on the SQL and/or logic that I have used i.e. do I need the "INNER JOIN" in the second Select statement?

cardona
Starting Member

10 Posts

Posted - 2006-11-22 : 21:02:50

Peter, I played around with your code, and I have gotten it to work!
again I have bolded what I have changed.

SELECT		tblMain.RequestingStaff,
		tblMain.CurrentStatus,
		tblMain.TANumber, 
   		tblItinerary.DateOfTravel dateTravel, 
   		tblItinerary.ItineraryTANumber ItineraryTA,
   		tblItinerary.DepartPlace dptPlace, 
   		tblItinerary.ArrivePlace arrPlace
FROM		tblMain
INNER JOIN	tblItinerary ON tblmain.RequestingStaff = 'The User' AND --tblMain.RequestingStaff AND
		tblitinerary.ItineraryTANumber = tblMain.TANumber AND tblMain.CurrentSTatus in (6,8,9)
INNER JOIN	(
			SELECT		RequestingStaff,
					ItineraryTANumber,
					MIN(DateOfTravel) dot
			FROM		tblItinerary,tblmain
			WHERE		CurrentStatus IN (6, 8, 9)
			GROUP BY	RequestingStaff,
					ItineraryTANumber
		) q ON q.RequestingStaff = tblmain.RequestingStaff
			AND q.ItineraryTANumber = tblitinerary.ItineraryTANumber
			AND q.dot = tblItinerary.DateOfTravel

Thanks heaps for all your help everyone!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources