• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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