Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Unanswered: Showing all Categories

    Hi guys i have a problem I have a table that shows a participants activity for the state. The fields are both drop down boxes One is [state category] and [state services].

    under [State Category] = Academic Support, Self Concept and Counseling

    and under [State Services] = "Computer Class Hours";"GED Class Hours";"College Class Hours";"Cultural Activites Hours";"Leadership Hours";"Individual - Personal Adjustment Hours";"Individual - Academic Progress Hours";"Individual - Vocational Planning Hours";"Group - Personal Adjustment Hours";"Group - Academic Progress Hours";"Group - Vocational Planning Hours"


    The thing is the users only uses the Self Concept [state Category] and Cultural Activities Hours [State Services]

    but for a particular report I need all of these categories to show, but in a particular order. Now I got help from all you great db gurus before on another report. I'm just not sure how to get all the Categories to show even thought there are no hours asociated with them. can someone help plse. Also does that make sense???

    Code:
    SELECT     AdultStateActivity_tbl.[Contact Date], AdultStateActivity_tbl.[Earned hours], Parent.[Parent First Name], Parent.[Parent Last Name], Parent.[Parent ID], 
                          AdultStateActivity_tbl.[State Category] AS Expr1, AdultStateActivity_tbl.[State Services] AS Expr2, COUNT(*) AS Expr3
    FROM         AdultStateActivity_tbl INNER JOIN
                          Parent ON AdultStateActivity_tbl.[Parent ID] = Parent.[Parent ID] INNER JOIN
                          StateLookup_tbl ON Parent.[Parent ID] = StateLookup_tbl.[Parent ID]
    GROUP BY AdultStateActivity_tbl.[Contact Date], AdultStateActivity_tbl.[Earned hours], Parent.[Parent First Name], Parent.[Parent Last Name], Parent.[Parent ID], 
                          AdultStateActivity_tbl.[State Category], AdultStateActivity_tbl.[State Services]
    HAVING      (StateLookup_tbl.[State Category] IN (N'Academic Support', N'Self Concept', N'Counseling')) AND (AdultStateActivity_tbl.[Contact Date] BETWEEN 
                          @Beginning_ContactDate AND @End_ContactDate)
    ORDER BY CASE WHEN AdultStateActivity_tbl.[State Category] = N'Academic Support' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Computer Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'GED Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'College Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Category] = N'Self Concept' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Cultural Activities Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Leadership Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Category] = N'Counseling' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Individual - Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Individual - Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Individual - Vocational Planning Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Group - Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Group - Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN AdultStateActivity_tbl.[State Services] = N'Group - Vocational Planning Hours' THEN 0 ELSE 1 END

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Consider an OUTER join...
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a LEFT OUTER JOIN

    surely we've shown you this before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Yes R937 you did and it didnt work I still couldnt get all the categories to show. I must have been doing something wrong but I did use LEFT OUTER JOIN. but I did try that
    Last edited by desireemm; 09-22-09 at 13:45.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have a table with all your dropdowns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    When I inherited the database originally there was no Lookup table. So I created one. Was just a combo box with State Category rowsource
    "Academic Support";"Self Concept";"Counseling";"Enrichment"

    and State Services

    "Computer Class Hours";"GED Class Hours";"College Class Hours";"Cultural Activites Hours";"Leadership Hours";"Individual - Personal Adjustment Hours";"Individual - Academic Progress Hours";"Individual - Vocational Planning Hours";"Group - Personal Adjustment Hours";"Group - Academic Progress Hours";"Group - Vocational Planning Hours"

    Code:
    CREATE TABLE [dbo].[StateLookup_tbl](
    	[State Category] [nvarchar](255) NULL,
    	[State Services] [nvarchar](255) NULL,
    	[Parent ID] [nvarchar](50) NULL,
    	[LookUpID] [int] IDENTITY(1,1) NOT NULL,
     CONSTRAINT [PK_StateLookup_tbl] PRIMARY KEY CLUSTERED 
    (
    	[LookUpID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    which I joined to the Activity Table

    Code:
    CREATE TABLE [dbo].[AdultStateActivity_tbl](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Contact Date] [datetime] NULL,
    	[Contact Time] [datetime] NULL,
    	[Parent ID] [nvarchar](50) NULL,
    	[Referral Date] [datetime] NULL,
    	[Earned hours] [float] NULL,
    	[Catagory for hours] [nvarchar](255) NULL,
    	[State Category] [nvarchar](255) NULL,
    	[State Services] [nvarchar](255) NULL,
    	[Event Name] [nvarchar](255) NULL,
    	[Event Location] [nvarchar](255) NULL,
     CONSTRAINT [PK_ContactStateParticipant_tbl] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    this is the code I am using

    Code:
    SELECT     AdultStateActivity_tbl.[Contact Date], AdultStateActivity_tbl.[Earned hours], Parent.[Parent First Name], AdultStateActivity_tbl.[Earned hours], 
                          Parent.[Parent ID], Parent.[Parent First Name], Parent.[Parent Last Name], StateLookup_tbl.[State Category], StateLookup_tbl.[State Services]
    FROM         AdultStateActivity_tbl INNER JOIN
                          Parent ON AdultStateActivity_tbl.[Parent ID] = Parent.[Parent ID] INNER JOIN
                          StateLookup_tbl ON Parent.[Parent ID] = StateLookup_tbl.[Parent ID]
    WHERE     (AdultStateActivity_tbl.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate) AND 
                          (StateLookup_tbl.[State Category] IN (N'Academic Support', N'Self Concept', N'Counseling')) AND 
                          (StateLookup_tbl.[State Services] IN (N'Computer Class Hours', N'GED Class Hours', N'College Class Hours', N'Cultural Activities Hours', 
                          N'Leadership Hours', N'Individual - Personal Adjustment Hours', N'Individual - Academic Progress Hours', N'Individual - Vocational Planning Hours', 
                          N'Group - Personal Adjustment Hours', N'Group - Academic Progress Hours', N'Group - Vocational Planning Hours'))
    ORDER BY CASE WHEN StateLookup_tbl.[State Category] = N'Academic Support' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Computer Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'GED Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'College Class Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Category] = N'Self Concept' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Cultural Activities Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Leadership Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Category] = N'Counseling' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Individual - Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Individual - Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Individual - Vocational Planning Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Group - Personal Adjustment Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Group - Academic Progress Hours' THEN 0 ELSE 1 END, 
                          CASE WHEN StateLookup_tbl.[State Services] = N'Group - Vocational Planning Hours' THEN 0 ELSE 1 END
    Last edited by desireemm; 09-22-09 at 15:48.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why don't you try a LEFT OUTER JOIN as suggested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    this??

    Code:
    SELECT AdultStateActivity_tbl.[Contact Date]
         , AdultStateActivity_tbl.[Earned hours]
         , Parent.[Parent First Name]
         , AdultStateActivity_tbl.[Earned hours]
         , Parent.[Parent ID]
         , Parent.[Parent First Name]
         , Parent.[Parent Last Name]
         , StateLookup_tbl.[State Category]
         , StateLookup_tbl.[State Services]
    FROM StateLookup_tbl
    LEFT OUTER JOIN Parent ON StateLookup_tbl.[Parent ID] = Parent.[Parent ID]
    LEFT OUTER JOIN AdultStateActivity_tbl ON Parent.[Parent ID] = AdultStateActivity_tbl.[Parent ID]
                                        AND AdultStateActivity_tbl.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate
    ORDER BY CASE WHEN StateLookup_tbl.[State Category] = N'Academic Support' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Computer Class Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'GED Class Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'College Class Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Category] = N'Self Concept' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Cultural Activities Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Leadership Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Category] = N'Counseling' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Individual - Personal Adjustment Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Individual - Academic Progress Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Individual - Vocational Planning Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Group - Personal Adjustment Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Group - Academic Progress Hours' THEN 0 ELSE 1 END, 
             CASE WHEN StateLookup_tbl.[State Services] = N'Group - Vocational Planning Hours' THEN 0 ELSE 1 END

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by desireemm
    this??
    i dunno

    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    well I ran the report the State Services and state Category was empty. I think I messed up with the look up tables.

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok fixed the look up tables. tried the left outer join but all the categories are not showing. The main thing that they provide thats in the lookup tables is from State Category = Self Concept
    and State Services = Cultural Acitivites Hours
    those are the only two services they offer so thats all thats showiing. Not sure how to make the other categories show even thought the users dont offer it to the participants??

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query in post #8 will return all rows in the StateLookup_tbl table table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Hi R937 thank yoiu so much for your feeback I really appreciate it. I wanted to make sure I creating the lookup table correctly. what I did was I did a make table from the AdultStateActivity_tbl and created a between it and the AdutlStateActivity_tbl. Did I make a mistak in doing that??

    Code:
    CREATE TABLE [dbo].[AdultStateActivity_tbl](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Contact Date] [datetime] NULL,
    	[Contact Time] [datetime] NULL,
    	[Parent ID] [nvarchar](50) NULL,
    	[Referral Date] [datetime] NULL,
    	[Earned hours] [float] NULL,
    	[Catagory for hours] [nvarchar](255) NULL,
    	[State Category] [nvarchar](255) NULL,
    	[State Services] [nvarchar](255) NULL,
    	[Event Name] [nvarchar](255) NULL,
    	[Event Location] [nvarchar](255) NULL,
     CONSTRAINT [PK_ContactStateParticipant_tbl] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    these are the fields I used to make the table

    Code:
     SELECT     ID, [State Category], [State Services]
    FROM         AdultStateActivity_tbl

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i am ~so~ lost

    you seem to think that i know your tables and what's in them

    i don't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    sorry R937 heres some sample data
    Code:
       [State Catagory]          [State Services]
    
    544	Self Concept	Cultural Activites Hours	118
    546	Self Concept	Cultural Activites Hours	119
    547	Self Concept	Cultural Activites Hours	120
    548	Self Concept	Cultural Activites Hours	121
    549	Self Concept	Cultural Activites Hours	122
    551	Self Concept	Cultural Activites Hours	123
    552	Self Concept	Cultural Activites Hours	124
    553	Self Concept	Cultural Activites Hours	125
    554	Self Concept	Cultural Activites Hours	126
    555	Self Concept	Cultural Activites Hours	127
    556	Self Concept	Cultural Activites Hours	128
    557	Self Concept	Cultural Activites Hours	129
    558	Self Concept	Cultural Activites Hours	130
    559	Self Concept	Cultural Activites Hours	131
    561	Academic Support	GED Class Hours	132
    562	Academic Support	GED Class Hours	133
    563	Academic Support	GED Class Hours	134
    564	Counceling	Individual - Academic Programs Hours	135
    565	Counceling	Individual - Personal Adjustment Hours	136
    566	Counceling	Individual - Personal Adjustment Hours	137

Posting Permissions

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