Multitable Select

I have 2 tables involved in a select statement that I cannot figure out quite how to do.

Table 1- GroupedItems
int SharedId
int GroupId

Table 2- SharedItems
int SharedId
int ItemId

In Table 1 SharedId is unique
In Table 2 ItemId is unique

Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId

Example
Table 1
1 - 1
2 - 1
3 - 2

Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5

What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId

If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.

You can use something like this query in 2005 to get a unique set of items:

create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go

select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1

|||Thank you for your help.

This is exactley what I needed.
|||

More straight forward way to write the query is to do below:

select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)

And depending on your indexes/data this might give better performance than the ROW_NUMBER approach. For this particular example, the above approach will be twice as fast as the ROW_NUMBER approach. And if you have an index on say sharedId then the performance difference will be more significant.

|||True enough. I am always just a bit concerned about duplicate and ordering so I just defaulted to doing it that way. I should have thought about this better way since there is a single value to correlate with. Thanks!|||Thanks to both of you.

This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row number

Multitable Select

I have 2 tables involved in a select statement that I cannot figure out quite how to do.

Table 1- GroupedItems
int SharedId
int GroupId

Table 2- SharedItems
int SharedId
int ItemId

In Table 1 SharedId is unique
In Table 2 ItemId is unique

Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId

Example
Table 1
1 - 1
2 - 1
3 - 2

Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5

What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId

If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.

You can use something like this query in 2005 to get a unique set of items:

create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go

select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1

|||Thank you for your help.

This is exactley what I needed.|||

More straight forward way to write the query is to do below:

select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)

And depending on your indexes/data this might give better performance than the ROW_NUMBER approach. For this particular example, the above approach will be twice as fast as the ROW_NUMBER approach. And if you have an index on say sharedId then the performance difference will be more significant.

|||True enough. I am always just a bit concerned about duplicate and ordering so I just defaulted to doing it that way. I should have thought about this better way since there is a single value to correlate with. Thanks!|||Thanks to both of you.

This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row numbersql

Multitable query

Hi there,

I have what seemed to look a simple qeury but I get double results or not enough.

There are 3 tables

ToolboxesToolsToolsInBoxes* ToolboxID* ToolID* ToolboxID* AllowedSizeID* SizeID* ToolID* Name* Name* Price1WhenPutInToolBox* #toolsInBox* PriceTool1* Price2WhenPutInToolBox* PriceBox1* PriceTool2* DateAdded* PriceBox2* InStock* DateRemoved* InStock,,,,,,,,,

some data for testing

Tools
ToolID SizeID Name PriceTool1 PriceTool2
1 2 NameTool1 4 5
2 2 NameTool2 2 3
3 2 NameTool3 22 25
4 3 NameTool4 9 14
5 2 NameTool5 33 36
6 2 NameTool6 23 27
7 3 NameTool7 7 7

ToolBoxToolboxIDAllowedSizeIDName#toolsInBoxPriceBox1PriceBox212ToolBox1210011023ToolBox2115016032ToolBox31200200

ToolsInBoxesToolboxIDToolIDPrice1WhenPutInToolBox Price2WhenPutInToolBox DateAddedDateRemoved11101511-11-2007n/a1281310-11-2007n/a15404512-10-200713-10-20072491409-09-2007n/a328813307-07-2007n/a

Now when I want to modify ToolBox1 (Size = 2) I would like to get a list of the Tools already in the toolbox AND the available tools that fit the AllowedSize for that toolbox in ONE table so it would be easy to add/remove tools from the toolbox

ToolboxID=1 (Size = 2)InToolBoxToolIDNamePrice1WhenPutInToolBox Price2WhenPutInToolBoxPriceTool1PriceTool2DateAddedDateRemovedYes1NameTool110154511-11-2007n/aYes2NameTool28132310-11-2007n/aavailable3NameTool3n/an/a2225n/an/aavailable5NameTool54045333612-10-200713-10-2007available6NameTool6n/an/a2327n/an/a

when a tool is removed from toolbox it becomes available again and keeps all information from that ToolBox

for ToolBoxID = 2 (Size = 3)InToolBoxToolIDNamePrice1WhenPutInToolBox Price2WhenPutInToolBoxPriceTool1PriceTool2DateAddedDateRemovedYes4NameTool4182891409-09-2007n/aavailable7NameTool7141477n/an/a

A tool can be put in many toolboxes

for ToolBoxID = 3 (Size = 2)InToolBoxToolIDNamePrice1WhenPutInToolBox Price2WhenPutInToolBoxPriceTool1PriceTool2DateAddedDateRemovedYes2NameTool2881332307-07-2007n/aavailable1NameTool1n/an/a45n/an/aavailable3NameTool3n/an/a2225n/an/aavailable5NameTool5n/an/a3336n/an/aavailable6NameTool6n/an/a2327n/an/a

I tried many, many queries, but not a good result.

I simple words it should be somthing like :

SELECT * FROM ToolsInBoxes WHERE ToolsInBoxes.ToolBoxID = (xx)

AND

( SELECT * FROM Tools WHERE Tools.Size = ToolBox(xx).Size

AND Tools NOT IN ToolsInBoxes WHERE ToolsInBoxes.ToolBoxID = (xx) )

I would appriciate any help

Best regards,

Bonaparte

1)Get a list of tools that already in the toobox

Select * from tools where ToolID IN(Select tooID from ToolsInBoxes where ToolBoxID=2)

2)Available Tools--
Select t.* from Tools t
inner join Toolbox tb ON tb.AllowedSizeID=t.SizeID AND AllowedSizeID=2
And t.ToolID NOT IN(Select tooID from ToolsInBoxes where ToolBoxID=2)

3) Since you want all the tools in one table you can create a field to differentiate the two queries and Union them

Select *, Flag="Tools Already in Box" from tools where ToolID IN(Select tooID from ToolsInBoxes where ToolBoxID=2)
UNION ALL
Select t.*,Flag="Available Tools" from Tools t
inner join Toolbox tb ON tb.AllowedSizeID=t.SizeID AND AllowedSizeID=2
And t.ToolID NOT IN(Select tooID from ToolsInBoxes where ToolBoxID=2)

Hope that helps and if that anwered your question please mark as answer.

|||

Thanks for your quick reply

3)Since you want all the tools in one table you can create a field to differentiate the two queries and Union them

So far so good, but I also want to have displayed the additional fields from ToolsInBox ( like Price1WhenPutInBox, DateAdded, ... ) in the same table (row).

like in the examples

and this can not be done with UNION

All queries combined using a UNION operator must have an equal number of expressions in their target list

Best regards,

Bonaparte

|||

This will give you something to start with although I was not able to understand your results in some cases..

SET dateformat dmygoDECLARE @.ToolsTABLE(ToolIDINT, SizeIDINT,Name VARCHAR(50), PriceTool1DECIMAL(10,2), PriceTool2DECIMAL(10,2))INSERT INTO @.ToolsSELECT 1, 2 ,'NameTool1' , 4 , 5UNIONALLSELECT 2, 2 ,'NameTool2' , 2 , 3UNIONALLSELECT 3, 2 ,'NameTool3' , 22 , 25UNIONALLSELECT 4, 3 ,'NameTool4' , 9 , 14UNIONALLSELECT 5, 2 ,'NameTool5' , 33 , 36UNIONALLSELECT 6, 2 ,'NameTool6' , 23 , 27UNIONALLSELECT 7, 3 ,'NameTool7' , 7 , 7DECLARE @.ToolBoxTABLE (ToolboxIDINT, AllowedSizeIDINT,Name VARCHAR(50), [#toolsInBox]INT, PriceBox1DECIMAL(10,2), PriceBox2DECIMAL(10,2))INSERT INTO @.ToolBoxSELECT 1, 2,'ToolBox1', 2, 100, 110UNIONALLSELECT 2, 3,'ToolBox2', 1, 150, 160UNIONALLSELECT 3, 2,'ToolBox3', 1, 200, 200DECLARE @.ToolsInBoxesTABLE (ToolboxIDINT,ToolIDINT, Price1WhenPutInToolBoxDECIMAL(10,2), Price2WhenPutInToolBoxDECIMAL(10,2), DateAddeddatetime,DateRemovedDATETIME)INSERT INTO @.ToolsInBoxesSELECT 1, 1, 10, 15,'11-11-2007',NULLUNIONALLSELECT 1, 2, 8, 13,'10-11-2007' ,NULLUNIONALLSELECT 1, 5, 40, 45,'12-10-2007','13-10-2007'UNIONALLSELECT 2, 4, 9, 14,'09-09-2007',NULLUNIONALLSELECT 3, 2, 88, 133,'07-07-2007',NULLDECLARE @.ToolBoxIDINT, @.SizeIDINTSELECT @.ToolBoxID = 1, @.SizeID=2SELECT T.toolid, T.Name, Price1WhenPutInToolBox,TIB.Price2WhenPutInToolBox, T.PriceTool1, T.PriceTool2, TIB.DateAdded, TIB.DateRemovedFROM @.Tools TJOIN @.ToolsInBoxes TIBON T.ToolID = TIB.ToolIDWHERE TIB.ToolboxID = @.ToolBoxIDUNION SELECT T.toolid, T.Name , TIB.Price1WhenPutInToolBox, TIB.Price2WhenPutInToolBox, T.PriceTool1, T.PriceTool2, TIB.DateAdded, TIB.DateRemovedFROM @.Tools TLEFTJOIN @.ToolsInBoxes TIBON T.ToolID = TIB.ToolIDWHERE T.SizeID = @.SizeIDAND NOT EXISTS(SELECT *FROM @.ToolsInBoxes T2WHERE T2.ToolId = T.ToolIdAND ToolboxID = @.ToolBoxID)

|||

Almost there ..

AND NOT EXISTS(SELECT *FROM @.ToolsInBoxes T2WHERE T2.ToolId = T.ToolIdAND ToolboxID = @.ToolBoxID)

displays 2 much,

the table : ToolsInBoxes can contain the ToolID more than ONES, from different ToolBoxes or deleted from ToolBox

pe. ToolID = 2 is used in ToolBox 1 and 3

The current UNION-part displays both.

I want ONLY the available TOOLS (with the good Size) that are NOT used yet.

I try to explain in plain text:

I have a number of Tools with Size, Price etc

I have a number of ToolBox, with Price, AllowedSize etc

I have a TABLE that contains de ToolsInBoxes, from ALL ToolBoxes, With ToolID, Price1WhenPutInToolBox, DateAdded etc

----------------------

Now lets say from the sapmle ToolboxID = 1:

In myToolBox are now 2 Tools from Size 2

1- Show me the ToolsInBox fromToolBox (that's OK, part 1 of the UNION)

a => ToolID = 1

b => ToolID = 2

c ToolID = 5 is removed so DO NOT SHOW, but the ToolID is back available to RE-use

2- Show me the Tools that are available for ToolBox

a => ToolID = 3

b => ToolID = 6

c => ToolID = 5 (was removed, so is available again)

--------------

As you can see ToolID = 2 is also in ToolBox 3 and there is where the problem is with the part 2 of the UNION.

Tools Size 2 (1,2,3,5,6)

Tools in ToolBox 1 (1,2)

SHOW ME

1- Tools in ToolBox = 1,2

2 - Tools available for THIS ToolBox = 3,5,6

That's all

Hope I explained it better this time

Best regards,

Bonaparte

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004sql

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ? Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time?
Or does SQL do this automatically?
Thanks
Sean
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.c om...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ? Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time?
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

multi-table join problem

I have three tables that all contain different types of data but have a customer name in common. I can join the three of them into one view that gives me an overview of what issues they are having by using the following query:

SELECT dbo.ProjectContentCount.SiteName AS SiteContent, dbo.ProjectIssueCount.SiteName AS SiteIssue,
dbo.ProjectFeatureCount.SiteName AS SiteFeature, dbo.ProjectContentCount.[Open] AS OpenContent,
dbo.ProjectContentCount.Fixed AS FixedContent, dbo.ProjectContentCount.Closed AS ClosedContent, dbo.ProjectIssueCount.[Open] AS OpenIssue,
dbo.ProjectIssueCount.Pending AS PendingIssue, dbo.ProjectIssueCount.Closed AS ClosedIssue, dbo.ProjectFeatureCount.[Open] AS OpenFeature,
dbo.ProjectFeatureCount.Pending AS PendingFeature, dbo.ProjectFeatureCount.Closed AS ClosedFeature
FROM dbo.ProjectFeatureCount FULL OUTER JOIN
dbo.ProjectIssueCount ON dbo.ProjectFeatureCount.SiteName = dbo.ProjectIssueCount.SiteName FULL OUTER JOIN
dbo.ProjectContentCount ON dbo.ProjectIssueCount.SiteName = dbo.ProjectContentCount.SiteName AND
dbo.ProjectFeatureCount.SiteName = dbo.ProjectContentCount.SiteName

This works fine because it gets a list of all clients and data for whichever row there exists data of that type, whether or not there is data for a given client on each table.

However, I now need to join this group of data to a master client table that contains contact information. The master table has info about all the clients but every join I try breaks the join I have working above and causes duplicate rows.

Any ideas would be greatly appreciated!Well, I found my answer after many iterations in query analyzer and here is what I came up with. I hope it helps someone else...

SELECT dbo.SiteMaster.ItemID, dbo.SiteMaster.ModuleID, dbo.SiteMaster.SiteID, dbo.SiteMaster.SiteManager, dbo.SiteMaster.SiteStatus,
dbo.SiteMaster.SiteName, dbo.ProjectIssueCount.[Open] AS OpenIssue, dbo.ProjectIssueCount.Pending AS PendingIssue,
dbo.ProjectIssueCount.Closed AS ClosedIssue, dbo.ProjectContentCount.[Open] AS OpenContent, dbo.ProjectContentCount.Fixed AS FixedContent,
dbo.ProjectContentCount.Closed AS ClosedContent, dbo.ProjectFeatureCount.[Open] AS OpenFeature,
dbo.ProjectFeatureCount.Pending AS PendingFeature, dbo.ProjectFeatureCount.Closed AS ClosedFeature
FROM dbo.ProjectContentCount FULL OUTER JOIN
dbo.ProjectIssueCount FULL OUTER JOIN
dbo.SiteMaster ON dbo.ProjectIssueCount.SiteName = dbo.SiteMaster.SiteName FULL OUTER JOIN
dbo.ProjectFeatureCount ON dbo.ProjectIssueCount.SiteName = dbo.ProjectFeatureCount.SiteName AND
dbo.SiteMaster.SiteName = dbo.ProjectFeatureCount.SiteName ON dbo.ProjectContentCount.SiteName = dbo.SiteMaster.SiteName AND
dbo.ProjectContentCount.SiteName = dbo.ProjectIssueCount.SiteName

Multi-Table IDENTITY

Is there a way to associate IDENTITY columns from two different tables, so that new records created in either table will have mutually unique values?

That is, a new record in table A will be given value 1, and then a new record created in table B will be given value 2 (because value 1 was already used by the IDENTITY column in table A). Can this be done?

No, identity fields are only unique within the table.

multi-statement functions vs stored procedures

I would like to understand the reason in which to "use multi-statement
functions instead of stored procedures that return tables. Writing stored
procedures that return tables as multi-statement userdefined functions can
improve efficiency."
When to use Multi-statement table-value and when to use stored procedure?
Thanks.
In general:
When you want to execute code, use stored procedures.
When you want to use the result table of your code in a SELECT statement (in the FROM clause), use a
MSTVUDF.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:3EA76526-4DA5-454B-973E-F457D7795E30@.microsoft.com...
> I would like to understand the reason in which to "use multi-statement
> functions instead of stored procedures that return tables. Writing stored
> procedures that return tables as multi-statement userdefined functions can
> improve efficiency."
> When to use Multi-statement table-value and when to use stored procedure?
> Thanks.
>

multi-statement functions vs stored procedures

I would like to understand the reason in which to "use multi-statement
functions instead of stored procedures that return tables. Writing stored
procedures that return tables as multi-statement userdefined functions can
improve efficiency."
When to use Multi-statement table-value and when to use stored procedure?
Thanks.In general:
When you want to execute code, use stored procedures.
When you want to use the result table of your code in a SELECT statement (in the FROM clause), use a
MSTVUDF.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:3EA76526-4DA5-454B-973E-F457D7795E30@.microsoft.com...
> I would like to understand the reason in which to "use multi-statement
> functions instead of stored procedures that return tables. Writing stored
> procedures that return tables as multi-statement userdefined functions can
> improve efficiency."
> When to use Multi-statement table-value and when to use stored procedure?
> Thanks.
>sql

multiplue UI Report Tables using exact same dataset ?

Is there an easier way to manage the table UI element on the report. For
example in my report I have a table for the header info and the data, the
data uses maybe 10 columns and we could be perhapps adding more columns
(fields) to it in the future. The header uses 4 columns. It's a pain
because everytime I add a column to the main report details I have to go up
and fix the header and merge the appropriate cells to get it back to 4 cells
in the header. Is there not a way to use multiple table elements but they
both use the same dataset,. When I say same dataset, I dont mean a copy of
the same dataset, I want it to use the same dataset without the query
running twice for each table.
You guys know what I mean?
Possible ?Sorry, I'm not sure what you mean!
Is the table header a seperate table from the 'data' table?
Are they using the same dataset or not?
If the header and data are in fact seperate tables, then why are you
doing it like that, you seem to be making extra work for yourself!
If two tables use the same dataset the query would only be run once.
Chris
D Witherspoon wrote:
> Is there an easier way to manage the table UI element on the report.
> For example in my report I have a table for the header info and the
> data, the data uses maybe 10 columns and we could be perhapps adding
> more columns (fields) to it in the future. The header uses 4
> columns. It's a pain because everytime I add a column to the main
> report details I have to go up and fix the header and merge the
> appropriate cells to get it back to 4 cells in the header. Is there
> not a way to use multiple table elements but they both use the same
> dataset,. When I say same dataset, I dont mean a copy of the same
> dataset, I want it to use the same dataset without the query running
> twice for each table.
> You guys know what I mean?
> Possible ?

Multipls cursors help

Hello,
I am trying to create multiple cursors and update 2 separate tables. But the
data tables seem to be locking after I run them. Is my logic off? My code is
below -
set xact_abort on
declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
declare GetUserID cursor for
select [user_id] from users where [user_id] not in (select [user_id] from
userdata)
open GetUserID
fetch next from GetUserID into @.user_id
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetUserData cursor for
select topic_name from userdata where [user_id] = 327
open GetUserData
fetch next from GetUserData into @.topic_name
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetNextID cursor for
select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
open GetNextID
fetch next from GetNextID into @.key_seq_num
while @.@.fetch_status = 0
begin
begin tran
insert into UserData(user_data_id, [user_id], topic_name, update_date,
update_user, create_date, create_user)
select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
getdate(), 'Default'
update nextkey set key_seq_num = @.key_seq_num + 1
from nextkey where key_name = 'userdata_user_data_id'
commit tran
print @.key_seq_num
fetch next from GetNextID into @.key_seq_num
end
close GetNextID
deallocate GetNextID
fetch next from GetUserData into @.topic_name
end
close GetUserData
deallocate GetUserData
fetch next from GetUserID into @.user_id
end
close GetUserID
deallocate GetUserIDAnil,
Can you explain what are you trying to accomplish?
AMB
"Anil" wrote:

> Hello,
> I am trying to create multiple cursors and update 2 separate tables. But t
he
> data tables seem to be locking after I run them. Is my logic off? My code
is
> below -
> set xact_abort on
> declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
> declare GetUserID cursor for
> select [user_id] from users where [user_id] not in (select [user_id] from
> userdata)
> open GetUserID
> fetch next from GetUserID into @.user_id
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetUserData cursor for
> select topic_name from userdata where [user_id] = 327
> open GetUserData
> fetch next from GetUserData into @.topic_name
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetNextID cursor for
> select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
> open GetNextID
> fetch next from GetNextID into @.key_seq_num
> while @.@.fetch_status = 0
> begin
> begin tran
> insert into UserData(user_data_id, [user_id], topic_name, update_date,
> update_user, create_date, create_user)
> select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
> getdate(), 'Default'
> update nextkey set key_seq_num = @.key_seq_num + 1
> from nextkey where key_name = 'userdata_user_data_id'
> commit tran
> print @.key_seq_num
> fetch next from GetNextID into @.key_seq_num
> end
> close GetNextID
> deallocate GetNextID
> fetch next from GetUserData into @.topic_name
> end
> close GetUserData
> deallocate GetUserData
> fetch next from GetUserID into @.user_id
> end
> close GetUserID
> deallocate GetUserID|||I am trying to take all the user id's from cursor 1, some value from cursor
2
and 3 and update them into a table. Cursor 3 has an id that needs to be
incremented each time as it is not an auto number. The way I have laid out m
y
cursors, is that the correct way?
Thanks.
Anil
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Anil,
Can you explain it without referencing the cursors?
AMB
"Anil" wrote:
> I am trying to take all the user id's from cursor 1, some value from curso
r 2
> and 3 and update them into a table. Cursor 3 has an id that needs to be
> incremented each time as it is not an auto number. The way I have laid out
my
> cursors, is that the correct way?
> Thanks.
> Anil
> "Alejandro Mesa" wrote:
>|||Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Alejandro Mesa" wrote:
> Anil,
> Can you explain it without referencing the cursors?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct for multiple cursors? Is it possible to do what I am
trying to do with cursors?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct? Is it possible to do what I am trying to do?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Hi Anil,
Looks like the tables which you are accessing is getting locked since you do
all your stuff within a transaction.
You can do 2 things to see if the issue is happening because of a lock
1. Remove all transaction (begin/commit tran) and see if your code actually
works.
2. If your code works after step 1 then use WITH (NOLOCK) clause in your
select statements.
Regards,
Joe.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Alejandro Mesa" wrote:
>
cursor 2
be
out my
tables. But the
My code is
[user_id] from
'userdata_user_data_id'
update_date,|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>sql

multiples charts and tables on the same report from multiple datasets

Hi,
I am trying to use Reporting services toi create a report with a number of
charts and tables from the same datasource but multiple datasets. When I
created a table and dragged the data fields into the column it is
=First(Fields!OrderID.Value, "DataSet1")
It keeps repeating until the end of rows where in the dataset I used top5
SQL what should be the correct way to do it?
Also I am wondering if I can modify the color of and space between bar chart
items.
Thank you in advance,
SunnyRemove "First" from your code, so you end up with =Fields!OrderID.Value.
Also, in the charts, you might have to change your values. At least for me
the values shows up as =Count(Fields!OrderID.Value), and I usually don't
want to have "Count" at all.
You can change the background color of a chart, just right click and choose
properties. (DOn't remeber where, but you'll find it if you look for it.)
DOn't think you can change the spacing, except with just changing the size
of the chart.
Kaisa M. LIndahl
"Sansanee" <sansanee@.nospam.com> wrote in message
news:ufPXgHlNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to use Reporting services toi create a report with a number of
> charts and tables from the same datasource but multiple datasets. When I
> created a table and dragged the data fields into the column it is
> =First(Fields!OrderID.Value, "DataSet1")
> It keeps repeating until the end of rows where in the dataset I used top5
> SQL what should be the correct way to do it?
> Also I am wondering if I can modify the color of and space between bar
chart
> items.
>
> Thank you in advance,
> Sunny
>

Multiple variables

Hi
Our database has a series of tables that are all linked by a field called
company_code (amongst others). Occasionally data needs to be deleted and
this involves deleting multiple rows from some of these tables and updating
data in another table. I tend to wait until there are a few to do and then
do them all at once. To make the task less onerous I have saved a simple
script that does all the deletions for each code at once, a cut down version
is below:
/*Delete all pay data and make contact only*/
DECLARE @.code int
SET @.code = 12345
DELETEcompany_size
WHEREcompany_code = @.code
--Other deletions go in here
UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code = @.code
What I would like to be able to do is change the 'where' statements so that
they use the IN operator rather than the = operator. Is there a way to do
this using variables so I can assign a list of company codes to a variable
that can be used with an IN operator?
Thanks
Andy
On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:

>Hi
>Our database has a series of tables that are all linked by a field called
>company_code (amongst others). Occasionally data needs to be deleted and
>this involves deleting multiple rows from some of these tables and updating
>data in another table. I tend to wait until there are a few to do and then
>do them all at once. To make the task less onerous I have saved a simple
>script that does all the deletions for each code at once, a cut down version
>is below:
>/*Delete all pay data and make contact only*/
>DECLARE @.code int
>SET @.code = 12345
>DELETEcompany_size
>WHEREcompany_code = @.code
>--Other deletions go in here
>UPDATEcompany_basic
>SETreport_status = null,
>report_entry_urn = null,
>next_rpt_archive = null,
>/* Other fields to be updated... */
>WHEREcompany_code = @.code
>What I would like to be able to do is change the 'where' statements so that
>they use the IN operator rather than the = operator. Is there a way to do
>this using variables so I can assign a list of company codes to a variable
>that can be used with an IN operator?
>Thanks
>Andy
>
Hi Andy,
To do that, you'll have to use dynamic SQL. Since this is in a script
that only you can execute, you don't have to worry about SQL Injection
in this case.
Rough outline:
/*Delete all pay data and make contact only*/
DECLARE @.codes nvarchar(40)
DECLARE @.SQL nvarchar(4000)
SET @.code = N'(12345,6789)'
SET @.sql = 'DELETEcompany_size
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
--Other deletions go in here
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
In case you're tempted to use this technique in your production code as
well, read up on SQL injection and other dangers of this technique:
http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks very much for this - I've not tried it yet as I think there might be
a couple of typos that I want to confirm:
You declare a variable called @.codes, presumably all of my old instances of
@.code should now read @.codes instead.
Is the N in this line correct: SET @.code = N'(12345,6789)'?
I'm not brave enough to give it a go anyway!!!
Thanks again
"Hugo Kornelis" wrote:

> On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:
>
> Hi Andy,
> To do that, you'll have to use dynamic SQL. Since this is in a script
> that only you can execute, you don't have to worry about SQL Injection
> in this case.
> Rough outline:
> /*Delete all pay data and make contact only*/
> DECLARE @.codes nvarchar(40)
> DECLARE @.SQL nvarchar(4000)
> SET @.code = N'(12345,6789)'
> SET @.sql = 'DELETEcompany_size
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
> --Other deletions go in here
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> report_entry_urn = null,
> next_rpt_archive = null,
> /* Other fields to be updated... */
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
>
> In case you're tempted to use this technique in your production code as
> well, read up on SQL injection and other dangers of this technique:
> http://www.sommarskog.se/dynamic_sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:

>Hi Hugo
>Thanks very much for this - I've not tried it yet as I think there might be
>a couple of typos that I want to confirm:
>You declare a variable called @.codes, presumably all of my old instances of
>@.code should now read @.codes instead.
Hi Andy,
My bad - since I adapted the script to work for more than one code at
once, I thought it'd be best to change the variable name from @.code to
@.codes - but after changing the first, I promptly forgot to change the
rest. <blush>

>Is the N in this line correct: SET @.code = N'(12345,6789)'?
Yes. I've changed the datatype to nvarchar (note: not just varchar, but
nvarchar), as is required for dynamic SQL. The N in fron of the string
constant means that this is also regarded as nchar data instead of plain
char data. (Nothing bad will happen if you leave out the N, nor if you
use character type varchar - but it will cuase SQL Server to do an
implicit conversion under the hood. I prefer to use explicit conversion,
or no conversion at all).

>I'm not brave enough to give it a go anyway!!!
Always test code suggestions on a test database. Make sure you have a
recent backup or another means to retore the data. And if possible,
enclose the code to be tested in a transaction, so that you can rollback
the changes if something goes awry.
Following the above advise for code you write yourself is not exactly a
bad idea either :-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks for this. I've been out of the office the last few days so have only
just picked this up. Thanks for the advice as well!
Andy
"Hugo Kornelis" wrote:

> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> Hi Andy,
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @.code to
> @.codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Hi Hugo
I'm getting a syntax error when running this script because oneof the fields
that gets updated is a char data type so:
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = 'C',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
As you can see it is because the quote before C is closing the string and so
on. Do I need to break the string at that point and concatenate it so that:
pay_or_contact = ' + 'C' + ', etc etc
or is there a way of getting SQL to ignore the string within the string?
Sorry I didn't include that bit in the initial question - I was trying to
cut down on the size of the post!
Thanks
Andy
"Hugo Kornelis" wrote:

> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> Hi Andy,
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @.code to
> @.codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
(snip)
>As you can see it is because the quote before C is closing the string and so
>on. Do I need to break the string at that point and concatenate it so that:
>pay_or_contact = ' + 'C' + ', etc etc
>or is there a way of getting SQL to ignore the string within the string?
(snip)
Hi Andy,
You'll have to make sure that you embed a quote within the string. The
code passed to the EXEC (@.sql) needs quotes around the C to recognise it
as a literal. There are two ways to do that: either use the ASCII value
for the quote, or double the quote (one quote in a string is considered
a string delimiter, two consecutive quotes are considered one quote as
part of the string):
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = ''C'',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
or
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo
This has worked perfectly - its going to save me an awful lot of time!!!
Andy
"Hugo Kornelis" wrote:

> On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
> (snip)
> (snip)
> Hi Andy,
> You'll have to make sure that you embed a quote within the string. The
> code passed to the EXEC (@.sql) needs quotes around the C to recognise it
> as a literal. There are two ways to do that: either use the ASCII value
> for the quote, or double the quote (one quote in a string is considered
> a string delimiter, two consecutive quotes are considered one quote as
> part of the string):
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ''C'',
> year_end_date = null,
> --Other fields being set to Null
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> or
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
> year_end_date = null,
> --Other fields being set to Null
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Multiple Union

Anyone point know a more elegant solution to the following?
I have 2 tables
Events and Locations
Events structure is
EventID int
EventName varchar
EventLeader int
LocationID int
Locations consists of
LocationID int
Location1 varchar
Location2 varchar
Location3 varchar
.
.
Location20 varchar
I want to be able to get 1 column containing all locations
for a particular EventID like
Location
Boston
.
.
Seattle
NewYork
The way I found is having a SELECT location1 ..UNION
SELECT location2 all the way to SELECT location20
Is there a better way?"michael.obrien@.DelThiSul.ie" <anonymous@.discussions.microsoft.com> wrote in
message news:57ef01c42d34$2e05fc30$a501280a@.phx.gbl...
> Anyone point know a more elegant solution to the following?
> I have 2 tables
> Events and Locations
> Events structure is
> EventID int
> EventName varchar
> EventLeader int
> LocationID int
> Locations consists of
> LocationID int
> Location1 varchar
> Location2 varchar
> Location3 varchar
> .
> .
> Location20 varchar
> I want to be able to get 1 column containing all locations
> for a particular EventID like
> Location
> Boston
> .
> .
> Seattle
> NewYork
> The way I found is having a SELECT location1 ..UNION
> SELECT location2 all the way to SELECT location20
> Is there a better way?
Well you could store the locations in a seperate table, or use a
table-values UDF.
Like this:
create table location(
LocationID int,
Location1 varchar(20),
Location2 varchar(20),
Location3 varchar(20))
go
alter function get_locations(@.LocationID int)
returns @.locations table (Location varchar(20))
as
begin
declare @.Location1 varchar(20)
declare @.Location2 varchar(20)
declare @.Location3 varchar(20)
select
@.Location1 = Location1,
@.Location2 = Location2,
@.Location3 = Location3
from location
where locationID = @.LocationID
if @.Location1 is not null
insert @.locations values(@.Location1);
if @.Location2 is not null
insert @.locations values(@.Location2);
if @.Location3 is not null
insert @.locations values(@.Location3);
return;
end
go
insert location(locationid, location1,location2) values
(1,'Detroit','Houston')
select * from get_locations(1)
David

Multiple ToggleItems

I have a report that utilizes five separate tables. All of the tables
are filtered by the same datetimestamp, which appears in the detail
row. Right now, I have the ToggleItem for all of the tables set to
the same text box at the top of the report. I can also set the
ToggleItem for each of the tables to a cell contained in the same
table. Is there any way to specify multiple Toggleitem values, i.e.
the text box at the top of the report AND the cell in the table
itself?No, this isn't possible.
The closest I think you can get is to have a report parameter which
determines the initial visibility state of the tables.
<Hidden>=Parameters!InitiallyHidden.Value</Hidden>
Then instead of using the top level textbox be a toggle item, you make it a
drillthrough back to the same report with the initallyhidden parameter value
set to
=Not(Parameters!InitiallyHidden.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"utterman" <chris.utter@.gmail.com> wrote in message
news:806a4b3c.0407281257.46ecf520@.posting.google.com...
> I have a report that utilizes five separate tables. All of the tables
> are filtered by the same datetimestamp, which appears in the detail
> row. Right now, I have the ToggleItem for all of the tables set to
> the same text box at the top of the report. I can also set the
> ToggleItem for each of the tables to a cell contained in the same
> table. Is there any way to specify multiple Toggleitem values, i.e.
> the text box at the top of the report AND the cell in the table
> itself?