Can this be done with SQL? - 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)

Can this be done with SQL?

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2007-03-13 : 21:00:03

Hi all

I was wondering if what I am trying to do is actually achieveable with SQL or do I need to use a scripting language like ColdFusion?

I have a SQL query which returns a variable called DisplayName from a table called Format. I want to then return another query which returns a variable called Name from a table called Attributes, but only where Name is LIKE DisplayName

So the SQL (in my mind) would look something like this:

SELECT Attributes.Name FROM Attributes WHERE Attributes.Name LIKE '% (SELECT Format.DisplayName FROM Format) %'

The above doesn't work for me, but hopefully it conveys what I'm trying to do. Is it possible to use LIKE with Subqueries like this? I couldn't find anything about it in my SQL books.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 21:03:24

yes


select a.[Name]
from   Attributes a inner join Format f
on     a.[Name] like '%' + f.Displayname + '%'

token
Posting Yak Master

133 Posts

Posted - 2007-03-14 : 14:36:54

Hi khtan, thanks for the reply!

I think I need to show more code, because I made the problem sound really simply and the code you gave seems to work but my computer is lagging severely when it tries to execute it.

This is the code I am using which gives me the results as DisplayName:

SELECT f1.DisplayName
FROM dbo.Products p1, dbo.Format f1
WHERE f1.UNSPSC = p1.UNSPSC
AND p1.Manufacturer = URL.Manufacturer
AND p1.Partno = URL.Partno
AND f1.Overview = '1'
ORDER BY f1.ColOrder

So I now need to run a query based on the results from the query above that returns the field Name from a table called Attributes where Name is LIKE the DisplayName result returned.

So in my mind I think it should go something like this:

SELECT a1.Name FROM Attributes a1 WHERE a1.Name LIKE '%
(SELECT f1.DisplayName
FROM dbo.Products p1, dbo.Format f1
WHERE f1.UNSPSC = p1.UNSPSC
AND p1.Manufacturer = URL.Manufacturer
AND p1.Partno = URL.Partno
AND f1.Overview = '1'
ORDER BY f1.ColOrder)
'%

I know the above query is compeltely and utterly wrong, but I'm not sure how to go about it. I've looked at two SQL books and they don't mention anything about using the LIKE clause in a nested query. I would be very thankful for any help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 14:43:20

URL table is not defined...

Why don't you give us sample data and expected output instead?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 14:44:32

You can use EXISTS keyword.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 14:45:55

SELECT Attributes.Name FROM Attributes WHERE EXISTS (SELECT 1 FROM Format WHERE Attributes.Name LIKE '%' + Format.DisplayName + '%')

Peter Larsson
Helsingborg, Sweden

token
Posting Yak Master

133 Posts

Posted - 2007-03-14 : 14:48:35

Sorry - anything beginning URL is a URL parameter passed in the URL.

So in the code it reads #URL.Partno# for example which tells ColdFusion to grab it from the URL in the address bar.

token
Posting Yak Master

133 Posts

Posted - 2007-03-14 : 14:51:52

Okay, here is what I would like the results to look like

DisplayName    |     Name
------------------------------
Hard Disk          Hard Disk Drive
CPU                CPU Processor
Battery Life       Battery

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 15:01:35

Great! Now we know what you want.
But what do you have? Please post sample data...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 15:03:53

[code]SELECT a.Name
FROM Attributes AS a
INNER JOIN (
SELECT f1.DisplayName
FROM dbo.Products AS p1
INNER JOIN dbo.Format AS f1 ON f1.UNSPSC = p1.UNSPSC AND f1.Overview = '1'
WHERE p1.Manufacturer = #URL.Manufacturer#
AND p1.Partno = #URL.Partno#
) AS x ON a.Name LIKE '%' + x.DisplayName + '%'
ORDER BY a.Name[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 15:04:48

[code]SELECT a.Name
FROM Attributes AS a
WHERE EXISTS (
SELECT NULL
FROM dbo.Products AS p1
INNER JOIN dbo.Format AS f1 ON f1.UNSPSC = p1.UNSPSC AND f1.Overview = '1'
WHERE p1.Manufacturer = #URL.Manufacturer#
AND p1.Partno = #URL.Partno#
AND a.Name LIKE '%' + x.DisplayName + '%'
)
ORDER BY a.Name[/code]

Peter Larsson
Helsingborg, Sweden

token
Posting Yak Master

133 Posts

Posted - 2007-03-14 : 16:05:21

I keep getting this error now:

"The data types varchar and text are incompatible in the add operator. "

token
Posting Yak Master

133 Posts

Posted - 2007-03-14 : 16:09:43

So I changed DisplayName from text to varchar(MAX)

and now I get this:

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 16:10:45

Which column of a.Name and x.DisplayName is TEXT and which is VARCHAR?
Is TEXT datatype really necessary?

Peter Larsson
Helsingborg, Sweden

token
Posting Yak Master

133 Posts

Posted - 2007-03-14 : 18:19:19

Right I have changed every column to datatype VARCHAR.

The following query is pretty much what I need:

SELECT DISTINCT a1.Name
FROM dbo.Attributes a1
WHERE a1.Name IN (
SELECT f1.DisplayName
FROM dbo.Products p1, dbo.Format f1
WHERE f1.UNSPSC = p1.UNSPSC
AND p1.Manufacturer = 'Toshiba'
AND p1.Partno = 'PPM41E-00H01CHK'
AND f1.Overview = '1')

But the above query is only returning the value a1.Name

I want it to return f1.DisplayName and a1.Name. How can this be done?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 18:35:18

This?

SELECT		a.Name,
		x.DisplayName
FROM		Attributes AS a
INNER JOIN	(
			SELECT		f1.DisplayName
			FROM		dbo.Products AS p1
			INNER JOIN	dbo.Format AS f1 ON f1.UNSPSC = p1.UNSPSC AND f1.Overview = '1'
			WHERE		p1.Manufacturer = #URL.Manufacturer#
					AND p1.Partno = #URL.Partno#
		) AS x ON a.Name LIKE '%' + x.DisplayName + '%'
ORDER BY	a.Name

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources