Compare Column values

[TSO456]

Hello,

I have a table in SQL server that has two columns with different dollar values. I would like to create a query that looks at each value in column 1 and compares that value to each value in column two.

I would like to extract the values in column 1 that are less than 5% the values in column 2. Remember I have to compare each and every value in column 1 to all of the values in column 2, not just one value to one value.

Many thanks for your help

Jill

 

[dbomrrsm]

SELECT COL1, COL2
WHERE COL1 < (COL2/100*5)

DBomrrsm

 

[TSO456]

Does this compare each and every row in column 1 to all the rows of column 2 ?

 

[dbomrrsm]

if you had a table

Col1 Col2
1 2
3 4

The 1 would be compared with the 2 and the 3 with the 4 and so on down the table

did you want the value in Col1 i.e. 1 to be compared with every value in col2 i.e. 3 and 4 ?

Dbomrrsm

 

[TSO456]

Hi Dbomrrsm

Yes, I want the value in Col1 i.e. 1 to be compared with every value in col2 i.e. 2 and 4. Then after this comparison is done I want the next value in Col1 i.e. 3 to compare with col2 i.e. 2 and 4

Thank you

Jill

 

[dbomrrsm]

SELECT COL1
WHERE COL1 < (SELECT COL2/100*5 from MYTABLE)
FROM MYTABLE

Think this will work - hopefully

DBomrrsm

 

[SQLSister]

You'll probably need to do a cross join. Warning - cross joins are extremely slow.

Questions about posting. See faq183-874