Querying based on another Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Querying based on another Query?

Author

Topic

Jeepaholic
Starting Member

36 Posts

Posted - 2002-04-16 : 21:50:13

Hi there...here's what we're trying to accomplish...

Through an original query, we return a list of "variables". This dataset may look like the following:
List01
List02
Hello
Goodbye

The ultimate dataset we're trying to generate will use the above dataset to find all items that look like the following:
List01-GO34
List02-GO23
Hello-GO45
Goodbye-GO3

The obvious common point is the "-GO" string at the end. How do we accomplish this?

Any thoughts are GREATLY appreciated!
Al

Edited by - jeepaholic on 04/16/2002 21:52:52

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-16 : 22:01:22

Sorry - not really clear on what you want.

quote:

The obvious common poin is the "-GO" string

yup - certainly is. How are you getting the "34 or 23 or 45 or 3" on the end? What are these values?

Are you trying to find all matches in table B where results of query A + "-GO" can be matched ie:
where A.value + "-GO%" Like B.value

Or are you trying to return
select A.value + "-GO"

Or what?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-04-17 : 01:09:55

you can use dervied query's

select * from
(select somekey,listno+'-GO'+otherfieldname as listGo from tablename where <conditions> ) a
where listgo <condition>

as rrb suggested, your requriements aren't clear. post your table schema with sample data.

--------------------------------------------------------------

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-17 : 01:47:45

Al,

Think of a Table as a set.
Think of a query result as a set.

In relational theory this is known as "Closure".

If you perform an relational operation on a relation(Union, Project, Divide etc..) you get another relation.

So given this you can "nest" sets...

eg.
[code]
Select * from (Select * from (Select * from Lines) as A) as B
[code]

HTH

DavidM

Tomorrow is the same day as Today was the day before.

Jeepaholic
Starting Member

36 Posts

Posted - 2002-04-19 : 17:59:50

Thanks for the reply's folks!

Sorry for being a bit cryptic...but my solution to the problem is resolved thanks to you folks.

Per suggestion above, I'm using the following statement:

=================
SELECT A.name, B.name
FROM
(SELECT blah1.name
FROM BlahTable1) AS A,
(SELECT blah2.name
FROM BlahTable2) AS B
WHERE B.name LIKE (A.name+'-GO%')
=================

Say, for example...the following are the recordets:

A:
List01
List02
Hello
Goodbye

B:
List01-GO34
List01-GO92
List83-GO23
Hello-GO45
Goodriddance-GO3

The FINAL result set is the following:
List01, List01-GO34
List01, List01-GO92
Hello, Hello-GO45

Thanks again for the quick and insightful responses!
Al

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-04-19 : 22:10:52

Not sure if this would work a little better or not. If I am correct, the characters after and including the - would not matter.

Select a.name, b.name
From Tablea a, Tableb b
Where a.name = LEFT(b.name, Len(a.name))

Jeremy

Jeepaholic
Starting Member

36 Posts

Posted - 2002-04-20 : 15:08:48

Hey Jeremy...with your assumption - yes, that would probably work well!

The caveat is that the "-GO" string is vital as part of the query. For example, there could be a "-STOP" string after some of them that I don't want included in the final dataset.

Thanks for the thoughts!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources