s
(call then Main, Building, Location and Unit). Main is the main table and ha
s
several fields. In Main, I have two fields that can reference Unit, and one
field that references Building. Unit also references Building, and Building
references Location
.
In other words, Units belong to a Building and a Building belongs to a
Location. If I have a Unit in Main, I have to go to Unit to get the Building
,
then Building to get the Building related info and the Location.
Table Main has a BuildingCode field and two UnitCode (UCa and UCb) fields.
If there is a UCa value, there should not be a BuildingCode and vice versa.
There may or may not be a value for UCb.
My query needs to return all related information in a single recordset (i.e.
Building.BuildingName & Location.LocationName based on a join from
BuildingCode in Main, and Unit.UnitName, Building.BuildingName and
Location.LocationName based on a join on UnitCode in Main (remember two
differnt places where UnitCode is used in Main and they can be different). I
f
this makes sense to anyone and they can help, I would be eternally grateful,
and if you are ever in the Kansas City are I will buy you dinner.
tia,
Dougdoug,
not sure if i understand completely, it would be much easier for all if you
posted some DDL and sample data.
do your tables look something like this?
create table locations (
location_code char(2) not null primary key,
location_name varchar(10) not null
)
go
create table buildings (
building_code char(2) not null primary key,
location_code char(2) not null foreign key references
locations(location_code),
building_name varchar(10) not null
)
go
create table units (
unit_code char(2) not null primary key,
building_code char(2) not null foreign key references
buildings(building_code),
unit_name varchar(10) not null
)
go
create table main (
main_code char(2) not null primary key,
unit_code_a char(2) null foreign key references units(unit_code),
unit_code_b char(2) null foreign key references units(unit_code),
building_code char(2) null foreign key references buildings(building_code),
check (case when unit_code_a is null then 0 else 1 end + case when
building_code is null then 0 else 1 end = 1)
)
go
and some sample data:
insert locations(location_code, location_name)
select 'L1', 'location 1'
union all
select 'L2', 'location 2'
insert buildings(building_code, location_code, building_name)
select 'B1', 'L1', 'building 1'
union all
select 'B2', 'L2', 'building 2'
insert units(unit_code, building_code, unit_name)
select 'U1', 'B1', 'unit 1'
insert main(main_code, unit_code_a, unit_code_b, building_code)
select 'M1', 'U1', null, null
union all
select 'M2', null, null, 'B2'
(it is unclear what is unit_code_b used for.. could you please explain?)
then we could do it like this:
select coalesce(b1.building_name,b2.building_name) as building_name,
coalesce(l1.location_name,l2.location_name) as location_name,
u.unit_name
from main m
left outer join units u on m.unit_code_a=u.unit_code and m.building_code is
null
left outer join buildings b1 on u.building_code=b1.building_code
left outer join locations l1 on b1.location_code=l1.location_code
left outer join buildings b2 on m.building_code=b2.building_code and
m.unit_code_a is null
left outer join locations l2 on b2.location_code=l2.location_code
dean
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:08311DB6-A81A-4F73-924D-9A8F8143B125@.microsoft.com...
>I need a complex query, but I will try to keep this short. I have four
>tables
> (call then Main, Building, Location and Unit). Main is the main table and
> has
> several fields. In Main, I have two fields that can reference Unit, and
> one
> field that references Building. Unit also references Building, and
> Building
> references Location
> .
> In other words, Units belong to a Building and a Building belongs to a
> Location. If I have a Unit in Main, I have to go to Unit to get the
> Building,
> then Building to get the Building related info and the Location.
> Table Main has a BuildingCode field and two UnitCode (UCa and UCb) fields.
> If there is a UCa value, there should not be a BuildingCode and vice
> versa.
> There may or may not be a value for UCb.
> My query needs to return all related information in a single recordset
> (i.e.
> Building.BuildingName & Location.LocationName based on a join from
> BuildingCode in Main, and Unit.UnitName, Building.BuildingName and
> Location.LocationName based on a join on UnitCode in Main (remember two
> differnt places where UnitCode is used in Main and they can be different).
> If
> this makes sense to anyone and they can help, I would be eternally
> grateful,
> and if you are ever in the Kansas City are I will buy you dinner.
> tia,
> Doug|||Dean and others. Please see [url]http://www.cedar.cr
.cc/images/relationship.jpg[/url]
for a relationship diagram of the tables in question. A brief description of
why the relationships are as they are.
As you can probably tell, this is an inventory (of computers). A building
may or may not have units designated within it. If there are units in the
building, the unit field will be populated, otherwise the building field wil
l
be populated (in tblCIMComputers). The second Unit field is to allow
(usually) a person in another location to log into they system as though
their computer was located in a specific Unit (our login security system als
o
uses this table). As such, there may or may not be a value in this second
Unit field.
The reason for this type of layout is because Units can move from one
building to another. If such a move is made, one record in one table is
changed (i.e. BuildingCode in tblUnits) and all is well.
The constructs of the tables are very straight forward. Only field
definitions, primary and foreign keys. No other type of constraints. The
purpose of the query I am trying to build is to bring back all pertinent dat
a
to provide the person accessing it with a complete overview of all data.
"Dean" wrote:
> doug,
> not sure if i understand completely, it would be much easier for all if yo
u
> posted some DDL and sample data.
> do your tables look something like this?
> create table locations (
> location_code char(2) not null primary key,
> location_name varchar(10) not null
> )
> go
> create table buildings (
> building_code char(2) not null primary key,
> location_code char(2) not null foreign key references
> locations(location_code),
> building_name varchar(10) not null
> )
> go
> create table units (
> unit_code char(2) not null primary key,
> building_code char(2) not null foreign key references
> buildings(building_code),
> unit_name varchar(10) not null
> )
> go
> create table main (
> main_code char(2) not null primary key,
> unit_code_a char(2) null foreign key references units(unit_code),
> unit_code_b char(2) null foreign key references units(unit_code),
> building_code char(2) null foreign key references buildings(building_code)
,
> check (case when unit_code_a is null then 0 else 1 end + case when
> building_code is null then 0 else 1 end = 1)
> )
> go
>
> and some sample data:
> insert locations(location_code, location_name)
> select 'L1', 'location 1'
> union all
> select 'L2', 'location 2'
> insert buildings(building_code, location_code, building_name)
> select 'B1', 'L1', 'building 1'
> union all
> select 'B2', 'L2', 'building 2'
> insert units(unit_code, building_code, unit_name)
> select 'U1', 'B1', 'unit 1'
> insert main(main_code, unit_code_a, unit_code_b, building_code)
> select 'M1', 'U1', null, null
> union all
> select 'M2', null, null, 'B2'
> (it is unclear what is unit_code_b used for.. could you please explain?)
>
> then we could do it like this:
> select coalesce(b1.building_name,b2.building_name) as building_name,
> coalesce(l1.location_name,l2.location_name) as location_name,
> u.unit_name
> from main m
> left outer join units u on m.unit_code_a=u.unit_code and m.building_code i
s
> null
> left outer join buildings b1 on u.building_code=b1.building_code
> left outer join locations l1 on b1.location_code=l1.location_code
> left outer join buildings b2 on m.building_code=b2.building_code and
> m.unit_code_a is null
> left outer join locations l2 on b2.location_code=l2.location_code
>
> dean
> "Doug" <dbb211-hd204@.yahoo.com> wrote in message
> news:08311DB6-A81A-4F73-924D-9A8F8143B125@.microsoft.com...
>
>|||btw, I am using SQL Server 2000.
Doug|||FIrst, I am not trying to be sarcastic, just giving some friendly advice to
you and other posters that post questions without the necessary code to
allow us to help you quickly.
The diagram is nice but, If we are to help you write a query and test it to
make sure it does what you want, we need to have the tables created on our
database with the right structure and have sameple data in them.
This can happen in 2 ways:
1. We read your diagram and create the tables, relationships, etc from them
and then enter some sample data
2. You can provide us with the SQL script to accomplish this and save us a
lot of time
Which do you think will get you more help quicker? :-)
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:96AB7DF5-EE89-41B8-8E93-224EDE8C0564@.microsoft.com...
> Dean and others. Please see
> [url]http://www.cedar.cr
.cc/images/relationship.jpg[/url]> for a relationship diagram of the tables in question. A brief description
> of
> why the relationships are as they are.
> As you can probably tell, this is an inventory (of computers). A building
> may or may not have units designated within it. If there are units in the
> building, the unit field will be populated, otherwise the building field
> will
> be populated (in tblCIMComputers). The second Unit field is to allow
> (usually) a person in another location to log into they system as though
> their computer was located in a specific Unit (our login security system
> also
> uses this table). As such, there may or may not be a value in this second
> Unit field.
> The reason for this type of layout is because Units can move from one
> building to another. If such a move is made, one record in one table is
> changed (i.e. BuildingCode in tblUnits) and all is well.
> The constructs of the tables are very straight forward. Only field
> definitions, primary and foreign keys. No other type of constraints. The
> purpose of the query I am trying to build is to bring back all pertinent
> data
> to provide the person accessing it with a complete overview of all data.
> "Dean" wrote:
>
No comments:
Post a Comment