SQL Newbie needs suggestion 2

[CandyT]

I need to write a query to select data from a table, and then go back and select data from the same table based on different criteria excluding any data selected in the previously run select statement. For example,

I have a two tables:

Table 1 has three columns: Name 1, Name 2, Data 1
Table 2 has one column of names: Names

I need to select all rows from Table 1 where Table 1.Name 2 = Table 2.Names. Next I need to go back and select from Table 1 (excluding any rows returned by the first select statement) all data where Table 1.Name 1 = Table 2.Names.

I hope that explains it, can you suggest something?

Thank you!!!

Candy T

 

[dodge20]

I think what you want is a union query. I believe the union will automatically remove any duplicate records, if not let me know and I will help you. I think you can also use a select distinct statement and include both conditions in an or statement.


select * from table 1
where Name 2 in (select names from table 2)
UNION
select * from table 1
where Name 1 in (select names from table 2)




Dodge20

 

[CandyT]

The reason a straight Union query won't work is this:

the list of names in Table 2 can be as follows:

John Smith
Joe Brown
Ginny Robinson

Table 1.Name 1 can be John Smith and on that same row, Table 1.Name 2 can be Joe Brown. A Union query will select both, matching my names (correctly) to data in both columns. However, I only need it to match the first one I specified and ignore the second match. This is why I need to go through and select anything matching Table 1.Name 2 first and then go back and only select anything matching Table 1.Name 1 that was not selected in the previous statement. Confusing, I know.

 

[r937]

select Name1, Name2, Data1
from Table1
inner join Table2
on Table1.Name1 = Table2.Names
where not exists
( select 1
from Table2
where Names = Table1.Name2 )

rudy

 

[dodge20]

Rudy

Can you explain the 1 in the last part of your query? What is the difference between selecting 1 or * or null?

Dodge20

 

[r937]

sure

there's actually no difference!!

an EXISTS subquery always returns TRUE or FALSE

it doesn't matter what it selects from the row, only that it finds (or doesn't find) a row meeting the WHERE criteria

neat, eh?

 

[dodge20]

Thank you, I have been spending quite a bit of time trying to figure that out.

Dodge20

 

[CandyT]

Rudy:

I love the tip and will have infinite uses for it. Thank you!!

Unfortunately, it still doesn't quite do what I need. The Exists sub query would have to look for matches outside of the specific row. The match might exist on another row in the column of names.

I am still searching......

 

[dodge20]

I am not sure if I am understanding you correctly, see if this definition of an inner join is more along the lines of what you need.

An inner join selects rows from two tables such that the value in one column of the first table also appears in a certain column of the second table.

Dodge20

 

[r937]

CandyT, no, the subquery is correlated but does look for other rows besides the one it's "sitting on"

did you run it?

if it's not actually doing what you want, please give some actual rows from each table so i can build sample tables to test on...


rudy

 

[CandyT]

Rudy:

I fyou are willing to share an email address I can send excerpts of the table and the query and results I got.

CandyT
(Candy.Turbeville@wachovia.com)

 

[CandyT]

Rudy's solution was correct as stated above for anyone wanting to follow up.

CandyT