Solved

how to do this in query

Posted on 2013-01-28
11
297 Views
Last Modified: 2013-02-11
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
Comment
Question by:wasabi3689
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38829140
>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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38829161
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
 

Author Comment

by:wasabi3689
ID: 38829228
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
ID: 38829264
How about:

SELECT *
FROM Table2, Query1
WHERE Table2.[Name] Like '%' & query1.FieldName & '%'
0
 

Author Comment

by:wasabi3689
ID: 38829282
hi fyed,

I have this returned

The data types varchar and varchar are incompatible in the '&' operator.
0
 

Author Comment

by:wasabi3689
ID: 38829293
query1.FieldName is varchar(64) data type

Table2.[Name] is text data type
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38829344
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
 

Author Comment

by:wasabi3689
ID: 38829385
the same error?

Can I convert this as procedure? I mean, have a while loop then, return each record matched. If so, how?
0
 
LVL 10

Expert Comment

by:deviprasadg
ID: 38830466
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
 
LVL 32

Expert Comment

by:awking00
ID: 38831610
select * from table2 t2
where exists
(select 1 from table1 t1
 where charindex(t1.name,t2.name) > 0);
0
 
LVL 32

Expert Comment

by:awking00
ID: 38831620
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
StoredProcedure to JSON query faulty syntax 2 45
SQL profiler 3 51
Http Post send by Store Procedure Help 5 30
EF5: Update Model from Database not working 3 80
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question