Solved

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

Posted on 2016-11-25
13
33 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 31

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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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
 
LVL 31

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

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 31

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

22 Experts available now in Live!

Get 1:1 Help Now