Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count?
Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this.
No, as you're seeking for a computed column, and a computed column can only be derived from other columns in the same table, so you can't add a column to the Categories table which stores the count from the Photoes table. Maybe you cancreate a view which contains the count of photoes as well as other information you need in your application, and then query on the view:
CREATE VIEW CategoryPhotoCnt AS
SELECT COUNT(PhotoID) AS PhotoCnt,c.CategoryID,c.CategoryName
FROM Categories c join Photos p ON p.CID = c.CategoryID
GROUP BY c.CategoryID,c.CategoryName
