Solved

query question

Posted on 2016-10-18
4
30 Views
Last Modified: 2016-10-18
Hi,

I have two sample tables, how can I come up with the result that shows everything from V and left 3, 5 in S as null

SELECT     v.vid, v.vname, s.sid, s.sname
FROM         service s LEFT  JOIN
                      visit v ON s.sid = v.vid
WHERE s.sid not in (3,5)

thanks
c1.JPG
c2.JPG
0
Comment
Question by:mcrmg
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
Dustin Saunders earned 500 total points
ID: 41849185
SELECT v.vid,v.vname,s.sid,s.sname
FROM visit v
LEFT JOIN service s ON v.vid = s.sid AND s.sid NOT IN (3,5)

Open in new window


Should do that.
Results:
vid         value                                              sid         sname
----------- -------------------------------------------------- ----------- --------------------------------------------------
1           m                                                  1           aaa
2           m                                                  2           bbb
3           m                                                  NULL        NULL
4           w                                                  4           ddd
5           w                                                  NULL        NULL
6           w                                                  6           fff
7           w                                                  NULL        NULL
8           w                                                  NULL        NULL
9           m                                                  NULL        NULL
10          m                                                  NULL        NULL

Open in new window

0
 

Author Comment

by:mcrmg
ID: 41849194
is there another to do this?  thanks
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41849197
There might be, though this is the simplest route.  Is that not the result you needed?
0
 

Author Closing Comment

by:mcrmg
ID: 41849200
thank you very much
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now