Remove SQL output when value IS NULL

Question

The below SQL query is working for me, but it returns the property value when there staffNo is null 6 times(Once for each staff member). I want to return the property owner even if the staffNo is null, without 6 returns.

SELECT s.branchNo, s.staffNo, s.fName, s.lName, propertyNo, p.staffNo,
       o.fName, o.lName, o.ownerNo
  FROM staff s, propertyforrent p, privateowner o
 WHERE s.staffNo = p.staffNo AND o.ownerNo = p.ownerNo
    OR p.staffNo IS NULL     AND o.ownerNo = p.ownerNo
 ORDER BY s.branchNo, s.staffNo, propertyNo

Results

B003    SG14    David   Ford    PG16    SG14    Tony    Shaw    CO93
B003    SG14    David   Ford    PG4     NULL    Tina    Murphy  CO40
B003    SG37    Ann     Beech   PG21    SG37    Carol   Farrel  CO87
B003    SG37    Ann     Beech   PG36    SG37    Tony    Shaw    CO93
B003    SG37    Ann     Beech   PG4     NULL    Tina    Murphy  CO40
B003    SG5     Susan   Brand   PG4     NULL    Tina    Murphy  CO40
B005    SL21    John    White   PG4     NULL    Tina    Murphy  CO40
B005    SL41    Julie   Lee     PG4     NULL    Tina    Murphy  CO40
B005    SL41    Julie   Lee     PL94    SL41    Carol   Farrel  CO87
B007    SA9     Mary    Howe    PA14    SA9     Joe     Keogh   CO46
B007    SA9     Mary    Howe    PG4     NULL    Tina    Murphy  CO40

Here is the desired result

B003    SG14    David   Ford    PG16    SG14    Tony    Shaw    CO93
B003    SG14                    PG4     NULL    Tina    Murphy  CO40
B003    SG37    Ann     Beech   PG21    SG37    Carol   Farrel  CO87
B003    SG37    Ann     Beech   PG36    SG37    Tony    Shaw    CO93
B005    SL41    Julie   Lee     PL94    SL41    Carol   Farrel  CO87
B007    SA9     Mary    Howe    PA14    SA9     Joe     Keogh   CO46

Thanks in advance!

I did that because of the OR. I wanted it to find the owner if there is a staffNo, or if a staffNo is NULL. Is there another way to state it? — AustinTheNoob
– AustinTheNoob, Commented Jan 23, 2013 at 20:43
can u write the wished result , i dont understand what u mean in your question — echo_Me
– echo_Me, Commented Jan 23, 2013 at 20:44
WHERE (o.ownerNo = p.ownerNo) AND ((s.staffNo = p.staffNo) OR p.staffNo IS NULL)). note the bracketing. — Marc B
– Marc B, Commented Jan 23, 2013 at 20:45
I want to see the Owners first name and last name, and the Sales persons first name and last name. However if the sales person does not exist it did not return the owners last name. Once I added the or statement for IS NULL it showed NULL 6 times. NULL should only appear once, as should Tina Murphy. — AustinTheNoob
– AustinTheNoob, Commented Jan 23, 2013 at 20:47
i mean can you edit your post and put the wished result in table — echo_Me
– echo_Me, Commented Jan 23, 2013 at 20:49

Jackie Robinson · Accepted Answer · 2013-01-23 20:58:40Z

3

Using a left join may be helpful

SELECT s.branchNo, s.staffNo, s.fName, s.lName, propertyNo, p.staffNo,
       o.fName, o.lName, o.ownerNo
  FROM privateowner o
  LEFT JOIN propertyforrent p ON o.ownerNo = p.ownerNo
  LEFT JOIN staff s ON s.staffNo = p.staffNo

 ORDER BY s.branchNo, s.staffNo, propertyNo

edited Jan 23, 2013 at 20:58

answered Jan 23, 2013 at 20:51

Jackie Robinson

944 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AustinTheNoob Over a year ago

I get this from your code #1054 - Unknown column 'p.staffNo' in 'on clause'

AustinTheNoob Over a year ago

That worked! I will message you some questions I have. Thanks again

Mr. Radical · Accepted Answer · 2013-01-23 20:56:02Z

1

Try this:

SELECT s.branchNo, s.staffNo, s.fName, s.lName, propertyNo, p.staffNo,
       o.fName, o.lName, o.ownerNo
  FROM staff AS s LEFT JOIN propertyforrent AS p
  ON s.staffNo = p.staffNo AND  p.staffNo IS NOT NULL
  LEFT JOIN privateowner AS o
 ORDER BY s.branchNo, s.staffNo, propertyNo;

answered Jan 23, 2013 at 20:56

Mr. Radical

1,8381 gold badge20 silver badges30 bronze badges

Comments

Andy Refuerzo · Accepted Answer · 2013-01-23 20:56:45Z

1

Hope this is correct..

SELECT s.branchNo, s.staffNo, s.fName, s.lName, propertyNo, p.staffNo, o.fName, o.lName, o.ownerNo
FROM staff s 
join propertyforrent p on s.staffNo = p.staffNo
left join privateowner o on o.ownerNo = p.ownerNo
where p.staffNo is not null
ORDER BY s.branchNo, s.staffNo, propertyNo

Is the blank columns under David Ford intentional?

answered Jan 23, 2013 at 20:56

Andy Refuerzo

3,3321 gold badge35 silver badges39 bronze badges

1 Comment

AustinTheNoob Over a year ago

It is blank because there is no staffNO for that entry. This gave me everything except the NULL entry, which I would like to return.

Collectives™ on Stack Overflow

Remove SQL output when value IS NULL

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related