Union query ... is what I want possible?

[newtosql]

If I have the following...

SELECT DISTINCT STREET
FROM TableA
UNION
SELECT DISTINCT STREET DEPENDENT_STREET
FROM TableA
WHERE STREET IS NOT NULL AND DEPENDENT_STREET IS NOT NULL

...and for each result returned I want to do

SELECT TEXT_ID
FROM TableB
WHERE STREET=['a parameter I will specify']

How would I combine the two into a single statement? Is it possible, or is it a bit too complicated?

Thanks for your help!

 

[swampBoogie]

Your union query does not make any sense to me. And what has tableB to do with the first query?

Is dependent_street a column in tableA?

Could you give table descriptions, sample data and the expected result?


Using distinct will not change anything as union will remove all duplicates.

 

[nread]

Do these two tables relate in anyway...?

Are you trying to get a distinct list of Streets from two tables and compare to another one...?

 

[newtosql]

Sorry, I wasnt very clear on that!

TableA contains column named 'street' which is common to TableB.

The first query...
SELECT DISTINCT STREET
FROM TableA
UNION
SELECT DISTINCT STREET DEPENDENT_STREET
FROM TableA
WHERE STREET IS NOT NULL AND DEPENDENT_STREET IS NOT NULL

... will produce results column of say:
ABBERLEY DRIVE
ABBERSTON WALK
ABBOTS LEA
ABINGDON ROAD
and so on.

I want these results to be my parameter for a second query...
SELECT TEXT_ID
FROM TableB
WHERE STREET=[Parameter from tableA results ie, Abberley Drive]

Running 2 queries is time consuming and I wondered if there was another way that I could merge the two queries into one piece of code? Im not sure if its possible or if Im simply 'failing' to see what I need to do!

Hope this helps!

 

[swampBoogie]

You can write queries such as

SELECT TEXT_ID
FROM TableB
WHERE STREET in (
SELECT STREET
FROM TableA
WHERE STREET IS NOT NULL 
AND DEPENDENT_STREET IS NOT NULL)

 

[nread]

CREATE PROCEDURE dbo.sp_STREET
AS
DECLARE @Distinct_Table TABLE (vchrDistSt Varchar(50))

INSERT INTO @Distinct_Table
SELECT DISTINCT STREET
FROM TableA
UNION
SELECT DISTINCT STREET DEPENDENT_STREET
FROM TableA
WHERE STREET IS NOT NULL AND DEPENDENT_STREET IS NOT NULL

SELECT TEXT_ID
FROM TableB, @Distinct_Table
WHERE STREET = vchrDistSt

GO