Making a query in a table and in its child tables

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)

Making a query in a table and in its child tables

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-08-29 : 04:12:58

Hi,

How can I make a query in each linked table so that I can use the identity column to make the follow query?

For example:
(In this case each query is independent, it doesn’ t reuse code. And it doesn’ t work.)


SELECT TOP 1 * FROM Offers
ORDER BY Offer_id DESC

SELECT * FROM Offers_quality
WHERE Offer_num In(SELECT TOP 1 Offer_id FROM Offers)
ORDER BY Offer_qual_id DESC

SELECT * FROM Offers_maturation
WHERE Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality
WHERE Offer_num In(SELECT TOP 1 Offer_id FROM Offers)) 
ORDER BY Offer_matur_id DESC

Thank you,
Cesar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 04:25:46

Can you give more information?
Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

cesark
Posting Yak Master

215 Posts

Posted - 2005-08-29 : 06:38:28

These queries failed because it lacked 'ORDER BY Offer_id DESC' here: (in bold)


SELECT TOP 1 * FROM Offers
ORDER BY Offer_id DESC

SELECT * FROM Offers_quality
WHERE Offer_num In(SELECT TOP 1 Offer_id FROM Offers
ORDER BY Offer_id DESC)
ORDER BY Offer_qual_id DESC

SELECT * FROM Offers_maturation
WHERE Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality
WHERE Offer_num In(SELECT TOP 1 Offer_id FROM Offers
ORDER BY Offer_id DESC)) 
ORDER BY Offer_matur_id DESC

Are correct the structure of the queries? Or is there a better way to do it? I mean for example without this very long 'where' clause:


WHERE Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality
WHERE Offer_num In(SELECT TOP 1 Offer_id FROM Offers
ORDER BY Offer_id DESC))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources