Joining 2 tables 1

[rdavis]

I'm pretty new to SQL statements, and I've been trying to join 2 tables. I have two tables, one has building names, and the other has tenant names. I'm trying to join them so I have the Building/Tenant names together. I'm able to do that, but I'm trying to have the Building Name come up by itself as a record. This works by using a LEFT OUTER JOIN, but only if there are no tenants for that building. I want the Building Name to come up by itself as a record even if there are tenants.

Thanks in advance.

Rob

 

[MeanGreen]

The left outer join will give you the proper results. Basically, if there are no tenents then they would show up null.

select building,tenents
from tbl_Build left outer join tbl_Tenent
on tbl_build.bld_id = tbl_Tenent.bld_id

Hope this helps.

 

[rdavis]

Thanks for replying MeanGreen,

What you have is what I got, but I need a record to have a record ull for Tenants, even if there are tenants.

e.g.
result

BuildingName TenantName
------------ ----------
Building1
Building1 Tenant1
Building1 Tenant2
Building2
Building3
Building3 Tenant11

Do you see what I'm looking for now?

Thanks, I'm still trying to figure it out for myself.

Rob

 

[MeanGreen]

Try this:

select building,null as tenents
from tbl_Build
UNION
select building,tenents
from tbl_Build left outer join tbl_Tenent
on tbl_build.bld_id = tbl_Tenent.bld_id

Hope this helps.

 

[rdavis]

That worked MeanGreen, Thanks alot.

I did not know about that AS statement, You learn something new everyday.

Rob