Calling another record into this record

**Hugo Kornelis** · Jun 15 '06, 09:35 PM

Re: Calling another record into this record

On 15 Jun 2006 05:47:43 -0700, pltaylor3@gmail .com wrote:
[color=blue]
>This has got to be easier than I am making it out to be. I have a
>query that calls on two tables linked by unique ids. I would like to
>have the previous outing's # of laps brought into the current outing.
>An example of what the view follows
>SELECT TOP 100 PERCENT [Main Outing].OutingNumber, [Main
>Outing].OutingID, COUNT(dbo.vwLap s.LapTime) AS [# of laps]
>FROM dbo.vwOutings [Main Outing] INNER JOIN
> dbo.vwLaps ON [Main Outing].OutingID =
>dbo.vwLaps.Out ingID
>GROUP BY [Main Outing].OutingNumber, [Main Outing].OutingID
>ORDER BY [Main Outing].OutingNumber DESC[/color]
(snip)

Hi pltaylor3,

First, why do you have a "TOP 100 PERCENT" in your query? Since 100
percent equals all rows, you're actually requestin alll rows - which is
the default if you don't specify a TOP clause at all! Get rid of it.

Next, why do you choose aliases that require you to escape the names?
Are you really that fond of typing lots of [ and ] chartacters?

Third, you might also reconsider the "vw" prefixes on your views. In SQL
Server, objects should be named for what they represent, not for how
they happen to be implemented.

Last, here's an answer to your question:

SELECT mo.OutingNumber , mo.OutingID,
COUNT(l.LapTime ) AS "# of laps",
(SELECT COUNT(l2.LapTim e)
FROM dbo.Outings AS mo2
INNER JOIN dbo.Laps AS l2
ON mo2.OutingID = l2.OutingID
WHERE mo2.OutingNumbe r = mo.OutingNumber - 1) AS "# of
laps previousouting"
FROM dbo.Outings AS mo -- [Main Outing]
INNER JOIN dbo.Laps AS l
ON mo.OutingID = l.OutingID
GROUP BY mo.OutingNumber , mo.OutingID
ORDER BY mo.OutingNumber DESC

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

**Erland Sommarskog** · Jun 15 '06, 10:05 PM

Re: Calling another record into this record

(pltaylor3@gmai l.com) writes:[color=blue]
> This has got to be easier than I am making it out to be. I have a
> query that calls on two tables linked by unique ids. I would like to
> have the previous outing's # of laps brought into the current outing.
> An example of what the view follows
> SELECT TOP 100 PERCENT [Main Outing].OutingNumber, [Main
> Outing].OutingID, COUNT(dbo.vwLap s.LapTime) AS [# of laps]
> FROM dbo.vwOutings [Main Outing] INNER JOIN
> dbo.vwLaps ON [Main Outing].OutingID =
> dbo.vwLaps.Outi ngID
> GROUP BY [Main Outing].OutingNumber, [Main Outing].OutingID
> ORDER BY [Main Outing].OutingNumber DESC[/color]

First remove the TOP 100 PERCENT and ORDER BY from the view. They mean
nothing. In SQL 2000 it may seem that a SELECT from the view without
an ORDER BY always return rows according to the ORDER BY in the view,
but that is mere chance. In SQL 2005, this does not happen.

The only way to get sorted output from a query in SQL (no matter the
engine) is to have an ORDER BY clause in the SELECT statment that
outputs the data to the client.
[color=blue]
> returning
> Outing Number Outing ID
> # of laps
> 6 {87C29BDC-A2D1-4606-A87B-D456F702BFCE} 99
> 5 {203319A9-4B2F-406A-9454-0DD3D5F86211} 37
> 4 {B6306DF5-5BF0-4456-8E06-0CD2ED8B1874} 54
> 3 {A70643F3-28C2-47CC-B623-4BD6904C2825} 95
> 2 {EB409D12-C79C-4D96-BE2A-0BD49C5BE69D} 80
> 1 {75D7793D-3239-4544-BACC-84EB564C8638} 36
> what I woud like is
> Outing Number Outing ID
> # of laps # of laps previousouting
> 6 {87C29BDC-A2D1-4606-A87B-D456F702BFCE} 99 37
> 5 {203319A9-4B2F-406A-9454-0DD3D5F86211} 37 54
> 4 {B6306DF5-5BF0-4456-8E06-0CD2ED8B1874} 54 95
> 3 {A70643F3-28C2-47CC-B623-4BD6904C2825} 95 80
> 2 {EB409D12-C79C-4D96-BE2A-0BD49C5BE69D} 80 36
> 1 {75D7793D-3239-4544-BACC-84EB564C8638} 36 0
> sorry if this is a silly question I'm a little new to this thanks for
> any help[/color]

SELECT MO.OutingNumber , MO.OutingID,
COUNT(L.LapTime ) AS [# of laps],
COUNT(L2.LapTim e) AS [# of prev laps],
FROM dbo.vwOutings MO
JOIN dbo.vwLaps L ON MO.OutingID = dbo.vwLaps.Outi ngID
LEFT JOIN (dbo.vwOutings MO2
JOIN dbo.vwLaps L2 ON MO2.OutingID = L2.OutingID)
ON MO.OutingNumber = MO2.OutingNumbe r + 1
GROUP BY MO.OutingNumber , MO.OutingID

Bur this is a complete guess about what your tables and data are like. If
it does not work out, please post:

o CREATE TABLE statements for your tables.
o INSERT statements with the sample data.

Some narrative how the tables are organised would help too.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Calling another record into this record

Calling another record into this record

Comment

Comment