How to Populate a Checkbox: C# vs T-SQL
Sorry for the R101 question.
I have a SQL Server 2008 table with a list of items (itemID, Title). I have another table with boxes (boxID, Title). I then have a lookup table that identifies which items go into which boxes.
What is the best way to list all the items with a checkbox checked to show which items are in a box depending on a selected boxID?
Probably a T-SQL statement would be better than a convoluted C# datatable. I've had a few attempts but just cant get the right result.
EDIT: Sorry I've not been very clear. I need to present a list of all items with a checkbox next to each item. I want checkbox.checked against any item where the boxID has an equivalent item ID next to it in the lookup table.
The result should have have ideally the itemID, itemName and a boolean column to show whether to check the checkbox or not.
The idea is that when the 'box' is being updated items can be amended etc.
I suppose you want two things:
1) A list of all items
2) A list of all items in boxes
You can do this in a couple of ways (I'm guessing the structure of your tables here - I'm guessing ItemBox is the link table)
a) Select all of the items, then select a list of all of the items in boxes:
SELECT I.ItemID, I.Title, B.BoxId, B.Title FROM Item I INNER JOIN ItemBox IB ON (I.ItemID = IB.ItemID) INNER JOIN Box B ON (IB.BoxID = B.BoxID) ORDER BY B.Title, I.Title
Should give you all of the items and their boxes.
You can then display a list of all items in a form, and a list of box ids (just simple selects from each table).
If you cache the results from the above query (in say a dictionary, BoxItem dictionary) then you can look up the item quite simply based on the selected Box.
b) you can run the query each time a box is selected:
SELECT I.ItemID, I.Title, B.BoxId, B.Title FROM Item I INNER JOIN ItemBox IB ON (I.ItemID = IB.ItemID) INNER JOIN Box B ON (IB.BoxID = B.BoxID) WHERE B.Id = @selected_boxid ORDER BY B.Title, I.Title
And update accordingly.
Do you need examples for running the query, binding to various controls etc?
Here's a version that should do what you want based on your comment:
SELECT I.ItemID, I.Title, CASE WHEN IB.ItemID IS NULL THEN 0 ELSE 1 END AS ItemIsInBox FROM Item I LEFT OUTER JOIN ItemBox IB ON (I.ItemID = IB.ItemID);
Here's how I fixed the duplicate items:
SELECT DISTINCT TOP (100) PERCENT I.itemID, I.Title, CASE WHEN IB.BoxID = @boxID THEN 1 ELSE 0 END AS ItemIsInBox FROM Item AS I LEFT OUTER JOIN ItemBox AS IB ON I.itemID = IB.itemID AND **IB.BoxID = @boxID** ORDER BY I.itemID
Thought it was worth sharing. Thanks for your help. You all helped to get me here.