Three table query problem. - 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)

Three table query problem.

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2008-04-22 : 11:25:44

I have 3 tables, one called 'Table1' another called 'Point' and another called 'Base'.

I query table1 and get 100 records using the following sql:

SELECT * FROM table1

WHERE date >=dateadd(day,datediff(day,0,GetDate())- 6,0)
Order By date

Once I have all the records using the above query which could be 100 records I have to do a query using each record, each record has unique ID number. I have to query each record individually to get the data from a number of fields within the record. One field is called 'Town' and another is called 'Area' and it contains a letter either 'A' or 'B', if it contains 'A' then we must get whatever is another field called 'Unit', if 'B' is present then we do not query the 'Unit' field.

The table called 'Point' has 2 fields called ID which corresponds to the ID of the records in 'Table1' and a field called Town.
I need to use the ID number field from each record to read the coresponding Id field in the table called 'Point' and therefore
to get the appropriate Townland for each record in 'table1'.

The table called 'Base' has 2 fields one called Town which corresponds to the Town field in the 'Point' table and another called
Unit which muct be updated with the data from the unit field in 'Table1'.

Basically I am linking between 3 tables to update one with the information from another. I must do this for all 100 records
individually gotten using the query above.
I know this involves some looping but do not have a clue how to do it.

maybe you may know how to do this advanced sql.

Thanks,
macca

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 12:21:30

What is this reading comprehension?

1-5 of this link explains how to post your question in an easier to understand format...

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

You won't have to do any looping by the way.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-22 : 13:49:41

Follow Ryan's advice and post your table structure and sample data. Untill then, here is a quick query I put together based on your description (not this is untested and probably isnot right).

UPDATE
	T
SET
	T.Unit = B.Unit
FROM
	Table1 AS T
INNER JOIN
	Point AS P
	ON T.ID AND P.ID
	AND T.Town = P.Town
INNER JOIN
	Base AS B
	ON P.Town = B.Town
WHERE
	T.Area = 'A'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-22 : 13:51:35

I missed that you wanted to update Base with the Unit from Table1.

UPDATE
	B
SET
	B.Unit = T.Unit
FROM
	Table1 AS T
INNER JOIN
	Point AS P
	ON T.ID AND P.ID
	AND T.Town = P.Town
INNER JOIN
	Base AS B
	ON P.Town = B.Town
WHERE
	T.Area = 'A'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources