Solved

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

Posted on 2016-11-25
13
51 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
  • 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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 200 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 32

Accepted Solution

by:
awking00 earned 300 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 300 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

10 Experts available now in Live!

Get 1:1 Help Now