Solved

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

Posted on 2016-11-25
13
67 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 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 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