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

MultiTable Dataset

First off let me say that some of this is very confusing as it would
appear that the MS SQL group and the ADO.NET group need to get together
to mesh their termonology a bit.
I have a program that uses ReportViewer. To this program I load up an
ADO.NET dataset that contains serveral tables. I also load in an RDLC
report file and display the resulting report.
Up until now this has worked great and I have gotten every report I
need worked out. To keep this simple lets go with something we all
know all so well. My ADO.NET dataset contains three tables:
Customers, Orders, Parts. Each one of them is related to the other.
How does one go about displaying a report that would appear as follows:
Customer 1
-Order 1
--Part 1
--Part 2
--Part 3
-Order 2
--Part 4
--Part 5
Customer 2
-Order3
etc...
I have tried numerous things and read a ton of different posts, but all
to no avail. I know I must be doing something wrong when it comes to
setting this up. There do not seem to be many examples with multiple
tables in a single ADO dataset. Unless I am just missing something.
Also, as an aside, why did MS decide to call each table in a Report a
dataset while a dataset in ADO.NET contains many different tables?
Any help would be greatly appreciated.
Thanks,
AdamYou have to made a Select woth Joins of Customer, Order, Part
So for each part you have one record with the data Customer and Order within.
Then you can group this records in the report and so on.
So you will have only one Resultset for this kind of Report.
OK, The naming-conventions in ReportViewer differs a little bit.
Maybe they will sit together for the next release ;-)
--
LG HOLAN
"akeroo" wrote:
> First off let me say that some of this is very confusing as it would
> appear that the MS SQL group and the ADO.NET group need to get together
> to mesh their termonology a bit.
> I have a program that uses ReportViewer. To this program I load up an
> ADO.NET dataset that contains serveral tables. I also load in an RDLC
> report file and display the resulting report.
> Up until now this has worked great and I have gotten every report I
> need worked out. To keep this simple lets go with something we all
> know all so well. My ADO.NET dataset contains three tables:
> Customers, Orders, Parts. Each one of them is related to the other.
> How does one go about displaying a report that would appear as follows:
> Customer 1
> -Order 1
> --Part 1
> --Part 2
> --Part 3
> -Order 2
> --Part 4
> --Part 5
> Customer 2
> -Order3
> etc...
> I have tried numerous things and read a ton of different posts, but all
> to no avail. I know I must be doing something wrong when it comes to
> setting this up. There do not seem to be many examples with multiple
> tables in a single ADO dataset. Unless I am just missing something.
> Also, as an aside, why did MS decide to call each table in a Report a
> dataset while a dataset in ADO.NET contains many different tables?
> Any help would be greatly appreciated.
> Thanks,
> Adam
>|||Although you can do it, this gets quite messy. The clean way (but it is more
difficult when operating in local mode with the viewer control) is to use
subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"holan" <holan@.noemaol.noemail> wrote in message
news:FA17AF8F-0A21-4A92-BDB9-45A194502517@.microsoft.com...
> You have to made a Select woth Joins of Customer, Order, Part
> So for each part you have one record with the data Customer and Order
> within.
> Then you can group this records in the report and so on.
> So you will have only one Resultset for this kind of Report.
> OK, The naming-conventions in ReportViewer differs a little bit.
> Maybe they will sit together for the next release ;-)
> --
> LG HOLAN
>
> "akeroo" wrote:
>> First off let me say that some of this is very confusing as it would
>> appear that the MS SQL group and the ADO.NET group need to get together
>> to mesh their termonology a bit.
>> I have a program that uses ReportViewer. To this program I load up an
>> ADO.NET dataset that contains serveral tables. I also load in an RDLC
>> report file and display the resulting report.
>> Up until now this has worked great and I have gotten every report I
>> need worked out. To keep this simple lets go with something we all
>> know all so well. My ADO.NET dataset contains three tables:
>> Customers, Orders, Parts. Each one of them is related to the other.
>> How does one go about displaying a report that would appear as follows:
>> Customer 1
>> -Order 1
>> --Part 1
>> --Part 2
>> --Part 3
>> -Order 2
>> --Part 4
>> --Part 5
>> Customer 2
>> -Order3
>> etc...
>> I have tried numerous things and read a ton of different posts, but all
>> to no avail. I know I must be doing something wrong when it comes to
>> setting this up. There do not seem to be many examples with multiple
>> tables in a single ADO dataset. Unless I am just missing something.
>> Also, as an aside, why did MS decide to call each table in a Report a
>> dataset while a dataset in ADO.NET contains many different tables?
>> Any help would be greatly appreciated.
>> Thanks,
>> Adam
>>|||Thanks... I ended up going with subreports and it is working fine.
The joins were not quite going to work for me as my exact situation
wasn't an A to B to C relationship. It was an A-B A-C setup, which
means the joins would have ended up being A-B * C number of records and
that would have gotten really ugly.
thanks,
Adam