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.

Answers


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.


Need Your Help

DB-Toolkit Plugin - on save redirects to wp-admin/admin-ajax.php and returns only a 0

ajax wordpress wordpress-plugin

So I aquired a project that is using DB-Toolkit to make a section of the site but the section hadn't been updated in a while ( as in added new content too ) but recently I went to add new content t...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.