Using results from one query to populate another? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Using results from one query to populate another?

Author

Topic

V8S
Starting Member

3 Posts

Posted - 2011-10-19 : 09:10:49

Hi,

We have a table called Articles where each record contains an article about buying property in a particular area which we display on our website. Each article record has an ID.

Each record also contains fields called Country, Region and PropertyType that describe criteria for properties that relate to that article, so that on our website we can serve a list of properties for sale from our Properties table next to the article text.

At the moment I am reading both the article text and these criteria fields into my ASP page and then passing all the criteria back again into a separate stored procedure in order to generate a recordset to display the correct properties. This seems rather inefficient, and produces problems when those options include the apostrophe character such as Cote d'Azur.

EXAMPLE RECORD

ArticleID = 1

ArticleTitle = Normandy Houses For Sale

Country = France

Region = Normany

PropertyType = House

My question is:

Can I write a single stored procedure that will read the criteria from the Articles record in one SELECT, and then use them in a second SELECT statement to produce the recordset I want?

So, in essence...

SELECT ArticleTitle,Country,Region,PropertyType FROM Articles WHERE ArticleID = 1

SELECT PropertyID,Country,Region,District,PropertyType,Price,Picture,Bedrooms FROM Properties WHERE Country = ArticleCountry AND Region = ArticleRegion AND PropertyType = ArticlePropertyType

This will allow me, in the ASP page, to just call the procedure with just the ArticleID and not worry about the apostrophe problem.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 09:44:43

you can


SELECT p.PropertyID,p.Country,.pRegion,p.District,p.PropertyType,p.Price,p.Picture,p.Bedrooms 
FROM Properties p
INNER JOIN  Articles a
ON a.Country = p.Country
AND a.Region = p.Region
AND a.PropertyType = p.PropertyType 
WHERE ArticleID = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-19 : 09:49:13

You can make the first SELECT a derived table and join it to the second SELECT.

SELECT p.PropertyID,p.Country,p.Region,p.District,p.PropertyType,p.Price,p.Picture,p.Bedrooms
FROM Properties p
join (SELECT ArticleTitle,Country,Region,PropertyType FROM Articles WHERE ArticleID = 1) dt
on dt.Country = p.Country and dt.Region = p.Region and dt.PropertyType = p.PropertyType

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-19 : 09:50:13

Argh - have not seen that there isn't a reason to have a derived table.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources