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] 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 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 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:
> 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 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
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...
> 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:
>> 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 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.
>>|||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...
> > 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:
> >
> >> 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 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.
> >>
> >>
> >>
>
>