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 Select Minimum Value

I need help in doing a select statment with a minimum
here is the statement
CREATE TABLE [Test] (
[rIndex] [int] IDENTITY (1, 1) NOT NULL ,
[Defaul_] [int] NULL ,
[FilterType] [int] NULL ,
[ProtScr] [int] NULL ,
[ProtRank] [int] NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ageStart] [int] NULL ,
[ageStop] [int] NULL ,
[Sex] [int] NULL ,
CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
(
[rIndex]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'FOBT',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,3,'Sigmoidoscopy',50, 90,1)
insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
AgeStart, AgeStop,Sex)
values
(1,1,1,5,'Colonoscopy',65, 90,1)
I have a subquery to try and get the minimum ProtScr and ProtRank
When I do the subquery
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
I get 2 values
ProtScr ProtRank
1 3
1 5
if possible I want the subquery to only pick the lower value only. The main
select statement that I tried to do is located below to select the values
based on the subquery.
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where ProtRank EXISTS in
(Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1))
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. MiyasatoHi
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>I need help in doing a select statment with a minimum
> here is the statement
> CREATE TABLE [Test] (
> [rIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [Defaul_] [int] NULL ,
> [FilterType] [int] NULL ,
> [ProtScr] [int] NULL ,
> [ProtRank] [int] NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ageStart] [int] NULL ,
> [ageStop] [int] NULL ,
> [Sex] [int] NULL ,
> CONSTRAINT [PK_Test_1] PRIMARY KEY CLUSTERED
> (
> [rIndex]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'FOBT',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,3,'Sigmoidoscopy',50, 90,1)
> insert into Test ( Defaul_,FilterType,ProtScr,ProtRank,Desc
ription,
> AgeStart, AgeStop,Sex)
> values
> (1,1,1,5,'Colonoscopy',65, 90,1)
> I have a subquery to try and get the minimum ProtScr and ProtRank
> When I do the subquery
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> I get 2 values
> ProtScr ProtRank
> 1 3
> 1 5
> if possible I want the subquery to only pick the lower value only. The
> main select statement that I tried to do is located below to select the
> values based on the subquery.
> Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
> ageStart,AgeStop,sex from Test
> where ProtRank EXISTS in
> (Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1))
> Thanks
> Stephen K. Miyasato
>|||Thanks that helped
I get
protScr ProtRank
1 3
The subquery works but I could not get the main query to work
Select rIndex,Defaul_, FilterType, ProtScr,ProtRank, Description,
ageStart,AgeStop,sex from Test
where protScr, ProtRank EXISTS in -- need some help here
( -- subquery begins
SELECT ProtScr,MIN(ProtRank) AS ProtRank
FROM
(
Select ProtScr, MIN(ProtRank) AS ProtRank from test
GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
AND (Sex IN (1, 2)) AND (Defaul_ = 1)
) AS Der
GROUP BY ProtScr
) -- subquery ends
Where (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65) AND
(Sex IN (1, 2)) AND (Defaul_ = 1))
Thanks
Stephen K. Miyasato
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8bbKynTGHA.776@.TK2MSFTNGP09.phx.gbl...
> Hi
> SELECT ProtScr,MIN(ProtRank) AS ProtRank
> FROM
> (
> Select ProtScr, MIN(ProtRank) AS ProtRank from test
> GROUP BY ProtScr , Defaul_ , FilterType, ageStart,ageStop, Sex
> HAVING (FilterType IN (1, 2, 4)) AND (AgeStart <= 65) AND (AgeStop >= 65)
> AND (Sex IN (1, 2)) AND (Defaul_ = 1)
> ) AS Der
> GROUP BY ProtScr
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OkzZlunTGHA.5496@.TK2MSFTNGP11.phx.gbl...
>

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

Help with insert query

TblA TblB
---
ColA int Identity ColB char(5)
ColB char(5) FK
ColC char(6)
ColD char(50)
I would like to add rows to TblA where TblA/ColB would be populated
from TblB and ColC and ColD would be literals for each row added. ColC
= 'XXXX' and ColD = 'Suspense'
ColB and ColC in TblA combine to make a referenced key. There are
already some rows which are in TblA.
For example,
INSERT INTO TblA
(ColB, ColC, ColD)
VALUES ((
SELECT ColB
FROM TblB
WHERE ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX'
)), 'XXXX', 'Suspense')
Is there any way to do this?
TIA Lars> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
>
INSERT INTO TblA(ColB, ColC, ColD)
SELECT ColB, 'XXXX', 'Suspense'
FROM TblB
WHERE ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX')
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hope i understood you right:
Insert Into TblA
Select 'SomethingforA',
ColB,
'XXXX',
'Suspense'
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:df286192dm1u9468c1i6bqfkulc5pfmoh5@.
4ax.com...
> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
> Is there any way to do this?
> TIA Lars|||Try,
INSERT INTO TblA (ColB, ColC, ColD)
SELECT ColB, 'XXXX', 'Suspense'
FROM TblB
WHERE
ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX'
);
AMB
"larzeb" wrote:

> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
> Is there any way to do this?
> TIA Lars
>