SQL Join with no records found

so I’m trying to do a join using just one table with multiple selects.

The table I’m using looks like this…

  • pivot not working as desired
  • Generate autoincrementing varchar primary key in sql server
  • Can Count(*) ever return null?
  • Conversion Failed Due To Data Overflow (Numeric)
  • SSIS Scripting Component using the CLR
  • SQL SERVER select string from right after a certain character
  •     Floor No. | Apartment No. | Bathroom No. | Size
            1     |       1       |       1      |  30
            1     |       1       |       2      |  20
            1     |       2       |       1      |  30
            1     |       2       |       2      |  40
            1     |       2       |       3      |  60
            2     |       1       |       1      |  30
            2     |       1       |       2      |  20
            2     |       2       |       1      |  30
            2     |       2       |       2      |  40
            2     |       2       |       3      |  60
    

    So basically, each floor has a number of apartments, and each apartment has a number of bathrooms with different sizes. I’m trying to return results which look something like this..

        Floor No. | APT1A | APT1B | APT2A | APT2B | APT2C
           1      |   30  |   20  |   30  |   40  |   60
           2      |   30  |   20  |   30  |   40  |   60
    

    So far I’ve gotten this SQL statement which works..

        SELECT DISTINCT A.FloorNumber, B.Size As APT1A, C.Size AS APT1B 
        FROM BathroomTable A
        inner join BathroomTable B on a.FloorNumber  = B.FloorNumber
        inner outer join BathroomTable C on a.FloorNumber = C.FloorNumber
        WHERE b.ApartmentNumber = 'APT1' AND b.BathroomNumber = 1
        AND (C.ApartmentNumber = 'APT1' AND C.BathroomNumber = 2)
    

    And so on with the joins, which will work as long as I am joining records which exist in the database.. However, sometimes there will be only one bathroom in apartment 1, and the sql query will try to find the data for the second bathroom, and then no results will be returned. Is there any way to insert null values if its not found?

    Thanks, I hope you can understand what I’m trying to do..

  • Update using Self Join Sql Server
  • SQL UPDATE Self-join with compound matching value
  • SQL Sub Queries/Self Join
  • Finding duplicate records with different IDs within a table
  • T-SQL Multiple self-joins in a loop
  • Not quite understanding the query after just shifting column names
  • 2 Solutions collect form web for “SQL Join with no records found”

    Try this:

    SELECT DISTINCT B.FloorNumber, B.Size As APT1A, C.Size AS APT1B 
    FROM ApartementTable A
    inner join BathroomTable B on a.ApartmentNumber  = B.ApartmentNumber AND b.BathroomNumber = 1
    LEFT join BathroomTable C on a.ApartmentNumber = C.ApartmentNumber AND C.BathroomNumber = 2
    WHERE A.ApartmentNumber = 'APT1' 
    
    SELECT DISTINCT A.FloorNumber, B.Size As APT1A, C.Size AS APT1B 
    FROM BathroomTable A
    OUTER join BathroomTable B on a.FloorNumber  = B.FloorNumber
    OUTER join BathroomTable C on a.FloorNumber = C.FloorNumber
    WHERE b.ApartmentNumber = 'APT1' AND b.BathroomNumber = 1 AND (C.ApartmentNumber = 'APT1' AND C.BathroomNumber = 2)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.