Query to resolve id names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query to resolve id names

Author

Topic

djavet
Starting Member

36 Posts

Posted - 2004-12-07 : 10:06:18

Hello,

I try to have this for a Office move between several building:

The result for table "move":
id	id_now_gebaude	id_organisation	id_provisorium_standort	id_provisorium_gebaude	provisorium_datum	id_definitiv_standort	id_definitiv_gebaude	definitiv_datum
1	1	1	1	2	22.12.2004	1	4	01.04.2005
2	1	1	21	2	01.02.2005	1	1	01.04.2005
3	2	2	19	3	31.12.2004	1	1	01.05.2005
4	3	3	1	2	22.12.2004	1	4	01.04.2005

... but instead of id_gebaude, id_provisorium_gebaude, id_definitiv_gebaude, I wish the name from the table "gebaude".
Is that possible? I can only retreive the first one, but when I try the second I've no more result...

Table Gebaude:
id | gebaude
1 | Ber-lau10
2 | Ber-lau18
3 | Wor-Ati6
4 | Fri-Ars5

Query:
SELECT 
  move.id_now_gebaude,
  gebaude.gebaude as now_gebaude,
  move.id_organisation,
  organisation.organisation,
  move.id_provisorium_gebaude,
  gebaude.gebaude as provisorium_gebaude,
  move.id_provisorium_standort,
  move.provisorium_datum,
  move.id_definitiv_standort,
  move.id_definitiv_gebaude,
  gebaude.gebaude as definitiv_gebaude,
  move.definitiv_datum
FROM
  move,
  gebaude,
  organisation
WHERE
  (move.id_now_gebaude = gebaude.id)
  and
  (move.id_organisation = organisation.id)
  and
  (move.id_provisorium_gebaude = gebaude.id)
  and
  (move.id_definitiv_gebaude = gebaude.id)

A lot of thx for help and time.

Regards, Dom

quote:
Structure:
CREATE TABLE [gebaude] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[id_standort] [int] NULL ,
[gebaude] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [IX_gebaude] UNIQUE NONCLUSTERED
(
[gebaude]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [move] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[id_now_gebaude] [int] NULL ,
[id_organisation] [int] NULL ,
[id_provisorium_standort] [int] NULL ,
[id_provisorium_gebaude] [int] NULL ,
[provisorium_datum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id_definitiv_standort] [int] NULL ,
[id_definitiv_gebaude] [int] NULL ,
[definitiv_datum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [organisation] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[organisation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [IX_organisation] UNIQUE NONCLUSTERED
(
[organisation]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [standort] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[standort] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [IX_standort] UNIQUE NONCLUSTERED
(
[standort]
) ON [PRIMARY]
) ON [PRIMARY]
GO

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-12-07 : 17:46:52

Hi djavet

You are very close. But you want to include the gebaude table 3 times in your query, becuase you want to join on it in 3 ways - once based on id_now_gebaude, id_provisorium_gebaude and id_definitiv_gebaude.

So you need something like this:

SELECT 
  move.id_now_gebaude,
  a.gebaude as now_gebaude,
  move.id_organisation,
  organisation.organisation,
  move.id_provisorium_gebaude,
  b.gebaude as provisorium_gebaude,
  move.id_provisorium_standort,
  move.provisorium_datum,
  move.id_definitiv_standort,
  move.id_definitiv_gebaude,
  c.gebaude as definitiv_gebaude,
  move.definitiv_datum
FROM
  move,
  gebaude a,
  gebaude b,
  gebaude c,
  organisation
WHERE
  (move.id_now_gebaude = a.id)
  and
  (move.id_organisation = organisation.id)
  and
  (move.id_provisorium_gebaude = b.id)
  and
  (move.id_definitiv_gebaude = c.id)

hope that helps
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

djavet
Starting Member

36 Posts

Posted - 2004-12-08 : 08:27:05

Yeeessss!

Thx a lot.
I've gorget to use aliases...

Great forum!

Regards, Dom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources