query question

Hi I have 3 tables and am wondering if there is a way to do this. I would
like a qerry that returns data for a data item but will return only the # of
records for the event and not the destination. For example if the query
returns data for Data ID 4
I would want to see as the results.
Data name event destination
report recieved office
report intransit frontdesk
Even though there are 3 destinations I would like to only list the first 2
and base the number of records returned on the number of events. Thanks.
table1 event
****************************************
**
*pri key Count * foriegn key Data ID * event *
* 1 * 3 * arrived *
* 2 * 4 * recieved *
* 3 * 4 * intransit *
****************************************
**
table2 data item
*****************************
* Pri key Data ID * Data name *
* 3 * email *
* 4 * report *
*****************************
table3- destination-note no prim key
**********************************
* Data ID * destination *
* 4 * office *
* 4 * frontdesk *
* 4 * mailroom *
**********************************
Paul G
Software engineer.Hi Paul
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
and example data in a usable format, also posting the expected output from
the data provided would be helpful e.g.
CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] v
archar(30) )
INSERT INTO [event] ( [Count], [Data ID], [event] )
SELECT 1, 3, 'arrived'
UNION ALL SELECT 2, 4, 'recieved'
UNION ALL SELECT 3, 4, 'intransit'
CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30
) )
INSERT INTO [data item] ( [Data ID], [Data name] )
SELECT 3, 'email'
UNION ALL SELECT 4, 'report'
CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
INSERT INTO destination ( [Data ID], destination )
SELECT 4, 'office'
UNION ALL SELECT 4, 'frontdesk'
UNION ALL SELECT 4, 'mailroom'
There is no way to easily distinguish your destinations e.g
SELECT d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
--
report intransit office
report intransit frontdesk
report intransit mailroom
report recieved office
report recieved mailroom
report recieved frontdesk
(6 row(s) affected)
Limiting this to top 2
SELECT TOP 2 d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
--
report intransit office
report intransit frontdesk
(2 row(s) affected)
You could give destination alphabetical rank such as
SELECT f.[Data ID], f.destination,
( SELECT COUNT(*) FROM destination b WHERE f..destination <
b.destination )+1 AS RANK
FROM destination f
This may then allow you to pick different destinations by joining to the
event count (you may have to rank these to get similar number ranges.
SELECT TOP 2 d.[Data name], e.[Event], g.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN ( SELECT f.[Data ID],
f.destination,
( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
AS RANK
FROM destination f
) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
ORDER BY e.[Count] DESC
Data name Event destination
-- -- --
--
report intransit frontdesk
report recieved mailroom
(2 row(s) affected)
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> Hi I have 3 tables and am wondering if there is a way to do this. I would
> like a qerry that returns data for a data item but will return only the #
> of
> records for the event and not the destination. For example if the query
> returns data for Data ID 4
> I would want to see as the results.
> Data name event destination
> report recieved office
> report intransit frontdesk
> Even though there are 3 destinations I would like to only list the first 2
> and base the number of records returned on the number of events. Thanks.
> table1 event
> ****************************************
**
> *pri key Count * foriegn key Data ID * event *
> * 1 * 3 * arrived
> *
> * 2 * 4 * recieved
> *
> * 3 * 4 * intransit
> *
> ****************************************
**
> table2 data item
> *****************************
> * Pri key Data ID * Data name *
> * 3 * email *
> * 4 * report *
> *****************************
> table3- destination-note no prim key
> **********************************
> * Data ID * destination *
> * 4 * office *
> * 4 * frontdesk *
> * 4 * mailroom *
> **********************************
>
> --
> Paul G
> Software engineer.|||hi thanks for the response. Does seem quite useful to use DDL, will do this
in the future. Unfortunately I will net be able to use a constant for the #
of records returned as the number of records returned for each data item wil
l
not be the destinations but the number of events for each data item. Guess
I
will probably use 2 queries the first one getting the # of events for each
data item and the second returning the data name, event and destination and
using the TOP or ROWCOUNT to limit the # of records returned. Thanks again,
Paul.
--
Paul G
Software engineer.
"John Bell" wrote:

> Hi Paul
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> and example data in a usable format, also posting the expected output from
> the data provided would be helpful e.g.
> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event]
varchar(30) )
> INSERT INTO [event] ( [Count], [Data ID], [event] )
> SELECT 1, 3, 'arrived'
> UNION ALL SELECT 2, 4, 'recieved'
> UNION ALL SELECT 3, 4, 'intransit'
> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(
30) )
> INSERT INTO [data item] ( [Data ID], [Data name] )
> SELECT 3, 'email'
> UNION ALL SELECT 4, 'report'
>
> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> INSERT INTO destination ( [Data ID], destination )
> SELECT 4, 'office'
> UNION ALL SELECT 4, 'frontdesk'
> UNION ALL SELECT 4, 'mailroom'
>
> There is no way to easily distinguish your destinations e.g
> SELECT d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
--
> report intransit office
> report intransit frontdesk
> report intransit mailroom
> report recieved office
> report recieved mailroom
> report recieved frontdesk
> (6 row(s) affected)
>
> Limiting this to top 2
> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
--
> report intransit office
> report intransit frontdesk
> (2 row(s) affected)
> You could give destination alphabetical rank such as
>
> SELECT f.[Data ID], f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> b.destination )+1 AS RANK
> FROM destination f
> This may then allow you to pick different destinations by joining to the
> event count (you may have to rank these to get similar number ranges.
> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN ( SELECT f.[Data ID],
> f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )
+1
> AS RANK
> FROM destination f
> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> ORDER BY e.[Count] DESC
> Data name Event destination
> -- -- --
--
> report intransit frontdesk
> report recieved mailroom
> (2 row(s) affected)
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
>
>|||Hi
SQL 2005 allows top to take a variable, but that may not be any use to
yourself. You may be able to use a having clause if you can formulate the
number of rows required and then use the ranking as the tested value.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...[vbcol=seagreen]
> hi thanks for the response. Does seem quite useful to use DDL, will do
> this
> in the future. Unfortunately I will net be able to use a constant for the
> #
> of records returned as the number of records returned for each data item
> will
> not be the destinations but the number of events for each data item.
> Guess I
> will probably use 2 queries the first one getting the # of events for each
> data item and the second returning the data name, event and destination
> and
> using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> again,
> Paul.
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
>|||ok thanks for the additional information. I guess I could use the ROWCOUNT
which does take a variable. Hopefully we will upgrade to SQL 2005 but it
will be awhile.
--
Paul G
Software engineer.
"John Bell" wrote:

> Hi
> SQL 2005 allows top to take a variable, but that may not be any use to
> yourself. You may be able to use a having clause if you can formulate the
> number of rows required and then use the ranking as the tested value.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
>
>