CODEWITHSUNDEEP
sqlsql-serverself-join
Daniel McWilliams
Daniel McWilliams

Reputation: 23

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...

    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..

Upvotes: 1

Views: 69

Answers (2)

sateesh kumar
sateesh kumar

Reputation: 19

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)

Upvotes: 0

mehdi lotfi
mehdi lotfi

Reputation: 11571

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'

Upvotes: 1

Related Questions