Add missing data for specific column

Question

I have the following tables:

Specialty

SpecialtyId Name IsEnabled

SpecialtyReport

UserId SpecialtyName ReportsRead

I am trying to figure out how to select all the records for a specific user from SpecialtyReport Table and then whatever Name is missing, populate it from Specialty Table and all other values can be null.

What I have tried is this:

SELECT s.Name, t.UserId, t.ReportsRead
FROM Specialty s
LEFT OUTER JOIN SpecialtyReport t ON s.Name = t.SpecialtyName
WHERE t.UserId = 1

However, the result that I get is same as if I did Inner join. The Specialty Table has about 10 rows and all with unique names, where as the SpeialtyReport Table has 3 rows with that User Id.

I can't figure out why the outer left join won't populate missing names. The end result I get from above query is just 3 rows.

EDIT:

The SpecialtyReport Table is made up, it comes from a subquery. I did a test where I created the actual table and my query works on it, but it doesn't work if I replace the table with results from a subquery. Why is that?

can you give sample data and expected output?

Jay Shankar Gupta
– Jay Shankar Gupta

2018-02-21 19:00:33 +00:00
Commented Feb 21, 2018 at 19:00 — Jay Shankar Gupta
– Jay Shankar Gupta, Commented Feb 21, 2018 at 19:00

Serkan Arslan · Accepted Answer · 2018-02-21 19:01:58Z

1

You should write UserId predicate at join part

SELECT s.Name, t.UserId, t.ReportsRead
FROM Specialty s
LEFT OUTER JOIN SpecialtyReport t ON s.Name = t.SpecialtyName
      and  t.UserId = 1

edited Feb 21, 2018 at 19:01

answered Feb 21, 2018 at 19:00

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

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

4 Comments

Bagzli Over a year ago

How does this differ from my query? I'm sorry I can't seem to spot the difference.

Serkan Arslan Over a year ago

I deleted the where part and moved UserId = 1 predicate to join part with and

Jay Shankar Gupta Over a year ago

use ISNULL(userId,'noid')

Serkan Arslan Over a year ago

Yes, it is possible. You can use ISNULL or COALESCE functions. for example ISNULL(t.UserId,'')

Collectives™ on Stack Overflow

Add missing data for specific column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related