Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Help Creating A Grid

    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
    sugar Yes
    choc chip No
    thin mint Yes
    sprinkles No

    This is what I have so far, but how can I put that into the grid like I need?

    Code:
    --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]
    
    
    
    
    tbl_CookiesForSale2015
    (
    	cID int,
    	cookie varchar(100)
    )
    
    tbl_userCokies
    (
    	gsID int,
    	cID int,
    	HasSold int
    )

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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:
    so
    Code:
    tbl_CookiesForSale2015
    (
    	cID int,
    	cookie varchar(100)
    )
    becomes
    Code:
    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
    Wim

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •