Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I just want to say how much I value your site. I hope the good work keeps up there. It's really helped me..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

need to combine two selectsHelpful Member! 

thread183-1689156
Read More Threads Like This One
DougP (MIS)
25 Jul 12 10:56
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...

CODE

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 (Programmer)
25 Jul 12 11:05
Try this:

CODE

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 (MIS)
25 Jul 12 13:22
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 (MIS)
25 Jul 12 13:36
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.

CODE

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
Helpful Member!  gmmastros (Programmer)
25 Jul 12 14:02
ok.... try this:

CODE

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 (MIS)
25 Jul 12 14:27
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...

CODE

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
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close