Return Duplicate Rows

I want to select all my columns that have the same number twice....but i dont know how....I tried doit it this way

Select Distinct sti.parent_topic_id, st.description_short, sti.sort_order, count(st.description_short)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, st.description_short, sti.sort_order

--Having count(sti.sort_order) > 1

Order By parent_topic_id

I even tried sti.sort_order = sti.sort_order and that does not work eithier

But it returns everything...when i uncomment the having clause it does not return anything....I want my end result to be

2 TheName 10 1

2 SomthingElse 10 1

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

select *
from sam_topic_items sti

inner join sam_topic st

on sti.topic_id = st.id
inner join
(
select sti.topic_id
from sam_topic_items sti
inner join sam_topic st
on sti.topic_id = st.id
where st.type = 'Topic'
group by sti.topic_id
having count(*) > 1
) d
on st.topic_id = d.topic_id|||

Thanks 4 the response but this returns all my rows with the sam ID's instead of the same sort order...I have a table like this

id sort_order

1 2

1 2

1 1

1 3

I only want to return.......

id sort_order

1 2

1 2

I thought if i did it like you had it but tweak it a little it would work....but it still returns all the rows

select *

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

inner join

(

select sti.parent_topic_id, sti.sort_order

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

where st.type = 'Topic'

group by sti.parent_topic_id, sti.sort_order

having count(*) > 1

) d

on sti.sort_order = d.sort_order

|||

I think you need to join by both sort_order and topic_id.

A more basic question is if you know where the duplication is coming from.

Are there multiple records in the topic table (of type 'topic') which have the same ID?|||There are multiple items in the topic_item tables that have the same number in thier sort order...and I only want to select those id's that have the same number in there sort_order....my topic table has a PK of id and my topic_items table has a PK of topic ID and a PK of parent_topic_id.....|||

When I say primary key I mean a column (or columns) which uniquely identify a single record in the table. That's means there cannot be duplicates in the primary key. I am interested in topic_id vs. parent_topic_id are these the same field or related fields. They look like foreign keys (fields which link to a primary key on another table).

The main issue in your query seems to be multiple records in the topic_items table with the same topic_ID (or parent_topic_ID I'm not sure) and the same sort order.

If this is the case then:

SELECT topic_ID, sort_order
FROM topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1

should give a list of these records (but only 1 instance of each combination). Then joining it back to the other tables should give the record list you want.

SELECT ST.ID, STI.description_short, STI.sort_order
FROM (
SELECT topic_ID, sort_order
FROM sam_topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1
) TS
INNER JOIN sam_topic_items STI
ON (TS.topic_ID = STI.topic_ID) AND
(TS.sort_order = STI.sort_order)
INNER JOIN sam_topic ST
ON (STI.topic_id = ST.id)
WHERE (ST.type = 'Topic')
ORDER BY ST.ID

This is all testing by topic_id. If it should be parent_topic_id then change it to this throughout the query.

There is also still the question of what the type field does in selecting records (as I say do you have records other than type='Topic' in the sam_topic table?). Depending upon exactly what this does it may cause problems with the SQL to pick up the duplicates.

|||

Yes there is other records other than "Topic" there is also "ROOT" but I am just worrying about "Topic" and nothing else....This is how I had to change the query it works now partially

SELECT STI.parent_topic_id, ST.description_short, STI.sort_order

FROM (

SELECT parent_topic_id, sort_order

FROM sam_topic_items

GROUP BY parent_topic_id, sort_order

HAVING count(sort_order) > 1

) TS

INNER JOIN sam_topic_items STI

ON (TS.sort_order = STI.sort_order)

INNER JOIN sam_topic ST

ON (STI.topic_id = ST.id)

WHERE (ST.type = 'Topic')

ORDER BY STI.parent_topic_id

but it still does not return only the rows with duplicate sort order for a particular parent_topic_id this query returns

id description_short sort_order

2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1.....thanks 4 the help though!!!

See how it returns all these results for the same thing?...in my table there is only one "<1mm" for the parent_topic_id of 2131 but this returns like 20 of them...I dont know how it did that?

|||

Change your having clause to:

HAVING count(*) >1

|||Yea i did that I still get the same result though?|||

Yeah I get it. I didn't see the whole thing.

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

You need to take the description name out of the group by, otherwise you will count the values with the same id but different descriptions as different rows. To count the number of occurences of a different description for the same id you need to take the description out of your group.

|||Ok here is a better question this query

Select Distinct sti.parent_topic_id, st.Type, sti.sort_order, count(sti.sort_order)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, sti.sort_order, st.Type

Having count(sti.sort_order) > 1

Order By parent_topic_id

Returns this data exactly

parent_topic_id type sort_order the last column is the number of occurences that the sort_order has.....like if you look at the first row of data.....there are 3 occurences of 0 for parent_topic_id 2334...

2334 TOPIC 0 3
2398 TOPIC 32 2
2399 TOPIC 16 2
2430 TOPIC 22 2
2447 TOPIC 26 2
2447 TOPIC 12 2
2447 TOPIC 15 2
2452 TOPIC 1 3
2505 TOPIC 37 2
2505 TOPIC 52 2
2505 TOPIC 3 2
2507 TOPIC 6 2
2526 TOPIC 32 2
2549 TOPIC 54 2
2549 TOPIC 52 2
2562 TOPIC 0 2
2722 TOPIC 2 2
2880 TOPIC 0 2
2995 TOPIC 0 2
2997 TOPIC 0 2
3000 TOPIC 0 2
3001 TOPIC 0 2
3040 TOPIC 6 2
3152 TOPIC 26 2
3244 TOPIC 0 3
3524 TOPIC 0 2
3605 TOPIC 0 2
3638 TOPIC 0 2
3721 TOPIC 2 2
3723 TOPIC 1 2
4082 TOPIC 4 2
4100 TOPIC 1 3
4181 TOPIC 11 2
4220 TOPIC 0 2
4261 TOPIC 2 2
4262 TOPIC 1 2
4302 TOPIC 3 2
4302 TOPIC 1 2
4302 TOPIC 2 2
4439 TOPIC 0 2
5029 TOPIC 2 3
5032 TOPIC 2 4
5042 TOPIC 2 6
5042 TOPIC 11 2
5071 TOPIC 2 3
5297 TOPIC 2 2
5297 TOPIC 3 2
5297 TOPIC 1 2
5368 TOPIC 52 2
5368 TOPIC 3 2
5368 TOPIC 36 2
5368 TOPIC 48 2
5368 TOPIC 37 2
5368 TOPIC 54 2
5849 TOPIC 45 2
5882 TOPIC 16 2
5882 TOPIC 42 2
5882 TOPIC 54 2
5882 TOPIC 11 2
5882 TOPIC 55 3
5882 TOPIC 60 2
5882 TOPIC 40 2
6032 TOPIC 0 3
6137 TOPIC 0 2
8897 TOPIC 3 2
9461 TOPIC 2 2
9461 TOPIC 1 2
9598 TOPIC 11 2
9608 TOPIC 54 2
10345 TOPIC 0 2
10366 TOPIC 2 2
10627 TOPIC 17 2
11992 TOPIC 42 2
12081 TOPIC 4 2
12083 TOPIC 3 3
12298 TOPIC 20 2
12701 TOPIC 0 2
12716 TOPIC 9 4
12717 TOPIC 4 2
12750 TOPIC 6 2
12755 TOPIC 3 2
12760 TOPIC 1 2
12760 TOPIC 3 2
12760 TOPIC 4 2
12760 TOPIC 2 2
12926 TOPIC 5 3
12990 TOPIC 8 6
12998 TOPIC 9 4
12998 TOPIC 6 2
13001 TOPIC 2 2
13069 TOPIC 5 2
13069 TOPIC 1 2
13132 TOPIC 8 2
13134 TOPIC 4 2
13349 TOPIC 5 3
13429 TOPIC 4 2
13429 TOPIC 19 3
13429 TOPIC 1 2
13429 TOPIC 3 2
13429 TOPIC 6 2

Now using the above query how would i write a sub query to use the data return in this result to show some other data in the same result set?

|||

Like what? I thought thats what you wanted to show?

Your result set is going to be grouped.

It will only have a single row for each group.

You can do simple tricks like putting an aggregate function like MIN function to show things like the description but you will only show a single description for the grouped row.

What is it exactly that you are trying to show?

Have you considered an sp rather than a single query?

ALSO, please not that you DO NOT need st.Type in the grouping or the field list since your where criteria clearly states that it has to be equal to 'TOPIC'.

|||

thanks liz.....

If you look at the results for the query....and look at the id of 2334 it has 3 instances of 0 in that id....I want to be able to show

2334 TheNAme 0

2334 The OtherName 0

2334 The OtherName 0

I want to show the id the description and the sort order.....Only for those duplicate sort orders and thats it.....I could write a vb or C# app to do it....but i need to learn sql, haha as you can see i dont know it well!!! thanks!!

|||

It is real ugly but, if you are using 2 values you need to convert them to a char string and use them in the where clause together in the outer query...

Select sti.parent_topic_id, st.description_short, sti.sort_order
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order) IN
(
Select convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order)
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where st.type = 'Topic'
Group By sti.parent_topic_id, sti.sort_order, st.Type
Having count(sti.sort_order) > 1
Order By parent_topic_id
)

I don't know if this will execute properly because I don't have the right tables to test it but it is the general idea...

|||

First off please explain to me what the convert method did? I need to know what that did to the query to make it act like so....

BUT you are a WIZ!!!!!!!!! unless I am just that mentally slow THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!......

return duplicate records

Hello experts,

I'm trying the run the following query with specific intentions.

I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.

I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?

Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);

alexAlex,

There are a few solutions. Two are (might have typos, but you should be
able to get the idea):

select Employee_last_name, Employee_first_name
from tbl_employee
join (
select '009' as id union all
select '008' as id union all
select '007' as id union all
select '007' as id union all
select '006' as id
) as IDs
on IDs.id = tbl_employee.employee_id

or to make the specification of ids simpler:

declare @.ids varchar(1000)
set @.ids = '009008007007006'
declare @.idlength int
set @.idlength = 3

select Employee_last_name, Employee_first_name
from tbl_employee
join a_permanent_table_of_integers_from_0_to_whatever as Nums
on employee_id = substring(@.ids,@.idlength*n+1,@.idlength)
and n < len(@.ids)/@.idlength
-- [n] is the column name for the permanent table and should
-- be that tables primary key

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
alex wrote:

Quote:

Originally Posted by

Hello experts,
>
I'm trying the run the following query with specific intentions.
>
I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.
>
I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?
>
Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);
>
alex
>

|||>I would like the query to return 5 results; i.e., 4 distinct and one duplicate. <<

The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:

SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'|||--CELKO-- (jcelko212@.earthlink.net) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>I would like the query to return 5 results; i.e., 4 distinct and one

duplicate. <<

Quote:

Originally Posted by

>
The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:
>
SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'

Joe, I thought you knew SQL? This query will not return the results
that Alex was asking for.

Why is left as an exercise to the reader.

--
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

Return Distinct Values from Stored Procedures

I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!

Is OrderNum contained in the result set returned by the sp rptOpenOrderLines? If yes, you may need something likeDataView.RowFilter:

http://msdn2.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx

If no, there seems to be no way to do this without checking back in database.

|||Great! Thanks lori_Jay! Although I don't yet have the exact solution, this looks like a good lead toward the final product. That article is pretty useful and I appreciate the help. I've spent days on developing these datalist/gridview controls and its about time that it should be done. I'll post back with the solution when I'm complete with this part.|||

Please See:

http://forums.asp.net/thread/1371660.aspx

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return DISTINCT Values

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below?

Code Snippet

SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType

FROM @.GResults r

LEFT OUTER JOIN

ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY GCustodian, GCustodianAccount, GAssetType;

Thanks.

You can apply the distinct key word after the select clause – if all the column set values are duplicate,

Code Snippet

SELECT Distinct

CommentImage AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

If column set values (CommentImage, GCustodian, GCustodianAccount, GAssetType) are not unique you can apply group functions – it may cauase some data lose.

Code Snippet

SELECT Distinct

Max(CommentImage) AS ViewComment

, r.GPositionID

, Max(GCustodian)

, Max(GCustodianAccount)

, Max(GAssetType)

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

Group BY

r.GPositionID

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

CommentImage has dataType 'Image'

Using DISTINCT with it gives the below error...

The text, ntext, or image data type cannot be selected as DISTINCT.

|||

The following query might help you,

Select

(select Top 1 CommentImage from ReconComments s where s.GPositionID=data.GPositionID),

, GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

From

(

SELECT Distinct

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

) as data

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

Get this error...

The text, ntext, and image data types are invalid in this subquery or aggregate expression

|||Yes SQL Server 2000 cause this error..let me check the solution for this.|||r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.

Using SLQ Server 2005

thanks.

|||

If you really use SQL Server 2005 (check using => print @.@.version) database then the following query work fine,

MS Recommandation: Change your Image datatype to varbinary(max)

Code Snippet

SELECT Distinct

Cast(CommentImage as varbinary(max)) AS ViewComment

, r.GPositionID

, GCustodian

, GCustodianAccount

, GAssetType

FROM

@.GResults r

LEFT OUTER JOIN ReconComments cm

ON cm.GPositionID = r.GPositionID

WHERE

r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)

ORDER BY

GCustodian

, GCustodianAccount

, GAssetType;

|||

When I click the 'Help' > 'About' link it tells me its Microsoft SQL Server 2005.

Using

print @.@.version

tells me this...

Microsoft SQL Server 2000 - 8.00.878 (Intel X86)

|||

That means you connected SQL Server 2000 server from the Management Studio (2005 Client tool).

Let me clarify where the images are stored - is it in different table (ReconComments) .

Is there any possibilty to have duplicate images for one GPositionID.

|||Sorry - it is possible for one GPositionID to have duplicate images.|||

GPositionID and GCustodian are in the one table.
CommentImage is from a related table.

There is a M:M relation.

Here's the tables structure:

RComments Tbl:

RCommentsID int PK,
CommentImage image,
GPositionID int FK

@.GResults Tbl:

GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)

Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>
You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>
|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,[vbcol=seagreen]
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client[vbcol=seagreen]
it
>
>
|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>
|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with[vbcol=seagreen]
mode.[vbcol=seagreen]
works[vbcol=seagreen]
If
>
sql

Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client
it
>
>|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with
mode.
works
If
>