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