Help with select statement

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblAsset]
GO
CREATE TABLE [dbo].[tblAsset] (
PK[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL ,
[AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
----
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblEmployee]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployee]
GO
CREATE TABLE [dbo].[tblEmployee] (
PK[EmpID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[EmployeeID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
---
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblEmployeeAsset]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployeeAsset]
GO
CREATE TABLE [dbo].[tblEmployeeAsset] (
PK[EmpID] [int] NOT NULL ,
PK[AssetID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
---
tblAsset
AssetID AssetName
1 RyanPC
--
tblEMployee
EmpID EmployeeID
1 Ryan
--
tblemployeeasset
EmpID AssetID
1 1
I want a select statement that gives me this result...
Employee Asset
Ryan RyanPC"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
> I want a select statement that gives me this result...
> Employee Asset
> Ryan RyanPC
>
SELECT E.EmployeeID, A.AssetName
FROM tblAsset AS A
JOIN tblEmployeeAsset AS T
ON A.AssetID = T.AssetID
JOIN tblEmployee AS E
ON T.EmpID = E.EmpID ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This works exactly the way I want in the querly analyzer thank you.
However, I am calling this select statement from within some C# code
via a dataadapter, when I run the query I get an error, any idea why?
SqlDataAdapter da = new SqlDataAdapter("select e.EmployeeID,e.EmpID,
a.AssetName, a.AssetId from tblAsset a join tblEmployeeAsset t on
a.assetID = t.AssetID join tblEmployee e on t.EmpID = E.EmpID", str);
DataSet ds = new DataSet();
da.Fill(ds);
dataGrid1.DataSource = ds;
The connection exists, and every other thing works, but why not this?
If this is beyond your scope i will promptly post in a C# forum...
David Portas wrote:
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
>
> SELECT E.EmployeeID, A.AssetName
> FROM tblAsset AS A
> JOIN tblEmployeeAsset AS T
> ON A.AssetID = T.AssetID
> JOIN tblEmployee AS E
> ON T.EmpID = E.EmpID ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --

Help with SELECT please

I need to select 2 rows from a table for every SSN. DDL for the table:

CREATE TABLE [dbo].[tblResidentRotations] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResidentProgram] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RotationID] [int] NULL ,
[MonthName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RotationLocationID] [int] NULL ,
[CallLocationID] [int] NULL ,
[IMClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IMClinicDateLast] [datetime] NULL ,
[IMClinicDateFirst] [datetime] NULL ,
[PedsClinicDay] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClinicScheduleComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastFirstComments] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PGYLevel] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AcademicYear] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
"Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
data in mm/dd/yy format. I need to pull IMClinicDay for the date given
in IMClinicDateFirst. For example, if my data looks like this:

SSN: 999999999
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

then I need to pull what the IMClinicDay would be for this SSN in
September.

Thanks for any help and advice.Stop using that silly redundant "tbl-" prefix; If nobody told you
yet, SQL only has one data structure. Then you might want to read a
basic book on data modeling - you always name a data element for what
it is, not for how it is stored, where it is used, etc.

Next, we need keys to have proper tables. An IDENTITY is **never** a
key by definition. SSN is never VARCHAR() but it is fixed length, so
all you did was invite a loss of data integrity. Ditto when you pulled
oversize numbers out of the air for the other column sizes. Since there
are no non-NOT NULL columns, you cannot ever have a key!! Think about
VARCHAR(1) and what it means.

Do you know of a month name that is CHAR(15)? Why are you using
strings for temporal data in SQL? Why are you using vague strings like
"Monday PM" for temporal data? Why did you violate ISO-8601 formats
for the bad dates?

What does the resident's academic year have to do with rotations?
The whole mess looks denormalized. Just based on a few decades of
prior experience, I would guess this ought o reduce down to something
like this:

CREATE TABLE ResidentRotations
(ssn VARCHAR(9) NOT NULL
REFERENCES Residents(ssn),
resident_program VARCHAR(20) NOT NULL,
rotation_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
rotation_start_time DATETIME NOT NULL,
rotation_end_time DATETIME NOT NULL,
CHECK (rotation_start_time < rotation_end_time),
call_loc INTEGER NOT NULL
REFERENCES LocationsCodes(loc_nbr),
PRIMARY KEY (ssn, rotation_start_time));

>> I need to pull what the IMClinicDay would be for this SSN in September. <<

You need to use a Calendar table and insert the scheduled shifts in
advance for the known duration. You can take care of holidays,
re-scheduling, etc. with this approach.

You might want to read Rick Snodgrass' s book on Temporal queries in
SQL after you get thru a basic data modeling book and a few ISO
standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> data in mm/dd/yy format.

No, they are declared as datetime, which means that they are in a
binary format. If you say

SELECT * FROM tbl WHERE datecol = '07/01/05'

You could get rows from from 2007-01-05, 2005-01-07 or any other
of the six possible permutations, depending on the current settings.
On the other hand:

SELECT * FROM tbl WHERE datecol = '20070105'

will always give the same set of data.

OK, so that is not what you asked about, but since you had an apparent
misunderstanding about datetime, I figured I should point it out.

> I need to pull IMClinicDay for the date given
> in IMClinicDateFirst. For example, if my data looks like this:
> SSN: 999999999
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> then I need to pull what the IMClinicDay would be for this SSN in
> September.

If I understand this correctly, you should have a look at the datename()
function in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here's a sample of the data I have:

SSN: 999999999 (first row)
MonthName: July
IMClinicDay: Monday PM
IMClinicDateLast: 07/01/05
IMClinicDateFirst: 09/01/05

SSN: 999999999 (nth row)
MonthName: September
IMClinicDay: Wednesday PM
IMClinicDateLast: 09/01/05
IMClinicDateFirst: 10/01/05

With a SELECT statement, I want to return all of the first row and only
the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
in the first row to get this data.

Thanks for any help.

Erland Sommarskog wrote:
> (manning_news@.hotmail.com) writes:
> > For a given MonthName, I pull SSN, IMClinicDay, IMClinicDateLast, and
> > IMClinicDateFirst. IMClinicDay contains data like "Monday AM",
> > "Tuesday PM". IMClinicDateLast and IMClinicDateFirst contains date
> > data in mm/dd/yy format.
> No, they are declared as datetime, which means that they are in a
> binary format. If you say
> SELECT * FROM tbl WHERE datecol = '07/01/05'
> You could get rows from from 2007-01-05, 2005-01-07 or any other
> of the six possible permutations, depending on the current settings.
> On the other hand:
> SELECT * FROM tbl WHERE datecol = '20070105'
> will always give the same set of data.
> OK, so that is not what you asked about, but since you had an apparent
> misunderstanding about datetime, I figured I should point it out.
> > I need to pull IMClinicDay for the date given
> > in IMClinicDateFirst. For example, if my data looks like this:
> > SSN: 999999999
> > MonthName: July
> > IMClinicDay: Monday PM
> > IMClinicDateLast: 07/01/05
> > IMClinicDateFirst: 09/01/05
> > then I need to pull what the IMClinicDay would be for this SSN in
> > September.
> If I understand this correctly, you should have a look at the datename()
> function in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I just want to know if I can get to the row I want. If it's impossible
with the structure I have then just say so.

--CELKO-- wrote:
> Stop using that silly redundant "tbl-" prefix; If nobody told you
> yet, SQL only has one data structure. Then you might want to read a
> basic book on data modeling - you always name a data element for what
> it is, not for how it is stored, where it is used, etc.
> Next, we need keys to have proper tables. An IDENTITY is **never** a
> key by definition. SSN is never VARCHAR() but it is fixed length, so
> all you did was invite a loss of data integrity. Ditto when you pulled
> oversize numbers out of the air for the other column sizes. Since there
> are no non-NOT NULL columns, you cannot ever have a key!! Think about
> VARCHAR(1) and what it means.
> Do you know of a month name that is CHAR(15)? Why are you using
> strings for temporal data in SQL? Why are you using vague strings like
> "Monday PM" for temporal data? Why did you violate ISO-8601 formats
> for the bad dates?
> What does the resident's academic year have to do with rotations?
> The whole mess looks denormalized. Just based on a few decades of
> prior experience, I would guess this ought o reduce down to something
> like this:
> CREATE TABLE ResidentRotations
> (ssn VARCHAR(9) NOT NULL
> REFERENCES Residents(ssn),
> resident_program VARCHAR(20) NOT NULL,
> rotation_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> rotation_start_time DATETIME NOT NULL,
> rotation_end_time DATETIME NOT NULL,
> CHECK (rotation_start_time < rotation_end_time),
> call_loc INTEGER NOT NULL
> REFERENCES LocationsCodes(loc_nbr),
> PRIMARY KEY (ssn, rotation_start_time));
> >> I need to pull what the IMClinicDay would be for this SSN in September. <<
> You need to use a Calendar table and insert the scheduled shifts in
> advance for the known duration. You can take care of holidays,
> re-scheduling, etc. with this approach.
> You might want to read Rick Snodgrass' s book on Temporal queries in
> SQL after you get thru a basic data modeling book and a few ISO
> standards. Pretty much everything you did was fundamentally wrong.|||(manning_news@.hotmail.com) writes:
> Here's a sample of the data I have:
> SSN: 999999999 (first row)
> MonthName: July
> IMClinicDay: Monday PM
> IMClinicDateLast: 07/01/05
> IMClinicDateFirst: 09/01/05
> SSN: 999999999 (nth row)
> MonthName: September
> IMClinicDay: Wednesday PM
> IMClinicDateLast: 09/01/05
> IMClinicDateFirst: 10/01/05
> With a SELECT statement, I want to return all of the first row and only
> the IMClinicDay (Wednesday PM) of the nth row using IMClinicDateFirst
> in the first row to get this data.

Since your table definition did not include any information about keys,
I cannot be sure that this query works:

SELECT a.SSN, a.ResidentProgram, ..., b.IMClinicDay
FROM tblResidentRotations a
LEFT JOIN b tblResidentRotations
ON a.SSN = b.SSN
AND a.IMClinicDateLast = b.IMClinicDateFirst
WHERE a.IMClinicDateFirst >= @.yearmonth + '01' AND
a.IMClincDateFirst < dateadd(MONTH, 1, @.yearmonth + '01')

I assume that @.yearmonth holds the month you are looking for on the
form YYYYMM.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help with query

Hello,

I have two queries

SELECT TOP (100) PERCENT Player_name, SUM([Top-ups]) AS TOPUPS
FROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value,
dbo.Events.Top_up * dbo.Event_data.Transaction_value AS [Top-ups]
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 2)) AS Topups
GROUP BY Player_name
ORDER BY TOPUPS DESC

and

SELECT TOP (100) PERCENT Player_name, SUM(Expr1) AS Expr1
FROM (SELECT TOP (100) PERCENT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys,
dbo.Event_data.Transaction_value * dbo.Events.Rebuys AS Expr1
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 3)
ORDER BY Expr1 DESC) AS REBUYS
GROUP BY Player_name
ORDER BY Expr1 DESC

Can I combine these into one query to get the Player_name result, rebuys and top ups?

Sure, pop a UNION between the queries.

SELECT TOP (100) PERCENT Player_name, SUM([Top-ups]) AS TOPUPS
FROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value,
dbo.Events.Top_up * dbo.Event_data.Transaction_value AS [Top-ups]
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 2)) AS Topups
GROUP BY Player_name
ORDER BY TOPUPS DESC

UNION

SELECT TOP (100) PERCENT Player_name, SUM(Expr1) AS Expr1
FROM (SELECT TOP (100) PERCENT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys,
dbo.Event_data.Transaction_value * dbo.Events.Rebuys AS Expr1
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 3)
ORDER BY Expr1 DESC) AS REBUYS
GROUP BY Player_name
ORDER BY Expr1 DESC

|||

Thanks for replying, but I am getting an "incorrect syntax near the keyword Union"

am I missing a comma or something?

|||

You should not use ORDER BY for subqueries.

Also if you are selecting all the rows why bother with top 100 PERCENT.

SELECT Player_name,SUM([Top-ups])AS TOPUPSFROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value, dbo.Events.Top_up * dbo.Event_data.Transaction_valueAS [Top-ups]FROM dbo.Event_dataINNERJOIN dbo.EventsON dbo.Event_data.Event_id = dbo.Events.Event_idINNERJOIN dbo.PlayersON dbo.Event_data.Player_id = dbo.Players.Player_idWHERE (dbo.Event_data.Transaction_type = 2))AS TopupsGROUP BY Player_name--ORDER BY TOPUPS DESCUNION SELECT Player_name,SUM(Expr1)AS TOPUPSFROM (SELECT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys, dbo.Event_data.Transaction_value * dbo.Events.RebuysAS Expr1FROM dbo.Event_dataINNERJOIN dbo.EventsON dbo.Event_data.Event_id = dbo.Events.Event_idINNERJOIN dbo.PlayersON dbo.Event_data.Player_id = dbo.Players.Player_idWHERE (dbo.Event_data.Transaction_type = 3) )AS REBUYSGROUP BY Player_name--ORDER BY TOPUPS DESC

|||

OK Its working but I am only getting two colums Player_name and TOPUPS, when I really want Three coloums Player_name TOPUPS and REBUYS.

I am getting the player name twice in the player column with the rebuy total under the TOPUP column.

Any Ideas?

SELECT

Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY Player_name

UNION

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

what I am getting

Player_nameTOPUPSJohnSmith100John Smith400John Doe3600John Doe3700

What I want

Player_nameTOPUPSBUYINJohnSmith100400John Doe36003700

Thanks again.

|||

You could then insert the result of each of the queries into a table varible and do a SELECT from it.

declare @.ttable( player_namevarchar(100), topupsint, buyingint)INSERT INTO @.t (player_name, topups, buying )SELECT Player_name,SUM([Top-ups])AS TOPUPS ,NULLFROM (SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,Events.Top_up * Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN EventsON Event_data.Event_id = Events.idINNERJOIN PlayersON Event_data.Player_id = Players.Player_idWHERE Event_data.Transaction_type = 2--AS TopupsGROUP BY Player_name )AS TopupsINSERT INTO @.t (player_name, topups, buying )SELECT Player_name,NULL,SUM([Re-buys])AS REBUYSFROM (SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value * Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN EventsON Event_data.Event_id = Events.idINNERJOIN PlayersON Event_data.Player_id = Players.Player_idWHERE Event_data.Transaction_type = 3--AS REBUYSGROUP BY Player_name )AS REBUYSSELECT player_name,min(topups),min(buying)FROM @.tGROUP BY player_nameORDER BY player_name

|||

Once again thank you for all your help. I am going to mark the previous one as the answer, but I still have one outstanding issue: the return results give me the column name of player_name but the other two say no column name even thought the totals are correct. Heres what I have:

declare

@.ttable( player_namevarchar(100), topups1int, buying1int)

INSERT

INTO @.t(player_name, topups1)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, buying1)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

SELECT

player_name,min(topups1),min(buying1)

FROM

@.t

GROUP

BY player_name

ORDER

BY player_name|||

Give column names for your expressions in your last SELECT statement.

SELECTplayer_name,min(topups1) AS topups1,min(buying1) AS buying1

FROM@.t

GROUPBY player_name

ORDERBY player_name

|||Excellent!!! Thank you very much

Help with query

Hi all,
I have the following table:
CREATE TABLE [dbo].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](100) NOT NULL,
[CompanyTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL)
These companies can have a variety of CompanyTypeID's. I want to
retrieve a different number of records for each CompanyTypeID in one
call.
Ex.:
DECLARE @.countType1 int
DECLARE @.countType2 int
DECLARE @.countType3 int
DECLARE @.countType4 int
DECLARE @.countType5 int
SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
UNION
SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
UNION
SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
UNION
SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
At this point, Im doing it all with a UNION and getting what I want.
IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
HAVING and use the @.count variables to return the desired number of
records per companytype.
Thanks,
VictorWhat's wrong with using the union?
you'll want to change it to a UNION ALL in the interim, though, since
there won't be any duplicates to eliminate.
ViLo wrote:
> Hi all,
>
> I have the following table:
> CREATE TABLE [dbo].[Company](
> [CompanyID] [int] IDENTITY(1,1) NOT NULL,
> [CompanyName] [varchar](100) NOT NULL,
> [CompanyTypeID] [int] NOT NULL,
> [Active] [bit] NOT NULL)
>
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
>
> Thanks,
> Victor
>|||ViLo (victor@.lamovidarecords.com) writes:
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
> Ex.:
>
> DECLARE @.countType1 int
> DECLARE @.countType2 int
> DECLARE @.countType3 int
> DECLARE @.countType4 int
> DECLARE @.countType5 int
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @.count variables to return the desired number of
> records per companytype.
First of all, your query looks funny, as you say TOP 100 but as
there is no ORDER BY, you will get the 100, 200, etc as the optimizer
sees fit. Which will be neither random, nor deterministic.
If you are on SQL 2005 you can use TOP (@.count) if the arbitrary
choice is OK to you.
On SQL 2000, you probably need to use a temp table, particularly if you
want the TOP 200 by alphabet or so.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx