Query help

[waynew]

I have three columns. The first is the primary key id. The second two columns are what I'm interested in.

 

[/td]		20	827233677	734812262
	14	734812262	827233677

The example above shows "a match". Basically, what I'm looking for is to be able to select only the rows that have an opposite. i.e. row #20 has 827233677 in col_a and 734812262 in col_b. row #14 has 734812262 in col_a and 827233677 in col_b

[waynew]

Bump

 

[eli312]

not 100% sure what you mean but.

 

So you only want to pull from the DB the rows were col_a and col_b do not match?

[PFMaBiSmAd]

This is just off of the top of my head, but you can probably join the table to itself (giving every row combination) then pick the rows where the the left table column a = right table column b AND left table column b = right table column a.

 

Untested but should be close -

SELECT id, col_a, col_b FROM your_table  as t1 JOIN your_table as t2 WHERE t1.col_a = t2.col_b AND t1.col_b = t2.col_a

 

 

[geekthree]

If you are indeed only looking the rows selected, wouldn't you simply format a query like:

 

SELECT Col_2, Col_3

FROM table_name

WHERE Col_2 != Col_3

 

 

Using a while loop to throw the contents out into html?

 

Or perhaps you're looking to do something different and I'm missing the point (I'm assuming when you said "opposites" you meant different).

 

Mike

 

[geekthree]

I think I've just worked out what you're asking (that was like a puzzle.. I like puzzles though).

 

You want to pick any two rows where either A or B match each other, but not on the same row..

 

I think PFMaBiSmAd's query would do the job, but I think it should be an OR, not an AND:

 

SELECT id, col_a, col_b 
FROM your_table  as t1 
JOIN your_table as t2 
WHERE t1.col_a = t2.col_b AND t1.col_b = t2.col_a

 

In my brain (currently a little caffeine deprived..) this would only select a row if it looked something like this:

 

t1.col_a            t1.col_b

2222222          2222222

2222222          2222222

 

You're looking to evaluate on either:

 

t1.col_a            t1.col_b

2222225          2222222

2222222          2222225

 

or

 

t1.col_a            t1.col_b

2222222      2222226

2222226    2222222

 

 

Let me know if that made ANY sense.. I think I may have succeeded in confusing myself

[PFMaBiSmAd]

According to his statement and his example, he wants both opposite values to match.

[geekthree]

Bah, you're right PFM!.

 

I've looked at the Key in the example on the second row and thought it was part of the second column  .

 

Probably should have gotten that cup of coffee I was thinking of before I looked at that one