I want to be able to create a grid where you input someones ID and it gives you a listing of all the cookies available for sale and beside it show a Yes or No value as to whether or not the ID has sold them. I have successfully written queries which will show all cookies for sale, and the cookies that a user has sold or has not sold, but I can not figure out how to put that into 1 easy to read grid. I would want to show something like this:
userID = 1
Cookies ...... Sold ...... Not Sold
choc chip No
thin mint Yes
This is what I have so far, but how can I put that into the grid like I need?
--This gives a list of all of the cookies available to sell
Select cookies from tbl_CookiesForSale2015
Order By cookies ASC
--This shows what the specific person has sold
Select cookies as 'Has Sold' from tbl_CookiesForSale2015
where cookies IN (Select cookieID from tbl_userCookies where gsID = @gsID AND HasSold = 1)
Order By [Has Sold]
--This shows what the specific person has not sold
Select cookies as 'Not Sold' from tbl_CookiesForSale2015
where cookies IN (Select cookieID from tbl_userCookies where gsID = @gsID AND HasSold = 0)
Order By [NotSold]
Can you give us some more information?
Like provide us with all DDL and DML's to create all the tables + populate them with sample data.
Just a few quick remarks:
- don't use the prefix "tbl_" in table names. You don't call someone "person_Bob", but "Bob".
- storing the combination person/cookies NOT sold, seems like bad design. Better is to deduce that from the cookies for sale in a particular year and the cookies someone did sell in that year.
- CookiesForSale2015 may hint at a bad design. Most of the times it is better to include the year as an extra attribute (column) in the table:
CREATE TABLE dbo.CookiesForSale
ID int IDENTITY NOT NULL,
sale_year int NOT NULL
CONSTRAINT CC_CookiesForSale_sale_year CHECK (sale_year >= 2015),
cookie varchar(100) NOT NULL,
CONSTRAINT pk_CookiesForSale PRIMARY KEY (id)
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages