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

Multiple while fetch cursor code

I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or?

What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.

For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @.instrumentlinje remains empty.

If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument" statement doesn't execute.

DELETE FROM ALL_tbl_instrumentkoder

DECLARE @.medlem int
DECLARE @.instrument varchar(10)
DECLARE @.instrumentlinje varchar(150)

DECLARE medlemmer_cursor CURSOR FOR
SELECT medlemsnummer
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

DECLARE instrumenter_cursor CURSOR FOR
SELECT [MCPS Kode]
FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

OPEN medlemmer_cursor

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

WHILE @.@.FETCH_STATUS = 0
BEGIN

OPEN instrumenter_cursor
FETCH NEXT FROM instrumenter_cursor INTO @.instrument

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument
FETCH NEXT FROM instrumenter_cursor INTO @.instrument
END

CLOSE instrumenter_cursor

INSERT INTO ALL_tbl_instrumentkoder VALUES(@.medlem, @.instrumentlinje)

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

END

CLOSE medlemmer_cursor
DEALLOCATE medlemmer_cursor
DEALLOCATE instrumenter_cursorWell, I suspect the problem is related to referencing a variable in your cursor definition, but you shouldn't be using a cursor anyway.

Here is a simpler (non-cursor) method:

First, create this function:
create function dbo.instrumentlinje(@.medlem int)
returns varchar(4000) as
begin
declare @.instrumentlinje
select @.instrumentlinje = isnull(@.instrumentlinje + ' ', '') + MCPS Kode
from Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

return @.instrumentlinje
end

Then, run this code:
insert into ALL_tbl_instrumentkoder
(medlem,
instrumentlinje)
select medlemsnummer,
dbo.instrumentlinje(medlem)
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

Warning! Not tested for syntax errors, and you may need to edit object ownership.|||Basically the same way I did it in Access.. just a greenhorn when it comes to SQL-server.

Thanks man :-)|||TSQL is similar to Access SQL, though there are a few syntactical differences. The concept of avoiding cursors and loops in favor of set-based operations is the same, though.

multiple submit

Is there a way to stop duplicate entries into a database from people double clicking the submit button?You can use UNIQUE CONSTRAINTS for all columns not participating in a primary key.
See "Unique constraints" in BOL

Originally posted by rob7765
Is there a way to stop duplicate entries into a database from people double clicking the submit button?|||Although this could be handled on the DB side, I feel it's more appropriatly handled on the web-server side. Simplest is JS that disables a double-submit (Darn those mainframe people and their double-enter habbits).

For situations where you 100% of the time can't have double-submits, consider setting a hidden field & session variable that tracks if the user has submittted the form or not.

There is much discussion of this in forms dealing with the web side of life.

multiple submit

Is there a way to stop duplicate entries into a database from people double clicking the submit button?1. Beat them about the head and shoulders every time you catch them double clicking.

2. Put a unique index on the key column(s).

3. Add a trigger to prohibit inserting duplicate rows. Don't forget to have a nasty message!

4. Only allow modifications via stored procedures. By using a stored procedure you can do cool things like link to the payroll system and transfer $3.00 from their paycheck to yours everytime the double click.

5. Re-write your application so double clicks won't be a problem.|||I just posted a reply to a similar message. It was as general as, but as not witty as, Paul's response. I recomended solving it on the web side.