Keeping sub query. - 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

Transact-SQL (2000)

Keeping sub query.

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-02-02 : 20:30:31

Hi there,

If a have a query of the form...

SELECT * FROM table1 WHERE pk IN (SELECT * FROM table2)

Is it possible to return the results from the subquery as well as the primary query?

Cheers,

X.

DavidD
Yak Posting Veteran

73 Posts

Posted - 2004-02-02 : 20:40:44

Your query wouldn't work as your subquery can only return one field
Rewriting it like this will probably achieve what you are after:

select t1.*, t2.*
from table1 t1 join table2 t2 on t1.PK = t2.PK

Regards
David

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-02-02 : 20:58:48

Oh what I'm after is much more grizzley than that!

Basically I'm working on an ecommerce website which displays a page of browse/search results on the same web page as a product detail.

Here is the search results query...


CREATE PROCEDURE GetSearchResults 
(
    @Search varchar(255)
)
AS

SELECT
	productID, 
	p.name, 
	imageThumb, 
	price

FROM 
	products p

INNER JOIN categories c ON p.categoryID = c.categoryID

WHERE 
    	(
    		description LIKE '%' + @Search + '%'
	OR
		p.name LIKE '%' + @Search + '%'
	OR
		c.name LIKE '%' + @Search + '%'
	) 
	AND 
		statusID <> 2

ORDER BY nameID
GO

Furthermore, on one of these pages containing search results and a product detail, there are buttons/links to enable you to move to the next or previous product detail in the search results. The links have on the query string the pk of the current product being viewed plus a flag saying whether you want the next or the prevous item.

So here is a snippet from the query which returns the 'next' product given a pk..


                SELECT TOP 1
			@productID = productID,
			@name= name,
			@mainImage =imageMain,
			@price = price,
			@description = description,
			@nameID = nameID 
		
		FROM
			products

		WHERE 
			nameID > (SELECT nameID FROM products WHERE productID= @productID)
			AND productID in
				(SELECT
					productID

				FROM 
					products p

				INNER JOIN categories c ON p.categoryID = c.categoryID

				WHERE 
    					(
    					description LIKE '%' + @Search + '%'
					OR
					p.name LIKE '%' + @Search + '%'
					OR
					c.name LIKE '%' + @Search + '%'
					) 
					AND 
					statusID <> 2)

		ORDER BY nameID

'NameID' is a calculated field which adds the name of the product to the pk and is used to order the products alphabetically.

So you see, this query looks up the nameID for the given product, looks up all the products which have a higher alphabetical order, orders them and picks up the top one. Furthermore, it makes sure its within the search results.

So by now, you can see, things are getting extreme. Its already doing the search query twice per page request if you click a next/prev button - once for the page of search results and once for the product detail.

But there's more....

I also need to configure the next/previous links to be active or inactive depending on whether there is actually a next or a previous product. Obviously, for example, the first product from the search results doesn't have a previous product.

To do this, I'm getting a count of the total products in the search results and also the total number of products which are alphabetically lower than the selected product. From this data one can determine a product's position in the search results.

Thus here are the queries...


	SELECT @position = COUNT(*) FROM products WHERE nameID <= @nameID AND  productID IN
				(SELECT
					productID

				FROM 
					products p

				INNER JOIN categories c ON p.categoryID = c.categoryID

				WHERE 
    					(
    					description LIKE '%' + @Search + '%'
					OR
					p.name LIKE '%' + @Search + '%'
					OR
					c.name LIKE '%' + @Search + '%'
					) 
					AND 
					statusID <> 2)

	SELECT @total = COUNT(*) FROM products WHERE productID IN
				(SELECT
					productID

				FROM 
					products p

				INNER JOIN categories c ON p.categoryID = c.categoryID

				WHERE 
    					(
    					description LIKE '%' + @Search + '%'
					OR
					p.name LIKE '%' + @Search + '%'
					OR
					c.name LIKE '%' + @Search + '%'
					) 
					AND 
					statusID <> 2)

Thus, per page request, the search query is being run 4 times. Something tells me that this is not good, although it does actually work.

Can anybody get all this stuff into a single query which only does the search once?

X.

HendersonToo
Starting Member

20 Posts

Posted - 2004-02-03 : 16:19:57

Well there's probably more to it than the queries you've shown. This looks like a case to use a temporary table of the search query. This temporary table lasts only for the current connection.


INSERT INTO #SearchResult
SELECT ProductID
FROM products p
INNER JOIN categories c ON p.categoryID = c.categoryID
WHERE (
  description LIKE '%' + @Search + '%'
  OR
  p.name LIKE '%' + @Search + '%'
  OR
  c.name LIKE '%' + @Search + '%'
) AND statusID <> 2)
...
SELECT @position = COUNT(*) FROM products WHERE nameID <= @nameID AND productID IN (SELECT productID FROM #SearchResult)

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-02-03 : 22:20:02

OK, thanks, so I'll use a table variable to store the search results.

I was wondering if anyone knows of a better way to iterate from one product to the next?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources