• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

how to do this in query

I have a query like below

query 1

select name from table1 where id=....

will return a list of name


query 2

select * from table2 where table2.id like '%query1%'

this doesn't work. How to do query 2 to return the records that match any query 1 returned???
0
wasabi3689
Asked:
wasabi3689
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>How to do query 2 to return the records that match any query 1 returned???

SELECT name
FROM Query2
WHERE id IN (SELECT id FROM Query1)

btw the attached image will help
SQL Joins
0
 
Dale FyeCommented:
But a quicker way (generally) would be:

SELECT Query2.name
FROM Query2
INNER JOIN (SELECT id FROM Query1) as Temp
ON Query2.ID = Temp.ID
0
 
wasabi3689Author Commented:
this is not exact what I want. Let me clear out here


There is no relationship between query 1 and query 2. But in query 2, the field called "name" contains word from the name returned from query 1.

I want to return any records whose name field contain the word from query 1

query 1

select name from table1 where id=....

will return a list of name


query 2

select * from table2 where table2.name like '%query1%
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Dale FyeCommented:
How about:

SELECT *
FROM Table2, Query1
WHERE Table2.[Name] Like '%' & query1.FieldName & '%'
0
 
wasabi3689Author Commented:
hi fyed,

I have this returned

The data types varchar and varchar are incompatible in the '&' operator.
0
 
wasabi3689Author Commented:
query1.FieldName is varchar(64) data type

Table2.[Name] is text data type
0
 
Dale FyeCommented:
My experience with this aspect of SQL Server is limited. You might be able to use either the CAST( ) or CONVERT( ) SQL functions, something like:

SELECT *
FROM Table2, Query1
WHERE Cast(Table2.[Name] as varchar(64)) Like '%' + query1.FieldName + '%'
0
 
wasabi3689Author Commented:
the same error?

Can I convert this as procedure? I mean, have a while loop then, return each record matched. If so, how?
0
 
deviprasadgCommented:
Please check this:
SELECT distinct T2.* FROM table1 T1 CROSS JOIN table2 T2
WHERE T2.name LIKE ('%'+T1.name+'%')

Open in new window


Also you can add the condition on id
SELECT distinct T2.* FROM table1 T1 CROSS JOIN table2 T2
WHERE T2.name LIKE ('%'+T1.name+'%')
and t1.id=..

Open in new window

0
 
awking00Commented:
select * from table2 t2
where exists
(select 1 from table1 t1
 where charindex(t1.name,t2.name) > 0);
0
 
awking00Commented:
Might need AS keyword for table aliases -
select * from table2 AS t2
where exists
(select 1 from table1 AS t1
 where charindex(t1.name,t2.name) > 0);
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now