Procedure or function? - 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

General SQL Server Forums

New to SQL Server Programming

Procedure or function?

Author

Topic

gabew
Starting Member

3 Posts

Posted - 2008-02-05 : 03:48:11

Trying to get a handle on how to attack this problem of adding a new record in a table that has 2 joins to access (user) and associate with a grandparent table (hotel).

There is also a date condition with a separate table (contracts) in order to start the operation.

Select from Contracts where TermBegin and TermEnd <> today
For each contract hotel{
#Do they already have user?
Match User.ID + Organization.ID + Hotel.ID
If not{
Create Person
}

I'm not sure how to return the negative results of the 3 table join of User.Id + Organization.ID + Hotel.Id

Thanks for any tips/iedas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 03:55:47

Can you post DDL of your tables and some sample data?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 03:59:18

Something similar to this?

INSERT		TargetTable
		(
			UserID,
			OrganizationID,
			HotelID
		)
SELECT		c.UserID,
		c.OrganizationID,
		c.HotelID
FROM		Contracts AS c
LEFT JOIN	TargetTable AS x ON x.UserID = c.UserID
			AND x.OrganizationID = c.OrganizationID
			AND x.HotelID = c.HotelID
WHERE		x.UserID IS NULL
		AND c.TermBegin <= @Today
		AND c.TermEnd >= @Today

I can't give a better answer since there is not enough information in the question.
Please read this blog post http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 04:05:34

quote:
Originally posted by Peso

Something similar to this?
INSERT TargetTable
(
UserID,
OrganizationID,
HotelID
)
SELECT c.UserID,
c.OrganizationID,
c.HotelID
FROM Contracts AS c
LEFT JOIN TargetTable AS x ON x.UserID = c.UserID
AND x.OrganizationID = c.OrganizationID
AND x.HotelID = c.HotelID
WHERE x.UserID IS NULL
AND c.TermBegin <= @Today
AND c.TermEnd >= @Today
I can't give a better answer since there is not enough information in the question.
Please read this blog post http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

Thats why i asked OP to post his DDL & sample data

gabew
Starting Member

3 Posts

Posted - 2008-02-05 : 04:12:28

The main table (hotels) is pretty large.

[Hotels]
HotelID int
SabreID nvarchar(10)
OrganizationID int
OrganizationID_Chain int
PropertyTypeEnum nvarchar(8)

[Users]
UserID int
Username nvarchar
Password nvarchar
Organization int (FK)
PersonID int (FK)

[People]
PersonID int
GivenName nvarchar(50)
FamilyName nvarchar(50)
Email nvarchar(50)
EmployeeId nvarchar(10)

[Organizations]
OrganizationID [int
OrganizationName nvarchar(255)
Notes nvarchar(1000)
PrimaryContactInfoID int
EntityTypeEnum nvarchar(10)

gabew
Starting Member

3 Posts

Posted - 2008-02-05 : 04:55:44

Something similar to this?

Thanks for the help!

I need to create a new person in user table.

The 2 prerequisites are:
1) there must not be an existing associated record in the User table and Hotels
2) Contracts links to Hotels, and Contracts.termbegin (date) <> today

Hotels are linked to Person via Organization table.

(where users.userid = organizations.organizationid and
organizations.organizationid= hotels.organizationid)

The values inserted to create each new user will be
Hotels.hotelid for username.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources