Re: Subquery Problem and Count

From: Scott (sbailey_at_mileslumber.com)
Date: 09/11/04 

Date: Sat, 11 Sep 2004 17:41:24 -0500

Disregard the count issue, I solved it.

"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:OARQmCEmEHA.3816@TK2MSFTNGP14.phx.gbl...
> Hi
>
> The DDL and Test data are excellent!!
>
> Assuming there will either be 0 or 1 teamMember with teamTypeID = 1 then
try
> something like:
>
> SELECT t.teamID,
> t.teamName,
> t.shiftID,
> s.shiftName,
> a.areaName,
> l.UserId As LeaderId,
> l.UserFirst As LeaderFirstName,
> l.UserLastName As LeaderLastName
> FROM t_teams t
> JOIN t_shifts s ON t.shiftID = s.shiftID
> JOIN t_areas a ON t.areaID = a.areaID
> LEFT JOIN (SELECT m.teamID, m.userID, u.userFirst, u.userLastName
> FROM t_teammembers m
> JOIN t_users u ON m.userID = u.userID
> WHERE m.teamTypeID = 1 ) l ON t.teamID = l.teamID
> LEFT OUTER JOIN t_teammembers m ON t.teamID = m.teamID
>
> John
>
> "Scott" <sbailey@mileslumber.com> wrote in message
> news:ur11X6CmEHA.948@TK2MSFTNGP12.phx.gbl...
> > DDL included . I'm trying to return Team Name, etc. from a t_teams table
> > that has some simple joins. FIGURE 1 does this fine and even return the
> > userID of any user that is a team leader (t_teammembers.teamTypeID = 1).
> My
> > problem is getting the First and Last name of the user that is a leader.
> > When I run FIGURE 2, I get an error 'Only one expression can be
specified
> in
> > the select list when the subquery is not introduced with EXISTS.'
> >
> > Basically, I've found that if I add one join into the subquery in FIGURE
> 1,
> > I get this error. Without some type of join, I can't get to the first
and
> > last name of a leader. As previously stated, I included the DDL because
I
> > knew it would require it, however, the problem isn't that complex. ANY
> help
> > finding an alternate route of making FIGURE 2 return the user first and
> last
> > name within the subquery would be appreciated greatly. (This was my
first
> > time to submit a DDL, all that's needed is a empty table)
> >
> >
> > -- FIGURE 1
> >
> > SELECT t_teams.teamID, t_teams.teamName, t_teams.shiftID,
> > t_shifts.shiftName, t_areas.areaName,
> >
> > (SELECT t_teammembers.userID
> > WHERE t_teammembers.teamTypeID = 1 AND
> t_teammembers.teamID=t_teams.teamID)
> > As Leader
> > FROM t_teams INNER JOIN
> > t_shifts ON t_teams.shiftID = t_shifts.shiftID
INNER
> > JOIN
> > t_areas ON t_teams.areaID = t_areas.areaID LEFT
> OUTER
> > JOIN
> > t_teammembers ON t_teams.teamID =
> t_teammembers.teamID
> >
> >
> > -- FIGURE 2
> >
> > SELECT t_teams.teamID, t_teams.teamName, t_teams.shiftID,
> > t_shifts.shiftName, t_areas.areaName,
> > (SELECT t_teammembers.userID, t_users.userFirst, t_users.userLastName
> > FROM t_teammembers INNER JOIN
> > t_users ON t_teammembers.userID = t_users.userID
> > WHERE t_teammembers.teamTypeID = 1 AND
> t_teammembers.teamID=t_teams.teamID)
> > As Leader
> > FROM t_teams INNER JOIN
> > t_shifts ON t_teams.shiftID = t_shifts.shiftID
INNER
> > JOIN
> > t_areas ON t_teams.areaID = t_areas.areaID LEFT
> OUTER
> > JOIN
> > t_teammembers ON t_teams.teamID =
> t_teammembers.teamID
> >
> >
> > -- DDL Script below will create all tablesand sample data
> >
> > if not exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[t_shifts]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> > BEGIN
> > CREATE TABLE [t_shifts] (
> > [shiftID] [int] IDENTITY (1, 1) NOT NULL ,
> > [shiftName] [varchar] (50) NULL ,
> > CONSTRAINT [PK_t_shifts] PRIMARY KEY CLUSTERED
> > (
> > [shiftID]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> > INSERT INTO [t_shifts](shiftName)
> > VALUES('Shift 1')
> > INSERT INTO [t_shifts](shiftName)
> > VALUES('Shift 2')
> > INSERT INTO [t_shifts](shiftName)
> > VALUES('Shift 3')
> > INSERT INTO [t_shifts](shiftName)
> > VALUES('Shift 4')
> > INSERT INTO [t_shifts](shiftName)
> > VALUES('Shift 5')
> >
> >
> > if not exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[t_areas]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> > BEGIN
> > CREATE TABLE [t_areas] (
> > [areaID] [int] IDENTITY (1, 1) NOT NULL ,
> > [areaName] [varchar] (60) NULL ,
> > CONSTRAINT [PK_t_areas] PRIMARY KEY CLUSTERED
> > (
> > [areaID]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> > INSERT INTO [t_areas](areaName)
> > VALUES('Area 1')
> > INSERT INTO [t_areas](areaName)
> > VALUES('Area 2')
> > INSERT INTO [t_areas](areaName)
> > VALUES('Area 3')
> > INSERT INTO [t_areas](areaName)
> > VALUES('Area 4')
> > INSERT INTO [t_areas](areaName)
> > VALUES('Area 5')
> >
> > if not exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[t_teamMemberTypes]') and OBJECTPROPERTY(id,
> > N'IsUserTable') = 1)
> > BEGIN
> > CREATE TABLE [t_teamMemberTypes] (
> > [teammTypeID] [int] IDENTITY (1, 1) NOT NULL ,
> > [teammType] [varchar] (50) NULL ,
> > CONSTRAINT [PK_t_teamTypes] PRIMARY KEY CLUSTERED
> > (
> > [teammTypeID]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> > INSERT INTO [t_teamMemberTypes](teammType)
> > VALUES('Type 1')
> > INSERT INTO [t_teamMemberTypes](teammType)
> > VALUES('Type 2')
> > INSERT INTO [t_teamMemberTypes](teammType)
> > VALUES('Type 3')
> > INSERT INTO [t_teamMemberTypes](teammType)
> > VALUES('Type 4')
> > INSERT INTO [t_teamMemberTypes](teammType)
> > VALUES('Type 5')
> >
> >
> > if not exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[t_teams]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> > BEGIN
> > CREATE TABLE [t_teams] (
> > [teamID] [int] IDENTITY (1, 1) NOT NULL ,
> > [shiftID] [int] NULL ,
> > [areaID] [int] NULL ,
> > [millID] [int] NULL ,
> > [teamName] [varchar] (50) NULL ,
> > CONSTRAINT [PK_t_teams] PRIMARY KEY CLUSTERED
> > (
> > [teamID]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> > INSERT INTO t_teams(shiftID, areaID, millID, teamName)
> > VALUES('1', '1', '1', 'Team A')
> > INSERT INTO t_teams(shiftID, areaID, millID, teamName)
> > VALUES('2', '1', '1', 'Team B')
> > INSERT INTO t_teams(shiftID, areaID, millID, teamName)
> > VALUES('1', '2', '1', 'Team C')
> > INSERT INTO t_teams(shiftID, areaID, millID, teamName)
> > VALUES('2', '2', '1', 'Team D')
> > INSERT INTO t_teams(shiftID, areaID, millID, teamName)
> > VALUES('3', '2', '1', 'Team E')
> > INSERT INTO t_teams(shiftID, areaID, millID, teamName)
> > VALUES('1', '3', '1', 'Team F')
> > INSERT INTO t_teams(shiftID, areaID, millID, teamName)
> > VALUES('2', '3', '1', 'Team G')
> > INSERT INTO t_teams(shiftID, areaID, millID, teamName)
> > VALUES('3', '3', '1', 'Team H')
> >
> >
> > if not exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[t_users]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> > BEGIN
> > CREATE TABLE [t_users] (
> > [userID] [int] IDENTITY (1, 1) NOT NULL ,
> > [userEmail] [varchar] (30) NULL ,
> > [userLastName] [varchar] (35) NULL ,
> > [userFirst] [varchar] (35) NULL ,
> > [userActive] [tinyint] NULL ,
> > [millID] [int] NULL ,
> > CONSTRAINT [PK_t_users] PRIMARY KEY CLUSTERED
> > (
> > [userID]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> > INSERT INTO t_users(userEmail, userLastName, userFirst, userActive,
> millID)
> > VALUES('mike@cnn.com', 'mike', 'smith', '1', '1')
> > INSERT INTO t_users(userEmail, userLastName, userFirst, userActive,
> millID)
> > VALUES('john@cnn.com', 'john', 'taft', '1', '1')
> > INSERT INTO t_users(userEmail, userLastName, userFirst, userActive,
> millID)
> > VALUES('ben@cnn.com', 'ben', 'jones', '1', '1')
> > INSERT INTO t_users(userEmail, userLastName, userFirst, userActive,
> millID)
> > VALUES('tom@cnn.com', 'tom', 'hurt', '1', '1')
> > INSERT INTO t_users(userEmail, userLastName, userFirst, userActive,
> millID)
> > VALUES('larry@cnn.com', 'larry', 'mitchell', '1', '1')
> >
> >
> > if not exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[t_teammembers]') and OBJECTPROPERTY(id,
N'IsUserTable')
> =
> > 1)
> > BEGIN
> > CREATE TABLE [t_teammembers] (
> > [teammID] [int] IDENTITY (1, 1) NOT NULL ,
> > [userID] [int] NULL ,
> > [teamID] [int] NULL ,
> > [teamTypeID] [int] NULL ,
> > CONSTRAINT [PK_t_teammembers] PRIMARY KEY CLUSTERED
> > (
> > [teammID]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> >
> > INSERT INTO t_teammembers(userID, teamID, teamTypeID)
> > VALUES('1', '1', '1')
> >
> > INSERT INTO t_teammembers(userID, teamID, teamTypeID)
> > VALUES('2', '4', '1')
> >
> > INSERT INTO t_teammembers(userID, teamID, teamTypeID)
> > VALUES('3', '5', '1')
> >
> > INSERT INTO t_teammembers(userID, teamID, teamTypeID)
> > VALUES('4', '4', '3')
> >
> > INSERT INTO t_teammembers(userID, teamID, teamTypeID)
> > VALUES('5', '4', '2')
> >
> > INSERT INTO t_teammembers(userID, teamID, teamTypeID)
> > VALUES('6', '1', '4')
> >
> > INSERT INTO t_teammembers(userID, teamID, teamTypeID)
> > VALUES('1', '5', '4')
> >
> >
> >
> >
>
>

Relevant Pages

  • Re: Subquery Problem
    ... I've found that if I add one join into the subquery in FIGURE ... I included the DDL because ... > OUTER ... > millID) ...
    (microsoft.public.sqlserver.programming)
  • Re: Resettin Running Total
    ... I got these results by copying and pasting the DDL from your first ... then copying and pasting the INSERT statements and the SELECT ... Did you copy and paste my subquery when you tested it, ... could you check for possible typing errors? ...
    (microsoft.public.sqlserver.programming)
  • Re: Why doesnt this work?
    ... says that no shop_person from this subquery may be equal - of course, ... Begin by posting your DDL ... Without DDL and sample data, with only a query and a "it does not work!", ...
    (microsoft.public.sqlserver.programming)
  • Re: Subquery returned more than 1 value. This is not permitted when th
    ... You're expecting a single row from that subquery?? ... Post your DDL ... > I am writing a cursor in which i am updating a table for each matching ...
    (microsoft.public.sqlserver.server)