CODEWITHSUNDEEP
sqlsql-server
Duncan McKirdy
Duncan McKirdy

Reputation: 141

SQL Server Query Aid

I have a query in SQL Server to return a list of Reports, it has to return either a string representing a location, or a string representing the store it's referencing.

The issue is my query is only returning reports that references a store id, instead of returning all reports and the relevant location information. I'm convinced its a stupid syntax issue, but I haven't done database work for a while, and can't seem to pick it out. I've tried several different ways to get this to work, but it simply refuses.

SELECT rep.rep_id                                                                   AS "RepId",
       Isnull(rep.rep_status, 'C')                                                  AS "RepStatus",
       Isnull(( loc.location_street + ' ' + loc.location_city ), store.Description) AS "Location",
       rep.date_reported                                                            AS "DateReported",
       rep.reported_by                                                              AS "ReportedBy"
FROM   Report rep
       JOIN Report_Location reploc
         ON reploc.rep_id = rep.rep_id
       JOIN Location loc
         ON loc.location_id = reploc.location_id
       LEFT JOIN Store store
              ON store.StoreID = loc.store_id;

I've tried removing the left join and just adding a where loc.store_id = store.StoreID or loc.store_id IS NULL. Neither worked. Thanks in advance for your help.

Upvotes: 0

Views: 243

Answers (2)

Tharif
Tharif

Reputation: 13971

Achieve using Left outer join :

SELECT        rep.rep_id AS RepId, ISNULL(rep.rep_status, 'C') AS RepStatus, ISNULL(loc.location_street + ' ' + loc.location_city, store.Description) AS Location, 
                         rep.date_reported AS DateReported, rep.reported_by AS ReportedBy
FROM            Report AS rep LEFT OUTER JOIN
                         Report_Location AS reploc ON reploc.rep_id = rep.rep_id LEFT OUTER JOIN
                         Location AS loc ON loc.location_id = reploc.location_id LEFT OUTER JOIN
                         Store AS store ON store.StoreID = loc.store_id

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

You need to use LEFT OUTER JOIN instead of Inner Join

SELECT rep.rep_id                                                                   AS "RepId",
       Isnull(rep.rep_status, 'C')                                                  AS "RepStatus",
       Isnull(( loc.location_street + ' ' + loc.location_city ), store.Description) AS "Location",
       rep.date_reported                                                            AS "DateReported",
       rep.reported_by                                                              AS "ReportedBy"
FROM   Report rep
       LEFT JOIN Report_Location reploc
              ON reploc.rep_id = rep.rep_id
       LEFT JOIN Location loc
              ON loc.location_id = reploc.location_id
       LEFT JOIN Store store
              ON store.StoreID = loc.store_id;

Upvotes: 3

Related Questions