Comparing 2 columns in a particular table

[shilpashetty27]

Hi,

I'm trying to compare two columns in one particular table. To be more specific, I have 3 columns as shown below:

Column 1 Column2 Column3
1 abcd efgh
2 efgh mnop
3 ijkl abcd
4 mnop pqrs
tuvw
ijkl

I want to verify if all the values in Column2 exist in Column3.
Any ideas on how i can go about this are appreciated.
Thanks in advance!
cheers,
Shilpa

 

[Remou]

You can do a self-join on the table, something like:

SELECT t1.Column2 From tblTable t1 WHERE t2.Column3 Is Null
LEFT JOIN tblTable t2 ON t1.Column2=t2.Column3

 

[BigRed1212]

What about something like:

SELECT a.[column1], a.[column2], a.[column3]
FROM testtable a 
WHERE a.column2 not in (select distinct testtable.column3 from testtable)
[code]

If I did it right I think that returns any record in which the column 2 value is not in column 3.

 

[shilpashetty27]

Hi,
Thanks for your responses

I have a couple questions:

1) In Remou's reponse, I notice you have used two tables (t1 and t2) but my data is all in just one table. Perhaps, I'm missing something. More clarifications are appreciated.


2) In BigRed1212's response, I notice in the FROM statement, you typed FROM testable a. Are you specifying 2 tables too?
Again, clarifications are appreciated.


Thanks again!
Shilpa

 

[Remou]

I have use only one table but each has an alias. You will see the same thing if you add two copies of your table to the query design screen. Once you have done that, you can drag the second column to the third column and set the relationship to show all records from the first copy (LEFT JOIN). After that, add column3 from the second copy to the grid and set the criteria to Is Null. That is what the SQL I posted indicates.

 

[BigRed1212]

What Remou said about the aliases.

You can think about the statement:

FROM testtable a

as the same as "from testtable AS a" just like in a select statement where you might have "select var1 as a_more_meaningful_name".

It is shorthand. Once you you do it, you don't have to keep writing the name of the table over and over. For example, because I did "from testtable a" I can refer to the fields as a.field1, a.field2, etc instaed of having to write out testtable.field1, testtable.field2 etc.

Remous did the same thing. I used a and b. He used t1 and t2.

HTH.