?
Solved

MS Sql query to list a table and join another table though all rows are not there

Posted on 2016-11-25
13
Medium Priority
?
79 Views
Last Modified: 2016-11-28
We have 2 tables:

Table1
ItemNo   status
-----------   ----------
Part1       1
Part2       1
Part3       1
Part4       5
Part5       1

Table2
ItemNo   status2
-----------   ----------
Part1       0
Part2       1
Part3       1
Part4       0

What we want as a final result is:  
Display all rows of table1 and all rows in table2 where table1 status is 1 and table2 status is 0, but always display table2 rows that don't exist in table1.

Example Desired Result:

ItemNo   status   status2
------------  ---------  -----------

Part1       1            0
Part5       1            0


We use this script:

select p1.ItemNo, p1.Status, p2,Status2 from Table1 p1
left outer join Table2 p2 on p1.ItemNo=p2.ItemNo where p1.Status=1 and p2.Status2=0 order by 1
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 41901650
>>Display all rows of table1 and all rows in table2 where table1 status is 1 and table2 status is 0<<
I see how this would produce the first record in your desired output
>>but always display table2 rows that don't exist in table1<<
I don't see how the second record of your desired output is correct since part5 exists in table1 but not table2, Please clarify.
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 41901651
you should get also the rows without a match in Table2 if you add 'OR p2.ItemNo IS NULL' before 'order by 1'
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 800 total points
ID: 41901663
try..

SELECT o.*,j.status2 FROM
(
	select p1.ItemNo, p1.Status
	from Table1 p1 WHERE p1.status = 1
)o
OUTER APPLY
(
	SELECT * FROM 
	(
		SELECT * FROM Table2 p2 WHERE p2.status2 = 0
	)k
)j WHERE o.ItemNo = j.ItemNo
UNION ALL
SELECT p1.ItemNo, p1.Status , 0 FROM Table1 p1
LEFT JOIN Table2 p2 ON p1.ItemNo = p2.ItemNo 
WHERE p2.ItemNo IS NULL

Open in new window



Output



ItemNo                                             Status      status2
-------------------------------------------------- ----------- -----------
Part1                                              1           0
Part5                                              1           0

(2 row(s) affected)

Open in new window



Hope it helps !!
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 32

Accepted Solution

by:
awking00 earned 1200 total points
ID: 41901736
No need for union -
select t1.itemno, t1.status status1, coalesce(t2.status2,0) status2
from table1 t1 left join table2 t2
on t1.itemno = t2.itemno
where t1.status = 1
and coalesce(t2.status2,0) = 0
;
0
 

Author Comment

by:rayluvs
ID: 41901749
Gotta tell'ya, that script is  beautiful!  Please explain your script!

Obviously when  we placed the question we used easy table/column names not to complicate the assistance EE gives us.  However, when placing the script to actual naming, gives  a lot  of errors.  So prior placing these question for  your assistance, we created a somewaht virtual table1 & table2 with the columns  and  your script and worked like a charm!

Here is  the virtual tables:

DECLARE @Table1 TABLE(ItemNo VARCHAR(255),sStatus int)
DECLARE @Table2 TABLE(ItemNo VARCHAR(255),sStatus int)
INSERT INTO @Table1  VALUES ('Part1',1) 
INSERT INTO @Table1  VALUES ('Part2',1) 
INSERT INTO @Table1  VALUES ('Part3',1) 
INSERT INTO @Table1  VALUES ('Part4',5) 
INSERT INTO @Table1  VALUES ('Part5',1) 

INSERT INTO @Table2  VALUES ('Part1',0) 
INSERT INTO @Table2  VALUES ('Part2',1) 
INSERT INTO @Table2  VALUES ('Part3',1) 
INSERT INTO @Table2  VALUES ('Part4',0) 

SELECT * FROM @Table1 
SELECT * FROM @Table2

SELECT o.*,j.sstatus FROM
 (select p1.ItemNo, p1.sStatus from @Table1 p1 WHERE p1.sstatus = 1)o
 OUTER APPLY
 (SELECT * FROM (SELECT * FROM @Table2 p2 WHERE p2.sstatus = 0)k)j 	
 WHERE o.ItemNo = j.ItemNo
UNION ALL
 SELECT p1.ItemNo, p1.sStatus , 0 FROM @Table1 p1
 LEFT JOIN @Table2 p2 ON p1.ItemNo = p2.ItemNo 
 WHERE p2.ItemNo IS NULL

Open in new window



So prior closing the question, can you explain your script for our understanding?

Thanx in advance!
0
 

Author Comment

by:rayluvs
ID: 41901751
awking001, didn't see your script; also worked like a charm!  .. can you explain your script?
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1200 total points
ID: 41901784
Okay, let's start with this -
from table1 t1 left join table2 t2
 on t1.itemno = t2.itemno
This will create null values for table2 where the itemno does not exist in table2
Then this
coalesce(t2.status2,0)
Where the coalesce function takes the first non-null value so when t2.status = 0, it returns t2.status and when t2.status is null, it returns 0

My test -

select * from table1;

ITEMN     STATUS
----- ----------
Part1          1
Part2          1
Part3          1
Part4          5
Part5          1

select * from table2;

ITEMN    STATUS2
----- ----------
Part1          0
Part2          1
Part3          1
Part4          0

select t1.itemno, t1.status status1, coalesce(t2.status2,0) status2
from table1 t1 left join table2 t2
on t1.itemno = t2.itemno
where t1.status = 1
and coalesce(t2.status2,0) = 0;

ITEMN    STATUS1    STATUS2
----- ---------- ----------
Part1          1          0
Part5          1          0

Open in new window


I suspect your actual data may contain many more than 4 or 5 records in the table. Using union requires a sort function that can reduce performance on a lot of records which this does not
0
 

Author Comment

by:rayluvs
ID: 41901822
Great Info! Thanx!
0
 

Author Comment

by:rayluvs
ID: 41901826
Note: chose awking00 as best since same result less scripting instructions,
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 41904035
I tried my suggestion in MS Access, where it worked! Did you try it on SQL server?
This is your script with my addition in line 3:

select p1.ItemNo, p1.Status, p2,Status2 from Table1 p1
left outer join Table2 p2 on p1.ItemNo=p2.ItemNo where p1.Status=1 and p2.Status2=0
OR p2.ItemNo IS NULL
order by 1

Less complicated, but maybe it did not work for you?

Kind regards,
Stellan
0
 

Author Comment

by:rayluvs
ID: 41904373
Yes! thanx!
0
 
LVL 32

Expert Comment

by:awking00
ID: 41904447
Stellan,
I believe your query will return a null value for status2 for Itemno Part5 when a zero value was to be expected.
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 41904836
Aah, yes you are right. I did not look carefully enough at the desired results example. Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

765 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