Combining XML

Assume I have the following, in T-SQL:

declare @.xml1 xml

declare @.xml2 xml

set @.xml1 = '<first></first>'

set @.xml2 = '<second></second>'

I want to combine these, into the following, and return the results typed as XML:

<results>

<first></first>

<second></second>

</results>

I can't seem to find any way to do this - other than converting them to a string, and concatinating them and then typing the results as XML. Any ideas?

Thanks,

Scott

One way to do this is to use 'FOR XML PATH'

declare @.xml1 xml

declare @.xml2 xml

set @.xml1 = '<first></first>'

set @.xml2 = '<second></second>'

select @.xml1 , @.xml2
FOR XML PATH ('results'), type

The section 'Constructing XML Using FOR XML ' in BOL has more information about the different FOR XML capabilities.

Thanks

Babu

|||

Beautiful!!!

Thank you Babu!

combining two tables

If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3select A.col1,B.col1
FROM table_A A,table_B B

good luck with school.|||hahaha, been using only joins, didn't remember about that, thanks

Combining two select statements

I have a SP returning the following result
The select statement for this is

Code:

SELECT dbo.TEST1.[OFFICE NAME],COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCountFROM dbo.Test2INNERJOIN dbo.test3INNERJOIN dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryIDWHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)GROUP BY dbo.TEST1.[OFFICE NAME]ORDER BYCOUNT(dbo.TEST1.[ACCOUNT ID])DESC

name id count

case1 226 320
case2 219 288
case3 203 163
case4 223 90
case5 224 73

i have another select stnat which returns like this
The select statement is

Code:Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name]
order by count(test1.[office name]) DESC

name count
case6 10
case2 56
case4 66
case1 74
case3 88
case7 100
case5 177

How can i combine this select stament with the SP, so that, i get a fourth column with

case1 226 320 74
case2 219 288 56
....................
.....................

Hope i am not confusing you all
Please help me, if someone knows how to combine this?

Thanks

Use an alias for the Office Name column for both statements and add the id column to your first select( you need to add this column to your the GROUP BY list). Then you can use an INNER JOIN on this name column and retrieve all three columns.

Something like:

SELECT t1.name, t1.id, t1.AccountCount, t2.AccountCount2 FROM (SELECT dbo.TEST1.[OFFICE NAME] as name, [ACCOUNT ID] as id,COUNT(dbo.TEST1.[ACCOUNT ID])AS AccountCount
FROM dbo.Test2INNERJOIN
dbo.test3INNERJOIN
dbo.Test4ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code]INNERJOIN
dbo.TEST1ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID]ON dbo.Test2.[Model ID] = dbo.test3.IDINNERJOIN
dbo.[Inquiry Details]ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDateBETWEENCONVERT(DATETIME, @.startDate, 102)ANDCONVERT(DATETIME, @.endDate, 102))AND dbo.Test1.[Account ID]IN(SELECT [account id]FROM test5WHERE [Contact ID] = @.contactId)
GROUP BY name, id ) t1 INNER JOIN (Select test1.[office name] as name, count(test1.[office name]) as AccountCount2 From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code]
Group by test1.[Office Name] ) t2 ON t1.name=t2.name
ORDER BY t1.AccountCount DESC

|||

I think you've forgotten a column in your first select statement. Your first select statement selects only two columns while the output shows three columns, name, id and count. Please check and repost.

Combining two pivot tables and displaying the data

Hi all,
I have the following tables

Tbl_Request

RequestType NoOfPositionsRequired SkillCategory
Req1 10 .Net
Req2 3 Java
Req1 2 SQL
Req3 5 Java
-

Tbl_User

ID SkillCategory Experienced
--
101 Java 0
102 .Net 1
103 Java 1
104 SQL 1
105 .Net 0
106 J2EE 0

Experience is a bool column.

Required Output:

SkillCategory Req1 Req2 Req3 TotalDemand Exp NonExp Total Supply

.Net 12 0 0 12 1 1 2
Java 0 3 5 8 1 2 2
SQL 1 0 0 1 1 0 1
-

Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.

Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.

I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.

You can combine using join statement

Sample

Code Snippet

TempTable1 - SkillCategory,Req1,Req2,Req3,TotalDemand

TempTable2 - SkillCategory,Exp,NonExp,TotalSupply

Select T1.SkillCategory,T1.Req1,T1.Req2,T1.Req3,T1.TotalDemand,T2.Exp,T2.NonExp,T2.TotalSupply

from TempTable1 T1

left join TempTable2 T2 on T2.SkillCategory = T1.SkillCategory

|||

Hi Vidhura,

That solution works fine for my web application.But for using that procedure as dataset for the reporting services I face an error. I can't use two temp tables in a procedure.I want to know if this can be achieved without making use of temporary table

|||

Hi,

Using the above SQL Statement just replace the two temptables with select statements, as below

Code Snippet

--create table #Tbl_Request (

--RequestType varchar(20),

--NoOfPositionsRequired int ,

--SkillCategory varchar(20)

--)

--

--insert into #Tbl_Request values ('Req1', 10,'.Net')

--insert into #Tbl_Request values ('Req2',3,'Java')

--insert into #Tbl_Request values ('Req1',2,'SQL')

--insert into #Tbl_Request values ('Req3',5,'Java')

--

--create table #Tbl_User (ID int, SkillCategory varchar(20) ,Experienced int)

--

--insert into #Tbl_User values (101, 'Java', 0)

--insert into #Tbl_User values (102, '.Net', 1)

--insert into #Tbl_User values (103, 'Java', 1)

--insert into #Tbl_User values (104, 'SQL', 1)

--insert into #Tbl_User values (105, '.Net', 0)

--insert into #Tbl_User values (106, 'J2EE', 0)

select

*

from (

SELECT

SkillCategory,

isnull(Req1,0) Req1,

isnull(Req2,0)Req2,

isnull(Req3,0) Req3,

isnull(Req1,0)+ isnull(Req2,0)+ isnull(Req3,0) as TotalDemand

FROM

(select SkillCategory,NoOfPositionsRequired,RequestType from #Tbl_Request) p

PIVOT

(

SUM (NoOfPositionsRequired)

FOR RequestType IN

( Req1, Req2, Req3 )

) AS pvt ) as firsttable

left join

(

SELECT SkillCategory, [0] as Exper,[1] as NonExp

FROM

(select ID, SkillCategory,Experienced from #Tbl_User ) p

PIVOT

(

COUNT (ID)

FOR Experienced IN

( [1], [0] )

) AS pvt

) as secondtable on

firsttable.skillcategory = secondtable.skillcategory

Hope that helps

Matt

|||

1 - Your result does not make much sense.

1.1 'J2EE' is not in the final result

1.2 '.Net' appears just one time in table [Tbl_Request] and the value of [NoOfPositionsRequired] is 10. So the value 12 for [Req1] in the final result is not correct

2 - For this kind of problem / question, is very helpful to post DDL, including constraints and indexes, sample data in the form of "insert" statements and expected result. That way we do not have to waste our time simulating your environment. The help should be in both way, shouldn't it?

Code Snippet

-- POST DDL

create table dbo.Tbl_Request (

RequestType varchar(10) not null,

NoOfPositionsRequired int not null,

SkillCategory varchar(15) not null

)

go

create table dbo.Tbl_User (

ID int not null,

SkillCategory varchar(15) not null,

Experienced smallint not null

)

go

-- POST SAMPLE DATA

insert into dbo.Tbl_Request values('Req1', 10, '.Net')

insert into dbo.Tbl_Request values('Req2', 3, 'Java')

insert into dbo.Tbl_Request values('Req1', 2, 'SQL')

insert into dbo.Tbl_Request values('Req3', 5, 'Java')

go

insert into dbo.Tbl_User values(101, 'Java', 0)

insert into dbo.Tbl_User values(102, '.Net', 1)

insert into dbo.Tbl_User values(103, 'Java', 1)

insert into dbo.Tbl_User values(104, 'SQL', 1)

insert into dbo.Tbl_User values(105, '.Net', 0)

insert into dbo.Tbl_User values(106, 'J2EE', 0)

go

declare @.pvt_columns nvarchar(max)

declare @.sel_columns nvarchar(max)

declare @.isnull nvarchar(max)

declare @.sum_col nvarchar(max)

declare @.sql nvarchar(max)

set @.pvt_columns = stuff(

(

select ',' + quotename(RequestType)

from (select distinct RequestType from dbo.Tbl_Request) as t

order by RequestType

for xml path('')

), 1, 1, '')

set @.isnull = N'isnull(' + replace(@.pvt_columns, ',', N',0),isnull(') + N', 0)'

set @.sum_col = replace(@.isnull, ',isnull', '+isnull')

set @.sel_columns = stuff(

(

select ',isnull(' + quotename(RequestType) + ',0) as ' + quotename(RequestType)

from (select distinct RequestType from dbo.Tbl_Request) as t

order by RequestType

for xml path('')

), 1, 1, '')

set @.sql = N'

select

coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],

' + @.sel_columns + N',' +

@.sum_col + N'as TotalDemand,

b.Exp,

b.NonExp,

[Total Supply]

from

(

select

*

from

dbo.Tbl_Request

pivot

(

sum(NoOfPositionsRequired)

for RequestType in (' + @.pvt_columns + N')

) as pvt

) as a

full outer join

(

select

SkillCategory,

sum(case when Experienced = 1 then 1 else 0 end) as Exp,

sum(case when Experienced = 0 then 1 else 0 end) as NonExp,

sum(1) as [Total Supply]

from

dbo.Tbl_User

group by

SkillCategory

) as b

on a.SkillCategory = b.SkillCategory

order by

coalesce(a.SkillCategory, b.SkillCategory)

'

exec sp_executesql @.sql

go

drop table dbo.Tbl_User, dbo.Tbl_Request

go

You have to be careful with SQL injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||Thank you hunchback..Thanks a lot. Apologies for the lack of schema. I will keep that in mind the next time i post some qustions on the forum.

Firstly my view requires me to have only those skills as demanded to appear and not all the skills that are possible. Hence J2EE was not included. Because there are some160 skills that covers all the associates and not all of whom are to be shown i.e not all those skill category people are to be shown. Secondly that was a small mistake and you are right about the count being 10 not 12 for .Net.

So could you tell me how I can achieve this..

Thanks again

|||

Hi,

A simple where clause would do it, in both select statement

Code Snippet

WHERE

SkillCategory IN ('.NET', 'SQL' .... )

Sure you could amend the above procedure to pass a list in as a variable

Code Snippet

declare @.listSkills varchar(100)

set @.ListSkills = '''.NET'',''SQL'''

set @.sql = N'

select

coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],

' + @.sel_columns + N',' +

@.sum_col + N'as TotalDemand,

b.Exp,

b.NonExp,

[Total Supply]

from

(

select

*

from

dbo.Tbl_Request

pivot

(

sum(NoOfPositionsRequired)

for RequestType in (' + @.pvt_columns + N')

) as pvt

) as a

where

skillcategory IN ('@.ListSkills')

full outer join

(

select

SkillCategory,

sum(case when Experienced = 1 then 1 else 0 end) as Exp,

sum(case when Experienced = 0 then 1 else 0 end) as NonExp,

sum(1) as [Total Supply]

from

dbo.Tbl_User

where

skillcategory IN ('@.ListSkills')

group by

SkillCategory

) as b

on a.SkillCategory = b.SkillCategory

order by

coalesce(a.SkillCategory, b.SkillCategory)

Hope that helps

Matt

Combining two pivot tables and displaying the data

Hi all,
I have the following tables

Tbl_Request

RequestType NoOfPositionsRequired SkillCategory
Req1 10 .Net
Req2 3 Java
Req1 2 SQL
Req3 5 Java
-

Tbl_User

ID SkillCategory Experienced
--
101 Java 0
102 .Net 1
103 Java 1
104 SQL 1
105 .Net 0
106 J2EE 0

Experience is a bool column.

Required Output:

SkillCategory Req1 Req2 Req3 TotalDemand Exp NonExp Total Supply

.Net 12 0 0 12 1 1 2
Java 0 3 5 8 1 2 2
SQL 1 0 0 1 1 0 1
-

Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.

Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.

I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.

You can combine using join statement

Sample

Code Snippet

TempTable1 - SkillCategory,Req1,Req2,Req3,TotalDemand

TempTable2 - SkillCategory,Exp,NonExp,TotalSupply

Select T1.SkillCategory,T1.Req1,T1.Req2,T1.Req3,T1.TotalDemand,T2.Exp,T2.NonExp,T2.TotalSupply

from TempTable1 T1

left join TempTable2 T2 on T2.SkillCategory = T1.SkillCategory

|||

Hi Vidhura,

That solution works fine for my web application.But for using that procedure as dataset for the reporting services I face an error. I can't use two temp tables in a procedure.I want to know if this can be achieved without making use of temporary table

|||

Hi,

Using the above SQL Statement just replace the two temptables with select statements, as below

Code Snippet

--create table #Tbl_Request (

--RequestType varchar(20),

--NoOfPositionsRequired int ,

--SkillCategory varchar(20)

--)

--

--insert into #Tbl_Request values ('Req1', 10,'.Net')

--insert into #Tbl_Request values ('Req2',3,'Java')

--insert into #Tbl_Request values ('Req1',2,'SQL')

--insert into #Tbl_Request values ('Req3',5,'Java')

--

--create table #Tbl_User (ID int, SkillCategory varchar(20) ,Experienced int)

--

--insert into #Tbl_User values (101, 'Java', 0)

--insert into #Tbl_User values (102, '.Net', 1)

--insert into #Tbl_User values (103, 'Java', 1)

--insert into #Tbl_User values (104, 'SQL', 1)

--insert into #Tbl_User values (105, '.Net', 0)

--insert into #Tbl_User values (106, 'J2EE', 0)

select

*

from (

SELECT

SkillCategory,

isnull(Req1,0) Req1,

isnull(Req2,0)Req2,

isnull(Req3,0) Req3,

isnull(Req1,0)+ isnull(Req2,0)+ isnull(Req3,0) as TotalDemand

FROM

(select SkillCategory,NoOfPositionsRequired,RequestType from #Tbl_Request) p

PIVOT

(

SUM (NoOfPositionsRequired)

FOR RequestType IN

( Req1, Req2, Req3 )

) AS pvt ) as firsttable

left join

(

SELECT SkillCategory, [0] as Exper,[1] as NonExp

FROM

(select ID, SkillCategory,Experienced from #Tbl_User ) p

PIVOT

(

COUNT (ID)

FOR Experienced IN

( [1], [0] )

) AS pvt

) as secondtable on

firsttable.skillcategory = secondtable.skillcategory

Hope that helps

Matt

|||

1 - Your result does not make much sense.

1.1 'J2EE' is not in the final result

1.2 '.Net' appears just one time in table [Tbl_Request] and the value of [NoOfPositionsRequired] is 10. So the value 12 for [Req1] in the final result is not correct

2 - For this kind of problem / question, is very helpful to post DDL, including constraints and indexes, sample data in the form of "insert" statements and expected result. That way we do not have to waste our time simulating your environment. The help should be in both way, shouldn't it?

Code Snippet

-- POST DDL

create table dbo.Tbl_Request (

RequestType varchar(10) not null,

NoOfPositionsRequired int not null,

SkillCategory varchar(15) not null

)

go

create table dbo.Tbl_User (

ID int not null,

SkillCategory varchar(15) not null,

Experienced smallint not null

)

go

-- POST SAMPLE DATA

insert into dbo.Tbl_Request values('Req1', 10, '.Net')

insert into dbo.Tbl_Request values('Req2', 3, 'Java')

insert into dbo.Tbl_Request values('Req1', 2, 'SQL')

insert into dbo.Tbl_Request values('Req3', 5, 'Java')

go

insert into dbo.Tbl_User values(101, 'Java', 0)

insert into dbo.Tbl_User values(102, '.Net', 1)

insert into dbo.Tbl_User values(103, 'Java', 1)

insert into dbo.Tbl_User values(104, 'SQL', 1)

insert into dbo.Tbl_User values(105, '.Net', 0)

insert into dbo.Tbl_User values(106, 'J2EE', 0)

go

declare @.pvt_columns nvarchar(max)

declare @.sel_columns nvarchar(max)

declare @.isnull nvarchar(max)

declare @.sum_col nvarchar(max)

declare @.sql nvarchar(max)

set @.pvt_columns = stuff(

(

select ',' + quotename(RequestType)

from (select distinct RequestType from dbo.Tbl_Request) as t

order by RequestType

for xml path('')

), 1, 1, '')

set @.isnull = N'isnull(' + replace(@.pvt_columns, ',', N',0),isnull(') + N', 0)'

set @.sum_col = replace(@.isnull, ',isnull', '+isnull')

set @.sel_columns = stuff(

(

select ',isnull(' + quotename(RequestType) + ',0) as ' + quotename(RequestType)

from (select distinct RequestType from dbo.Tbl_Request) as t

order by RequestType

for xml path('')

), 1, 1, '')

set @.sql = N'

select

coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],

' + @.sel_columns + N',' +

@.sum_col + N'as TotalDemand,

b.Exp,

b.NonExp,

[Total Supply]

from

(

select

*

from

dbo.Tbl_Request

pivot

(

sum(NoOfPositionsRequired)

for RequestType in (' + @.pvt_columns + N')

) as pvt

) as a

full outer join

(

select

SkillCategory,

sum(case when Experienced = 1 then 1 else 0 end) as Exp,

sum(case when Experienced = 0 then 1 else 0 end) as NonExp,

sum(1) as [Total Supply]

from

dbo.Tbl_User

group by

SkillCategory

) as b

on a.SkillCategory = b.SkillCategory

order by

coalesce(a.SkillCategory, b.SkillCategory)

'

exec sp_executesql @.sql

go

drop table dbo.Tbl_User, dbo.Tbl_Request

go

You have to be careful with SQL injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||Thank you hunchback..Thanks a lot. Apologies for the lack of schema. I will keep that in mind the next time i post some qustions on the forum.

Firstly my view requires me to have only those skills as demanded to appear and not all the skills that are possible. Hence J2EE was not included. Because there are some160 skills that covers all the associates and not all of whom are to be shown i.e not all those skill category people are to be shown. Secondly that was a small mistake and you are right about the count being 10 not 12 for .Net.

So could you tell me how I can achieve this..

Thanks again

|||

Hi,

A simple where clause would do it, in both select statement

Code Snippet

WHERE

SkillCategory IN ('.NET', 'SQL' .... )

Sure you could amend the above procedure to pass a list in as a variable

Code Snippet

declare @.listSkills varchar(100)

set @.ListSkills = '''.NET'',''SQL'''

set @.sql = N'

select

coalesce(a.SkillCategory, b.SkillCategory) as [SkillCategory],

' + @.sel_columns + N',' +

@.sum_col + N'as TotalDemand,

b.Exp,

b.NonExp,

[Total Supply]

from

(

select

*

from

dbo.Tbl_Request

pivot

(

sum(NoOfPositionsRequired)

for RequestType in (' + @.pvt_columns + N')

) as pvt

) as a

where

skillcategory IN ('@.ListSkills')

full outer join

(

select

SkillCategory,

sum(case when Experienced = 1 then 1 else 0 end) as Exp,

sum(case when Experienced = 0 then 1 else 0 end) as NonExp,

sum(1) as [Total Supply]

from

dbo.Tbl_User

where

skillcategory IN ('@.ListSkills')

group by

SkillCategory

) as b

on a.SkillCategory = b.SkillCategory

order by

coalesce(a.SkillCategory, b.SkillCategory)

Hope that helps

Matt

Combining Table and Matrix format in one Report in RS2005

Hello,

I am using RS 2005 trying to create the following report. My report consists of the following columns: Question, Sub Question, N as Number of Responses, All as Average for all responses per given question and sub question, and Ethnicity column which is presented here in a Matrix format with ethnic group as columns and average response as Data values. It looks like my challenge is to combine Matrix format report (Ethnicity column) with a data such as N and All columns which are more like a table format. Any input how I could tackle this is greatly appreciated.

Thank you!

--

1.How often have you done each of the following?

N All F M Asian Multi-cultural a. Worked on a paper or project that required integrating ideas or information from various sources 1134 3.96 3.95 3.99 3.54 4.50 b. Used library resources 1132 4.21 4.26 4.09 4.12 4.33 c. Prepared multiple drafts of a paper or assignment before turning it in 1130 3.90 3.97 3.76 3.80 4.50

-How the source data looks like?sqlsql

Combining results - Performance Issues

Hi,
I am creating a view in the following way
CREATE VIEW TableView
AS
SELECT OriginalTable.*
FROM OriginalTable
WHERE ChangedFlag is null
UNION ALL
SELECT TableWithChanges.*
FROM TableWithChanges
OriginalTable and TableWithChanges have identical schemas. The data is in
OriginalTable and queries are run against it. However when I want to analyze
the query output with some changes I put the changed rows in
TableWithChanges and now run the queries against TableView. New and changed
rows are in TableWithChanges. Deletions and changes in OriginalTable are
handled by setting ChangedFlag = 1.
This way my earlier queries can keep running against OriginalTable and my
simulations can run against TableView.
The queries I run involve joins between many such tables, and sometimes self
joins too.
Problem: Performance is severely hit when I run queries against the view.
Specifically I observe that SQL Server does lot of processor intensive
activity. A query that was completing in 5 secs now is running for about 30
minutes (and has not completed yet). SQL Server is consuming close to 100%
CPU all this while.
Is there a better way in which I can combine the two data sets without
affecting performance to such an extent?
Please help me out here.
Thanks,
NitinPartitioned view is what you want. Though, you must follow its strict
guideline in order to get the benefits.
http://msdn.microsoft.com/library/e...des_06_17zr.asp
-oj
"Nitin M" <nitin@.nowhere.com> wrote in message
news:e7AWpMovFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am creating a view in the following way
> CREATE VIEW TableView
> AS
> SELECT OriginalTable.*
> FROM OriginalTable
> WHERE ChangedFlag is null
> UNION ALL
> SELECT TableWithChanges.*
> FROM TableWithChanges
> OriginalTable and TableWithChanges have identical schemas. The data is in
> OriginalTable and queries are run against it. However when I want to
> analyze the query output with some changes I put the changed rows in
> TableWithChanges and now run the queries against TableView. New and
> changed rows are in TableWithChanges. Deletions and changes in
> OriginalTable are handled by setting ChangedFlag = 1.
> This way my earlier queries can keep running against OriginalTable and my
> simulations can run against TableView.
> The queries I run involve joins between many such tables, and sometimes
> self joins too.
> Problem: Performance is severely hit when I run queries against the view.
> Specifically I observe that SQL Server does lot of processor intensive
> activity. A query that was completing in 5 secs now is running for about
> 30 minutes (and has not completed yet). SQL Server is consuming close to
> 100% CPU all this while.
> Is there a better way in which I can combine the two data sets without
> affecting performance to such an extent?
> Please help me out here.
> Thanks,
> Nitin
>|||Thanks OJ,
I have a explicit where clause [WHERE ChangedFlag is null] instead of the
check constraint. Will I give some better performance if I use check
constraints instead.
Also ChangedFlag column in this case is not a primary key column.
Is there any other trick to combine data?
Thanks,
Nitin
"oj" <nospam_ojngo@.home.com> wrote in message
news:eXBArSovFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Partitioned view is what you want. Though, you must follow its strict
> guideline in order to get the benefits.
> http://msdn.microsoft.com/library/e...des_06_17zr.asp
>
> --
> -oj
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:e7AWpMovFHA.4032@.TK2MSFTNGP15.phx.gbl...
>|||If you don't follow the guideline, you don't have a partitioned view. Thus,
sqlserver *will* be forced to scan every single table in your view
definition. PV is the trick to combine data.
-oj
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OpuTpcovFHA.1996@.TK2MSFTNGP10.phx.gbl...
> Thanks OJ,
> I have a explicit where clause [WHERE ChangedFlag is null] instead of the
> check constraint. Will I give some better performance if I use check
> constraints instead.
> Also ChangedFlag column in this case is not a primary key column.
> Is there any other trick to combine data?
> Thanks,
> Nitin
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eXBArSovFHA.2556@.TK2MSFTNGP15.phx.gbl...
>|||Why did you mimic a 1950's magnetic tape file generational system in
SQL? Are yoiu really using flags in a RDBMS, as if you were writing
assembly language code?
Go back to the basics; same schema means same entity in an RDBMS. Your
data model has split a set over two tables when you should have had
only one. My guess woild be that you need to show a history, whcih
means that you will have a (start_time, end_time) pair in the table and
will get the current status by looking at (end_time IS NULL).|||Hi Celko,
I have a system in which the original tables are being used in a zillion
places. The system does some analysis using queries. Now there is a need to
do the same analysis with changes to original data, a simulation or a "what
if the data changes" sort of analysis.
In this situation if I want to keep two 'avtars' of a row in the same table
I will to think about what to do with the queries that already exist.
And all this while I do not want to disturb any reports etc. which are
accessing the original data. I want the simulation and the existing stuff to
run simulataneously.
Do tell me if there is a better way out.
Thanks,
Nitin
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127294968.322772.46020@.g49g2000cwa.googlegroups.com...
> Why did you mimic a 1950's magnetic tape file generational system in
> SQL? Are yoiu really using flags in a RDBMS, as if you were writing
> assembly language code?
>
> Go back to the basics; same schema means same entity in an RDBMS. Your
> data model has split a set over two tables when you should have had
> only one. My guess woild be that you need to show a history, whcih
> means that you will have a (start_time, end_time) pair in the table and
> will get the current status by looking at (end_time IS NULL).
>|||"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23LDbZuqvFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Hi Celko,
> I have a system in which the original tables are being used in a zillion
> places. The system does some analysis using queries. Now there is a need
> to do the same analysis with changes to original data, a simulation or a
> "what if the data changes" sort of analysis.
> In this situation if I want to keep two 'avtars' of a row in the same
> table I will to think about what to do with the queries that already
> exist.
> And all this while I do not want to disturb any reports etc. which are
> accessing the original data. I want the simulation and the existing stuff
> to run simulataneously.
> Do tell me if there is a better way out.
> Thanks,
> Nitin
Why not do the simulation on a copy of the database?|||<Why not do the simulation on a copy of the database?>
Wont this take lot of extra time and space too?
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uQCI7%23qvFHA.2728@.TK2MSFTNGP14.phx.gbl...
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23LDbZuqvFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Why not do the simulation on a copy of the database?
>|||Space is cheap.
I don't understand the extra time comment.
Some compagnies even do reporting on a database copy.
Sure, the data isn't up to date as this depends on the backup frequency.
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23yk$hIrvFHA.4020@.TK2MSFTNGP10.phx.gbl...
> <Why not do the simulation on a copy of the database?>
> Wont this take lot of extra time and space too?
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uQCI7%23qvFHA.2728@.TK2MSFTNGP14.phx.gbl...
>

Combining record sets from two tables

Friends,
Can anyone give me the best way to solve the following challenge.
I have two tables in my MS SQLserver database. Table A has this dataset:
code_id code operations_descr
1 M Mob/Demob
2 T Transit
3 O Operation
4 S Standby
5 W WOW
6 C Crew Change
7 B Breakdown
8 R Maintenance
Table B has this dataset:
code_id operations_duration
3 125
3 10
2 1304
I want a new recordset where the sum of the operations_duration from table B
grouped on code_id is combined with the right code_id in table A, like this:
code_id code operations_descr total_operations_duration
1 M Mob/Demob 0
2 T Transit 1304
3 O Operation 135
4 S Standby 0
5 W WOW 0
6 C Crew Change 0
7 B Breakdown 0
8 R Maintenance 0
Thank you for any assistance!
/Leif S
--
Systems AnalystI've assumed that the first table is called "Operations" and that the second
one is called "OperationsDuration". Here is the query:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(OD.Total_Operations_Duration)
From Operations O, OperationsDuration OD
Where O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
Best regards,
Sami Samir
ITWorx
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi John,
The code you gave me works well. But it is more to it.
The data is collected from a survey vessel working in the North Sea and the
tables contains data from daily reports sent from the vessel. Table B stores
operations duration for each day where records from a given day share the
same report_id. When I take your code and add: "Where OD.report_id =
@.reportId" and supply report_id = 2, the result set exclude code lines from
table A that are not in table B like this:
code_id code operations_descr Total_Oper
ations_Duration
2 T Transit 1304
3 O Operation 135
Your advice is appreciated!
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>|||See if this works...
SELECT code_id, code, operations_descr,
operations_duration = (SELECT SUM(total_operations_duration)
FROM TABLEB
WHERE code_id = TABLEA.code_id
GROUP BY code_id
)
FROM TABLEA
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:5E01F636-997E-47A6-A844-206B6DBBE0CA@.microsoft.com...
> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
> B
> grouped on code_id is combined with the right code_id in table A, like
> this:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
I am not sure where report_id comes from as it was not mentioned in the
original post. You may want to check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data.
Try:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.reporti
d
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:
> Hi John,
> The code you gave me works well. But it is more to it.
> The data is collected from a survey vessel working in the North Sea and th
e
> tables contains data from daily reports sent from the vessel. Table B stor
es
> operations duration for each day where records from a given day share the
> same report_id. When I take your code and add: "Where OD.report_id =
> @.reportId" and supply report_id = 2, the result set exclude code lines fro
m
> table A that are not in table B like this:
> code_id code operations_descr Total_Oper
ations_Duration
> 2 T Transit 1304
> 3 O Operation 135
> Your advice is appreciated!
> /Leif S.
> --
> Systems Analyst
>
> "John Bell" wrote:
>|||Thanks, John. So easy!
When I moved the report_id qualifyer away from the "Where" clause and into
the Join statement I got what I wanted.
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> I am not sure where report_id comes from as it was not mentioned in the
> original post. You may want to check out
> http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
> data.
> Try:
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.repor
tid
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>

Combining PIVOT and INSERT queries

Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]As Q10FROM (SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]WHERE SurveyID=1)AS preData PIVOT (MAX(Answer)FOR QuestionIDIN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )AS dataORDER BY RespondantID

Thanks,

Martin

You can use a CTE and a SELECT into to get your pivot result to a new table. You need to remove ORDER BY RespondantID clause first.

Here is the sql script.

WITH mycte

AS

(SELECT RespondantID, [1]AS Q1, [2]AS Q2, [3]AS Q3, [4]AS Q4, [5]AS Q5, [6]AS Q6, [7]AS Q7, [8]AS Q8, [9]AS Q9, [10]AS Q10

FROM(SELECT RespondantID, QuestionID, Answer

FROM [3_Temp]

WHERE SurveyID= 1)AS preDataPIVOT(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))AS data

)

SELECT RespondantID, [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [Q7], [Q8], [Q9], [Q10]INTO [NewtableResult]FROM mycte

Combining numeric fields

Hello,
I use the following line in my sql view to bring 2 numeric fields together,
as one field (I'm using this to populate a listbox in vb.net).
This all works great, but I need to have the value 0 show up as 0.0. Right
now, it shows up as 0.
CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
Any suggestions?
TIA!
amberOn Tue, 20 Sep 2005 15:30:04 -0700, amber wrote:

>Hello,
>I use the following line in my sql view to bring 2 numeric fields together,
>as one field (I'm using this to populate a listbox in vb.net).
>This all works great, but I need to have the value 0 show up as 0.0. Right
>now, it shows up as 0.
>CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
>CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
>Any suggestions?
Hi amber,
Use the STR function instead of CAST. See the documentation in Books
Online for details on usage.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If the original data type of those two columns is integer (int, bigint,
smallint or tinyint) then cast it as decimal before casting it to varchar.
Look into data types in Books Online for more details, or provide DDL and
some sample data and please explain what exactly your goal is.
ML|||Thank you!
That fixed it!
Amber

Combining DELETE and JOIN statements

In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:

DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL

SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?

GerritYou could try with a NOT IN.

DELETE FROM GroupsMembers WHERE GroupID NOT IN (SELECT ID FROM Groups)
|||Thanks, that does seem to do the trick.

Gerrit

Combining Columns and Grouping By....

Hi,
I have the following SQL

SELECT Table1.Col1, Table3.Col1 AS Expr1,
COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
FROM Table3
INNER JOIN Table2 ON Table3.Col1=Table2.Col1
RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2
GROUP BY Table1.Col1, Table3.Col1

The output rows have a value in either Table1.Col1 or Table3.Col1 but not
both.
I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
column in the result but don't know how.
Thanks gratefullyHi

It would help if you posted the DDL (Create Table Statements) , example data
(as insert statements) and expected output. From your description it is not
100% clear how the tables relate or what results you expect.

If the values of Col1 are unique between each table your solution might be:

SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1

If not

SELECT IsNULL(T1.Col1,T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col2
ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
ELSE T3.Col3 END ) AS Col3No
FROM Table1 T1
LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
GROUP BY IsNULL(T1.Col1,T3.Col1)

or more probably

SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
FROM (
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1 ) A
GROUP BY Col1

John

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:ovWhb.854$_54.168325@.newsfep2-win.server.ntli.net...
> Hi,
> I have the following SQL
> SELECT Table1.Col1, Table3.Col1 AS Expr1,
> COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
> FROM Table3
> INNER JOIN Table2 ON Table3.Col1=Table2.Col1
> RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2
> GROUP BY Table1.Col1, Table3.Col1
> The output rows have a value in either Table1.Col1 or Table3.Col1 but not
> both.
> I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
> column in the result but don't know how.
> Thanks gratefully|||Thanks John,
I didn't explain too well so I'll detail tables, releationships and what I'm
trying to do. I have managed to reduce & simplify the issue to two tables:-

Targets table which has columns:
target id - key identity autoincrement integer
locationid - integer

Actions table which has columns:
actionid - key identity autoincrement integer
targetid - integer
locationid integer

relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
Actions.targetid (I want results from all rows in Actions).

I want to count all rows from Actions and group by locationid combined from
both tables.

Targets content:
targetid locationid
1 1
2 1

Actions Content:
actionid targetid locationid
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL

If I use:
SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS actions
FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
GROUP BY Actions.locationid, Targets.locationid

I get:
Actions Actions.locationid Targets.locationid
1 1 NULL
1 2 NULL
1 3 NULL
3 NULL 1

I want to combine both locationid columns in result giving:
Actions locationid
4 1
1 2
1 3

There are more columns than illustrated but if you the above can be cracked,
I'll be away!
Cheers,
Jack

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f886ac8$0$11451$afc38c87@.news.easynet.co.uk. ..
> Hi
> It would help if you posted the DDL (Create Table Statements) , example
data
> (as insert statements) and expected output. From your description it is
not
> 100% clear how the tables relate or what results you expect.
> If the values of Col1 are unique between each table your solution might
be:
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table1
> GROUP BY Col1
> UNION
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table3
> GROUP BY Col1
> If not
> SELECT IsNULL(T1.Col1,T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN
T1.Col2
> ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
> ELSE T3.Col3 END ) AS Col3No
> FROM Table1 T1
> LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
> GROUP BY IsNULL(T1.Col1,T3.Col1)
> or more probably
> SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
> FROM (
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table1
> GROUP BY Col1
> UNION
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table3
> GROUP BY Col1 ) A
> GROUP BY Col1
> John|||John,

Thanks for putting me on the right track. With ref to the example in my
reply post I used:

SELECT ISNULL(Actions.locationid, Targets.locationid) AS Location,
COUNT(Actions.actionid) AS Actions_No
FROM Actions LEFT OUTER JOIN
Targets ON Actions.targetid = Targets.targetid
GROUP BY ISNULL(Actions.locationid, Targets.locationid)

All the other columns I want to count are in the Actions table so I just
need to add them to the SELECT statement.
Thanks again,
Jack

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:Fu0ib.1525$_54.280845@.newsfep2-win.server.ntli.net...
> Thanks John,
> I didn't explain too well so I'll detail tables, releationships and what
I'm
> trying to do. I have managed to reduce & simplify the issue to two
tables:-
> Targets table which has columns:
> target id - key identity autoincrement integer
> locationid - integer
> Actions table which has columns:
> actionid - key identity autoincrement integer
> targetid - integer
> locationid integer
> relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
> Actions.targetid (I want results from all rows in Actions).
> I want to count all rows from Actions and group by locationid combined
from
> both tables.
> Targets content:
> targetid locationid
> 1 1
> 2 1
> Actions Content:
> actionid targetid locationid
> 1 NULL 1
> 2 NULL 2
> 3 NULL 3
> 4 1 NULL
> 5 1 NULL
> 6 2 NULL
> If I use:
> SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS actions
> FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
> GROUP BY Actions.locationid, Targets.locationid
> I get:
> Actions Actions.locationid Targets.locationid
> 1 1 NULL
> 1 2 NULL
> 1 3 NULL
> 3 NULL 1
> I want to combine both locationid columns in result giving:
> Actions locationid
> 4 1
> 1 2
> 1 3
> There are more columns than illustrated but if you the above can be
cracked,
> I'll be away!
> Cheers,
> Jack|||Hi

It sounds like it worked then!

Here is usable DDL and example data in case you need it again.

create table Targets (
targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY KEY,
locationid integer,
)

create table Actions (
actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY KEY,
targetid integer NULL,
locationid integer,
CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetId)
)

INSERT INTO Targets (locationid) VALUES (1)
INSERT INTO Targets (locationid) VALUES (1)

INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)

SELECT * FROM Targets

/*
targetid locationid
---- ----
1 1
2 1

(2 row(s) affected)
*/
SELECT * FROM Actions

/*
actionid targetid locationid
---- ---- ----
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL

(6 row(s) affected)
*/

-- Your attempt
SELECT A.locationid, T.locationid, COUNT(A.actionid) AS actions
FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
GROUP BY A.locationid, T.locationid

/*
locationid locationid actions
---- ---- ----
1 NULL 1
2 NULL 1
3 NULL 1
NULL 1 3

(4 row(s) affected)
*/

-- Your second attempt
SELECT ISNULL(A.locationid, T.locationid) AS Location,
COUNT(A.actionid) AS Actions_No
FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
GROUP BY ISNULL(A.locationid, T.locationid)

/* Gives
Location Actions_No
---- ----
1 4
2 1
3 1

(3 row(s) affected)
*/

John

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:498ib.4706$_54.349437@.newsfep2-win.server.ntli.net...
> John,
> Thanks for putting me on the right track. With ref to the example in my
> reply post I used:
> SELECT ISNULL(Actions.locationid, Targets.locationid) AS Location,
> COUNT(Actions.actionid) AS Actions_No
> FROM Actions LEFT OUTER JOIN
> Targets ON Actions.targetid = Targets.targetid
> GROUP BY ISNULL(Actions.locationid, Targets.locationid)
> All the other columns I want to count are in the Actions table so I just
> need to add them to the SELECT statement.
> Thanks again,
> Jack
> "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> news:Fu0ib.1525$_54.280845@.newsfep2-win.server.ntli.net...
> > Thanks John,
> > I didn't explain too well so I'll detail tables, releationships and what
> I'm
> > trying to do. I have managed to reduce & simplify the issue to two
> tables:-
> > Targets table which has columns:
> > target id - key identity autoincrement integer
> > locationid - integer
> > Actions table which has columns:
> > actionid - key identity autoincrement integer
> > targetid - integer
> > locationid integer
> > relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
> > Actions.targetid (I want results from all rows in Actions).
> > I want to count all rows from Actions and group by locationid combined
> from
> > both tables.
> > Targets content:
> > targetid locationid
> > 1 1
> > 2 1
> > Actions Content:
> > actionid targetid locationid
> > 1 NULL 1
> > 2 NULL 2
> > 3 NULL 3
> > 4 1 NULL
> > 5 1 NULL
> > 6 2 NULL
> > If I use:
> > SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS
actions
> > FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
> > GROUP BY Actions.locationid, Targets.locationid
> > I get:
> > Actions Actions.locationid Targets.locationid
> > 1 1 NULL
> > 1 2 NULL
> > 1 3 NULL
> > 3 NULL 1
> > I want to combine both locationid columns in result giving:
> > Actions locationid
> > 4 1
> > 1 2
> > 1 3
> > There are more columns than illustrated but if you the above can be
> cracked,
> > I'll be away!
> > Cheers,
> > Jack
>|||Thanks John,
Appreciate your informative close-out post and will certainly file for
reference.
Cheers,
Jack

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f891931$0$11446$afc38c87@.news.easynet.co.uk. ..
> Hi
> It sounds like it worked then!
> Here is usable DDL and example data in case you need it again.
> create table Targets (
> targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY
KEY,
> locationid integer,
> )
> create table Actions (
> actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY
KEY,
> targetid integer NULL,
> locationid integer,
> CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetId)
> )
> INSERT INTO Targets (locationid) VALUES (1)
> INSERT INTO Targets (locationid) VALUES (1)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
> INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
> INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
> INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)
> SELECT * FROM Targets
> /*
> targetid locationid
> ---- ----
> 1 1
> 2 1
> (2 row(s) affected)
> */
> SELECT * FROM Actions
> /*
> actionid targetid locationid
> ---- ---- ----
> 1 NULL 1
> 2 NULL 2
> 3 NULL 3
> 4 1 NULL
> 5 1 NULL
> 6 2 NULL
> (6 row(s) affected)
> */
> -- Your attempt
> SELECT A.locationid, T.locationid, COUNT(A.actionid) AS actions
> FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
> GROUP BY A.locationid, T.locationid
> /*
> locationid locationid actions
> ---- ---- ----
> 1 NULL 1
> 2 NULL 1
> 3 NULL 1
> NULL 1 3
> (4 row(s) affected)
> */
> -- Your second attempt
> SELECT ISNULL(A.locationid, T.locationid) AS Location,
> COUNT(A.actionid) AS Actions_No
> FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
> GROUP BY ISNULL(A.locationid, T.locationid)
> /* Gives
> Location Actions_No
> ---- ----
> 1 4
> 2 1
> 3 1
> (3 row(s) affected)
> */
>
> John

Combing 2 Queries into 1

I have spent several hours hurting my brain tring to solve the following. I am hoping one of you SQL gurus can help me out.

I have two tables (each with two fields):

Group with GroupName and GroupDescription

Here is some sample data:

HR HumanResources
IT Information Technology
Boston BostonOffice
NJ NewJerseyOffice

GroupMember with GroupName and UserID

Here is some sample data:

IT CMessineo
NJ CMessineo
Boston JSmith
IT JSmith

What I want is a single stored procedure that when passed a UserID will return a result set that lists all the groups and a 1 or ) if the UserID is a member of that group.

For example calling the procedure and passing CMessineo would produce this result:

HR 0
IT 1
Boston 0
NJ 1

Can this be done in a stored procedure?

This is what I have so far, but I am in over my head:

(
@.UserID varChar(40)
)
As
SELECT"GROUP".GROUPNAME, (SELECT ?
FROM"Group", GroupMember
Where"Group".GroupName = GroupMember.GroupName and GroupMember.UserID = @.UserID)

FROM"GROUP"

Thanks in advance,

ChrisI figured it out - it required an outer join (my first):

SELECT"Group".GroupName, COUNT(UserID) AS MEMBER
FROM"Group" Left Outer Join GroupMember
ON"Group".GroupName = GroupMember.GroupName and GroupMember.UserID=@.UserID
GROUP BY"Group".GroupName

combined merge + custom conflict resolution

I have what seems to be a unique case, but logically, would think that
someone out there is already doing this. I have the following configuration:
- merge replication
- column level tracking
- single pub./dist. server replicating with a single subscriber
- SQL Server 2000 Ent. on Windows 2000 Adv. Server
I have succesfully implemented replication, and data is merged properly in
both directions. However, when confilcts occurr, I want the ability to allow
those records that would merge accordingly (not in conflict), but for those
columns in conflict, I want the ability to run a stored procedure with my
proper business logic. Is there a way to do this? When using a stored proc
for conflict resolution, you are forced to return the "winning values" for
all the fields. This is where I lose the data that would have normally
replicated that was not in conflict. In my stored proc, I wish I had the
ability to see or read from a table the data values that would normally be
merged that were not in conflict, so I could supply them in my returned
recordset. I have the logic of reading both Subscriber and Publisher field
values for the current rowguid, but I don't know which of the two values (sub
or pub) has been edited so that it can merge.
Am I explaining this properly? I currently have an open instance with
Microsoft tech support ($250 !!), but they have yet to come back with a
solution. They have had the specs to my issue for a week now, and they have
not been able to resolve it. Maybe someone out there has done this. Thanks
in advance for all the help!
This type of logic is possible using merge replication, but you will have to
hack into the stored procedures that the merge agent uses to apply the
changes to the subscriber and publisher.
However, I think bi-directional transactional replication is a better choice
for implementing this form of custom business logic. When you use XCALL the
before and after images of the data will flow from the Publisher to the
Subscriber and you can incorporate logic to handle your conflicts this way.
So lets consider what a conflict is. A conflict is
1) a pk violation, trying to insert a PK value where a row with that PK
value already exists on the subscriber
2) trying to update a row on the Subscriber and instead of updating a single
row you update 0 or more than one rows.
3) trying to delete a row on the Subscriber and instead of deleting a single
row you delete 0 or more than one rows.
So, before your insert proc fires it will do an existence check. If the row
exists, it can instead do an update which will incorporate your custom
business logic. If the update ends up updating more than one row you have to
consider what is going on. This probably violates your database integrity,
but may not depending on how your database is set up or what you are trying
to accomplish. For instance consider you are replicating to audit table. A
row with a PK value of 1 may be inserted at the publisher and replicated to
the Subscriber. Then this row is deleted at the publisher, but this delete
is not replicated to the subscriber (remember the subscriber is an audit
table, so we need a record of that row). Then this row is readded with the
same PK (1) and replicate to the subscriber. If you haven't handled the
possibility of duplicate PK's you will have a problem.
Updates and deletes are simple as long as you can preserve the one to one
mapping of rows on the Publisher to the Subsriber. If you are doing a one to
many from the Publisher to the Subscriber it gets more complex, but it is
not impossible.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> I have what seems to be a unique case, but logically, would think that
> someone out there is already doing this. I have the following
configuration:
> - merge replication
> - column level tracking
> - single pub./dist. server replicating with a single subscriber
> - SQL Server 2000 Ent. on Windows 2000 Adv. Server
> I have succesfully implemented replication, and data is merged properly in
> both directions. However, when confilcts occurr, I want the ability to
allow
> those records that would merge accordingly (not in conflict), but for
those
> columns in conflict, I want the ability to run a stored procedure with my
> proper business logic. Is there a way to do this? When using a stored
proc
> for conflict resolution, you are forced to return the "winning values" for
> all the fields. This is where I lose the data that would have normally
> replicated that was not in conflict. In my stored proc, I wish I had the
> ability to see or read from a table the data values that would normally be
> merged that were not in conflict, so I could supply them in my returned
> recordset. I have the logic of reading both Subscriber and Publisher
field
> values for the current rowguid, but I don't know which of the two values
(sub
> or pub) has been edited so that it can merge.
> Am I explaining this properly? I currently have an open instance with
> Microsoft tech support ($250 !!), but they have yet to come back with a
> solution. They have had the specs to my issue for a week now, and they
have
> not been able to resolve it. Maybe someone out there has done this.
Thanks
> in advance for all the help!
>
|||being that I am only capable to connect at night, I was under the impression
that Merge Replication was my only option. Transaction Replication os for
constant connection, right? If I am wrong, please advise. As for the
remainder of your comments, I am not that familiar with Replication, to
understand everything you are referring to. If you know of any publications
or web sites that can help educate me, that would be great. So far, the web
has seemed to be kinda light on info that deals with the deep intricate
details regarding replication. It brushes lightly on the topic, but thats
it. Thanks so much for your assistance. I have been stuck with this for a
week now. Its frustrating.
"Hilary Cotter" wrote:

> This type of logic is possible using merge replication, but you will have to
> hack into the stored procedures that the merge agent uses to apply the
> changes to the subscriber and publisher.
> However, I think bi-directional transactional replication is a better choice
> for implementing this form of custom business logic. When you use XCALL the
> before and after images of the data will flow from the Publisher to the
> Subscriber and you can incorporate logic to handle your conflicts this way.
> So lets consider what a conflict is. A conflict is
> 1) a pk violation, trying to insert a PK value where a row with that PK
> value already exists on the subscriber
> 2) trying to update a row on the Subscriber and instead of updating a single
> row you update 0 or more than one rows.
> 3) trying to delete a row on the Subscriber and instead of deleting a single
> row you delete 0 or more than one rows.
> So, before your insert proc fires it will do an existence check. If the row
> exists, it can instead do an update which will incorporate your custom
> business logic. If the update ends up updating more than one row you have to
> consider what is going on. This probably violates your database integrity,
> but may not depending on how your database is set up or what you are trying
> to accomplish. For instance consider you are replicating to audit table. A
> row with a PK value of 1 may be inserted at the publisher and replicated to
> the Subscriber. Then this row is deleted at the publisher, but this delete
> is not replicated to the subscriber (remember the subscriber is an audit
> table, so we need a record of that row). Then this row is readded with the
> same PK (1) and replicate to the subscriber. If you haven't handled the
> possibility of duplicate PK's you will have a problem.
> Updates and deletes are simple as long as you can preserve the one to one
> mapping of rows on the Publisher to the Subsriber. If you are doing a one to
> many from the Publisher to the Subscriber it gets more complex, but it is
> not impossible.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> configuration:
> allow
> those
> proc
> field
> (sub
> have
> Thanks
>
>
|||While you can use bi-directional transactional replication in a disconnected
manner it is not really advisable to do so, as the conflict tracking and
resolving mechanisms are essentially non-existent.
If you get a conflict your distribution agent will fail and you will have to
resolve the conflict and restart the agent. If you have many conflicts this
is not acceptable, but if you have few conflicts or you can design your
replication solution to minimize conflicts this might be a solution for you.
Again, I don't know your data, data flow, or topology so I can't really
advise you on it. You know it more intimately than I so you should make the
decision on this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> being that I am only capable to connect at night, I was under the
impression
> that Merge Replication was my only option. Transaction Replication os for
> constant connection, right? If I am wrong, please advise. As for the
> remainder of your comments, I am not that familiar with Replication, to
> understand everything you are referring to. If you know of any
publications
> or web sites that can help educate me, that would be great. So far, the
web
> has seemed to be kinda light on info that deals with the deep intricate
> details regarding replication. It brushes lightly on the topic, but thats
> it. Thanks so much for your assistance. I have been stuck with this for
a[vbcol=seagreen]
> week now. Its frustrating.
> "Hilary Cotter" wrote:
have to[vbcol=seagreen]
choice[vbcol=seagreen]
the[vbcol=seagreen]
way.[vbcol=seagreen]
single[vbcol=seagreen]
single[vbcol=seagreen]
row[vbcol=seagreen]
have to[vbcol=seagreen]
integrity,[vbcol=seagreen]
trying[vbcol=seagreen]
A[vbcol=seagreen]
to[vbcol=seagreen]
delete[vbcol=seagreen]
the[vbcol=seagreen]
one[vbcol=seagreen]
one to[vbcol=seagreen]
is[vbcol=seagreen]
properly in[vbcol=seagreen]
to[vbcol=seagreen]
my[vbcol=seagreen]
stored[vbcol=seagreen]
for[vbcol=seagreen]
normally[vbcol=seagreen]
the[vbcol=seagreen]
normally be[vbcol=seagreen]
returned[vbcol=seagreen]
values[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
|||I appreciate your feedback Hilary. I do think that bi-directional
transactional replication is not an option for me, due to the number of
conflicts anticipated. I hope that Microsoft's support people will help me
find the right answer. Its costing me $250, so they better! :-)
Thanks again. I enjoy your particiapation on this discussion board. Its a
great educational tool!
"Hilary Cotter" wrote:

> While you can use bi-directional transactional replication in a disconnected
> manner it is not really advisable to do so, as the conflict tracking and
> resolving mechanisms are essentially non-existent.
> If you get a conflict your distribution agent will fail and you will have to
> resolve the conflict and restart the agent. If you have many conflicts this
> is not acceptable, but if you have few conflicts or you can design your
> replication solution to minimize conflicts this might be a solution for you.
> Again, I don't know your data, data flow, or topology so I can't really
> advise you on it. You know it more intimately than I so you should make the
> decision on this.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> impression
> publications
> web
> a
> have to
> choice
> the
> way.
> single
> single
> row
> have to
> integrity,
> trying
> A
> to
> delete
> the
> one
> one to
> is
> properly in
> to
> my
> stored
> for
> normally
> the
> normally be
> returned
> values
> a
> they
>
>

Combine Multiple Results into 1 RecordSet

Hello All
I have the following SPROC Below which I want to return the results of the 3
Querries in a single record Set which I can use in my webapp
/ ****************************************
****************
CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
AS
SELECT Count(*) As Total FROM vSQLInv_VersionString
SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
SELECT Count(*) As Valid FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
GO
****************************************
***************/
-- Desired Results --
Total Vulnerable Valid
80 5 75
Thanks
StuartSELECT Count(*) As Total,
SUM(CASE WHEN Status = 'Vulnerable' OR Status = 'EOF' THEN 1 ELSE 0 END)
As Vulnerable ,
SUM(CASE WHEN Status <> 'Vulnerable' THEN 1 ELSE 0 END) As Valid
FROM vSQLInv_VersionString
Jacco Schalkwijk
SQL Server MVP
"Stuart Shay" <sshay@.j51.com> wrote in message
news:umqEkOZMFHA.2384@.tk2msftngp13.phx.gbl...
> Hello All
> I have the following SPROC Below which I want to return the results of the
> 3 Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>|||SELECT
(
SELECT Count(*) FROM vSQLInv_VersionString
) AS Total ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
) As Vulnerable ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
) As Valid
FROM
vSQLInv_VersionString
Cheers,
Greg Jackson
PDX, Oregon|||SELECT Count(*) As Total,
Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as Vulnerable,
Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
FROM vSQLInv_VersionString
"Stuart Shay" wrote:

> Hello All
> I have the following SPROC Below which I want to return the results of the
3
> Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>
>|||Thanks & Have A GREAT Day !!!!!
Stuart
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:41A33FEB-3805-411F-A03D-459849349C05@.microsoft.com...
> SELECT Count(*) As Total,
> Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as
> Vulnerable,
> Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
> FROM vSQLInv_VersionString
>
> "Stuart Shay" wrote:
>

Combine and

I have a table which has the following columns.

id - int(11)
catid - int(11)
title - varchar(60)
content - text
parent - int(11)
postdate - datetime
user - int(11)
view - int(11)
email - char(1)
emailed - char(1)

Here's what I'm trying to accomplish.

This table is for a forum. If a user posts a question and selects to be automatically email the email column will be set to 'Y'

So when a user responds to the post the emailed column will be 'Y'

Every hour or so I will do a cron job to send out an email to the original poster that he/she has a reply to their post.

I want to get a list of those id's that have email 'Y' and emailed = 'Y'

I can get those queries separtely below, but want to do it in one. how can I accomplish this...

my queries.

SELECT * FROM forum_tbl where parent = 0 and email = 'Y' // original post where user wants emails

SELECT * FROM forum_tbl where parent > 0 and emailed = 'N' // reply to post where email has not been sent.

I'm using MySQL 4.0.20a-max

Thank you.You can use the UNION of both selects to get the result set:

select ....
UNION
select ....

or you can use

SELECT *
FROM
forum_tbl
WHERE
(parent = 0 and email = 'Y') OR (parent > 0 and emailed = 'N')|||You can use the UNION of both selects to get the result set:

select ....
UNION
select ....

or you can use

SELECT *
FROM
forum_tbl
WHERE
(parent = 0 and email = 'Y') OR (parent > 0 and emailed = 'N')|||The outcome puts the two selects together but I need to eliminate some of the information, like an intersect, but I can't do that in MySQL.

The first select has the ID I want. The second had the parent ID.

I need to match only those.

Make sense?

Thanks.|||Can you post an examle of what you want done? a few records in the table and the result set you are looking for, maybe that way I can help out better.|||id catid title content parent postdate user views email emailed
19 3 test test con 0 2004-12-07 00:00:00 1 3 Y NULL

24 2 test again 0 2004-01-08 12:52:04 1 11 Y NULL
25 2 test ing 24 2004-12-08 00:00:00 1 0 NULL N

This is the outcome of the union your posted earlier.

The outcome I'm looking for is that it only should show id 24 nothing else because it is the only one that has a reply that has not been "emailed".

Thanks.

Combine 2 rows from derived table into 1 row w/o repeating query?

I'm trying not to use a temp table, but i may have to do so..

i have a derived table that makes the following results:

ID Status Name

2 1 "A"

2 2 "B"

I want to get the following:

ID Name1 Name2

2 "A" "B"

but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.

Here it is,

Code Snippet

Create Table #data (

[ID] int ,

[Status] int ,

[Name] Varchar(100)

);

Insert Into #data Values('2','1','A');

Insert Into #data Values('2','2','B');

Select

Id

,max(case when Status=1 Then [Name] end) [name1]

,max(case when Status=2 Then [Name] end) [name2]

from

(

Select * from #data -- Your Derived Table

) as Data

Group By

Id

|||

The solution will work, I just needed to think about how to expand it for more columns, but I got it now.

If it's very very slow, I will try something with a CTE - I think that'll work as well.

|||

The CTE was 50-60% faster than the other route! but that method is also useful if using sql2000.|||

Yeah a CTE is going to be the way to go on this in 2005 for sure. Never been a fan of using temp tables and I avoid them when I can. So here's my own variation on the above sample...

Code Snippet

SELECT
[ID],
max(case when Status=1 Then [Name] end) [name1],
max(case when Status=2 Then [Name] end) [name2]

from (
SELECT 2 As [ID], 1 As Status, 'A' As [Name]
UNION SELECT 2, 2, 'B'
) AS Data
GROUP BY [ID]

combine 2 queries

Hi,

I have 2 queries that I need to join. I have the following tables:
attendancelog :
headerid
reportmonth

attlogstuds:
headerid
sid

class:
sid
class
status
yearcode
logdate
cdate
mid

The result must return all the classes that appear in query2 but not
in query1.
I am not sure how to join the 2 queries.

Help will be appreciated :-)

Thanks

QUERY1

select sid from
attlogstuds studs
inner join
attendancelog attlog
on studs.headerid=attlog.headerid
where reportmonth=10

query2
-- students learning excl. studs left before 1th oct.

select class.SID from class

left outer JOIN ( select * from class where yearcode=26 and status=6

and ( logdate <'20041001' or CDate
< '20041001' )

) c6 ON c6.sid = class.sid and c6.mid=class.mid
and c6.cdate >= class.cdate

where class.yearcode=26 and class.status in (3,5) and
class.cdate<'20041101' and c6.sid is null[posted and mailed, please reply in news]

avital (avitalnagar@.walla.co.il) writes:
> The result must return all the classes that appear in query2 but not
> in query1.
> I am not sure how to join the 2 queries.

Sounds like the EXCEPT operator would have come in hand, but that
operator is not in SQL Server.

It's always a good idea to include the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it possible to post a tested solution.

The below is an untested solution. I may have missed something but
it seems to me that a simple NOT EXISTS clause is what you need.

select class.SID
from class
left JOIN (select *
from class
where yearcode=26
and status=6
and ( logdate <'20041001' or
CDate < '20041001' )
) c6
ON c6.sid = class.sid
and c6.mid = class.mid
and c6.cdate >= class.cdate
where class.yearcode=26
and class.status in (3,5)
and class.cdate<'20041101'
and and c6.sid is null
and NOT EXISTS (select *
from attlogstuds studs
join attendancelog attlog
on studs.headerid=attlog.headerid
where studs.sid = class.sid)
--
Erland Sommarskog, SQL Swhere reportmonth=10erver MVP, esquel@.sommarskog.se

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

COM+ Application server has problems with SQL2000 both Win2003

Hi,
I want to run the following scenario:
1 server with COM+ components do DTC transactions against a SQL-2000
database. While this runs perfectly under Windows 2000, this does not work
at all under Windows 2003. I have checked all MSDN stuff, run the DTCTESTER
utilitly and came to the result, that is must be some network problem.
But: I have no idea how to resolve it, both are set to use TCP/IP, no
firewall is active, ping and drive mapping (in both directions) is
successful, also retrieving data via query analyzer is possible. Now which
setting must I change, so these machines can do those DTC transactions?
Regards
Is netbios name resolution working in both directions?
Can you make connections using ISQL.exe both ways without errors?
Did you restrict the rpc ports on the firewall for MSDTC?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

COM+ Application server has problems with SQL2000 both Win2003

Hi,
I want to run the following scenario:
1 server with COM+ components do DTC transactions against a SQL-2000
database. While this runs perfectly under Windows 2000, this does not work
at all under Windows 2003. I have checked all MSDN stuff, run the DTCTESTER
utilitly and came to the result, that is must be some network problem.
But: I have no idea how to resolve it, both are set to use TCP/IP, no
firewall is active, ping and drive mapping (in both directions) is
successful, also retrieving data via query analyzer is possible. Now which
setting must I change, so these machines can do those DTC transactions?
RegardsIs netbios name resolution working in both directions?
Can you make connections using ISQL.exe both ways without errors?
Did you restrict the rpc ports on the firewall for MSDTC?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.