need to combine two selects 1

[DougP]

I have two tabels, "Employees" 1 hold names & employee etc.
2nd "Submitted" holds whether they submitted a time sheet that week. The Submitted table has the WeekEnd Date of when they submit
such as
Fred Smith 7/28/2012
Cary Grant 7/28/2012

I would like to see all names frmm the Employees table regardless fi submittted has a record but also if they submitted a time sheet have it show an '*' next to their name, like so
* Fred Smith
Sally Jones
Sue Cope
* Cary Grant
etc

I created a temp table which I add records to with tow diifern selects, but is there a way to have a join statement. I am showing the names in a drop down list in an ASPX page. So some one could quickly see if a person did not submit yet?
This is what I have so far...

Select '* ' + Lastname + ', ' + Firstname from Submitted sd
 JOIN Employees sw on sw.LastName = sd.LastName 
Where sd.Weekenddate  = '2012-07-28'
and sd.ManagerName = 'Fred Flinstone'

DougP

 

[gmmastros]

Try this:

Select Case When sd.LastName Is NULL Then '* ' Else '' End 
       + Lastname + ', ' + Firstname 
from   Employees sw 
       [!]LEFT [/!]JOIN Submitted sd
          on sw.LastName = sd.LastName 
          And sd.Weekenddate  = '2012-07-28'
          and sd.ManagerName = 'Fred Flinstone'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

George, Thank you for that, it returns 96 rows with the word NULL in everyone. there are 96 employess,
But it's not returning any names using the + Lastname + ', ' + Firstname
and it is not finding the few records I have as submitted with a valid date of 7-28-12 so none of the rows contain "*"



DougP

 

[DougP]

If I cahnge it slighty I get 96 rows 6 of them have a name of the person that did submit a timesheet, but the other rows all conatin the word "NULL"
if we can flip it so the other have their name then that is great.
here is what this returns
NULL
* Wilma Flinstone
NULL
NULL
* barney Ruble
NULL
..
..
..

here is what I need
Betty Ruble
* Wilma Flinstone
BamBam Ruble
Pebbles Flinstone
* barney Ruble
So in other words where there is the word NULL I would like to see their name instead.

Select Case When sd.astName Is not NULL Then '* '  Else + sd.LastName + ', ' + sd.FirstName  End 
       + sd.LastName + ', ' + sd.FirstName 
from  dbo.employees sw 
        left JOIN Submitted sd
          on sw.LastName = sd.LastName

DougP

 

[gmmastros]

ok.... try this:

Select Case When sd.LastName Is not NULL Then '* '  Else + [!]sw[/!].LastName + ', ' + [!]sw[/!].FirstName  End 
       + [!]sw[/!].LastName + ', ' + [!]sw[/!].FirstName 
from  dbo.employees sw 
        left JOIN Submitted sd
          on sw.LastName = sd.LastName

Of course, this assumes that you have a FirstName column in the Employees table.

Since this query is using a left join, it will return everything from the employees table, and matching rows from the submitted table. If there are no matching rows in the submitted table, it returns NULL. By using the employee table to return the name, there shouldn't be any nulls.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Great George it does work
But when I was playing around and loaded the "*" Betty Ruble into the drop down list a bunch of things quit working since "stuff" was looking at and expecting a name and not "*" ...
so I will add another drop down and just put the name of those not submitted yet. Then I thougth about Microsoft Access so I barrowed "Access"es unmathced query wizard which works made this...

          SELECT rs.Lastname, rs.Firstname, rs.Manager
FROM Employees rs LEFT JOIN Submitted sd ON rs.[Lastname] = sd.[Lastname]
WHERE  rs.Manager ='Fred Flintstone' and (((sd.Lastname) Is Null));

Thank you though, have a star

DougP