VBE_programmer


Hi,

I am new about T sql querys.

I have one table with two columns

col1 col2

---------------------

A B

B A

C D

D C

P Q

Q P

I would like to display the result in the following way using in one query

In my view A-->B and B--->A are same and C-->D and D->C ..etc

Col1 col2

------------------------

A B

C D

P Q

Any body help me please

Thanks





Re: Simple Query on one table

Manivannan.D.Sekaran


here You go..

Code Snippet

Create Table MyData (

[col1] Varchar(100) ,

[col2] Varchar(100)

);

Insert Into MyData Values('A','B');

Insert Into MyData Values('B','A');

Insert Into MyData Values('C','D');

Insert Into MyData Values('D','C');

Insert Into MyData Values('P','Q');

Insert Into MyData Values('Q','P');

Go

-- on SQL Server 2000

Select * , Identity(int, 1, 1) as RowId INTO #Data From MyData

Select L.Col1, L.Col2 From #Data L Join #Data R On L.Col1 = R.Col2 And L.RowId < R.RowId

-- on SQL Server 2005

;With CTE

as

(

Select *, Row_Number() OVER (Order By Col1, Col2) as RowId From MyData

)

Select L.Col1, L.Col2 From CTE L Join CTE R On L.RowId < R.RowId and L.Col1 = R.Col2







Re: Simple Query on one table

Chris Howarth

Try the code below.

Chris

Code Snippet

DECLARE @MyTable TABLE

(

[col1] VARCHAR(100),

[col2] VARCHAR(100)

)

INSERT INTO @MyTable VALUES('A','B')

INSERT INTO @MyTable VALUES('B','A')

INSERT INTO @MyTable VALUES('C','D')

INSERT INTO @MyTable VALUES('D','C')

INSERT INTO @MyTable VALUES('P','Q')

INSERT INTO @MyTable VALUES('Q','P')

SELECT [col1], [col2]

FROM @MyTable

WHERE [col1] <= [col2]







Re: Simple Query on one table

VBE_programmer

Apolozies for my mistake.

I am using Mysql.I want the result in Mysql.

Please give me the query in Mysql format.

I need to use the query in Vb6 program.

Thanks for the reply.






Re: Simple Query on one table

VBE_programmer

Hi chris,

Thanks for reply.

SELECT [col1], [col2]

FROM @MyTable

WHERE [col1] <= [col2]

This query not giving all the rows.






Re: Simple Query on one table

Chris Howarth

This isn't the best place to ask for assistance with MySQL as this is a Microsoft SQL Server forum, however the code below should work on pretty much any SQL database engine.

Chris

Code Snippet

SELECT col1, col2

FROM <insert your table name here>

WHERE col1 <= col2






Re: Simple Query on one table

rh4m1ll3

just out of curiosity, what are your actual values

do you have values like,

col1 col2
"the quick brown fox" "the quick bown dog"
"brown the quick dog" "brown the quick fox"

and the correct result would be what

col1 col2
"the quick brown fox" "the quick bown dog"






Re: Simple Query on one table

Chris Howarth

Could you provide examples of rows that are missing Do these rows contain NULL in at least one column

Also, will you always have opposing pairs of values, i.e (A, B) and (B, A) or could your data contain only (A, B) or (B, A)

Chris






Re: Simple Query on one table

VBE_programmer

Sorry for posting this mysql query here.

I just want the help for sql query.

And i am working on postcodes

Here i got two type of results

(1..)

for example if i take this table

+----------+----------+
| pc | resultpc |
+----------+----------+
| AB52 6XN | AB53 8RL |
| AB53 8RL | AB52 6XN |
| AB53 8RL | AB53 8RL |
+----------+----------+

For the above table if i am using


SELECT pc,resultpc

FROM PcTest1

WHERE pc <= resultpc

the result is

+----------+----------+
| pc | resultpc |
+----------+----------+
| AB53 8RL | AB53 8RL |
+----------+----------+

For example i use like this table

+---------+----------+
| pc | resultpc |
+---------+----------+
| ML5 2QT | FK1 4BQ |
| ML5 2QT | FK1 4NN |
| ML5 2QT | FK1 5EJ |
| ML5 2QT | FK5 3JD |
| ML5 2QT | FK5 4QF |
| ML5 2QT | FK6 5HS |
| ML5 2QT | FK6 6PG |
| ML5 2QT | G1 2RN |
| ML5 2QT | G12 9BD |
| ML5 2QT | G20 7QF |
| ML5 2QT | G21 3LY |
| ML5 2QT | G22 6LJ |
| ML5 2QT | G3 8AU |
| ML5 2QT | G3 8EP |
| ML5 2QT | G32 0HS |
| ML5 2QT | G32 8UN |
| ML5 2QT | G32 9JQ |
| ML5 2QT | G33 2ET |
| ML5 2QT | G40 1DS |
| ML5 2QT | G40 2AB |
| ML5 2QT | G40 3DP |
| ML5 2QT | G41 1TG |
| ML5 2QT | G41 3UQ |
| ML5 2QT | G42 8EL |
| ML5 2QT | G42 9QE |
| ML5 2QT | G45 9SS |
| ML5 2QT | G46 6JB |
| ML5 2QT | G5 8HX |
| ML5 2QT | G64 1ND |
| ML5 2QT | G64 1XP |
| ML5 2QT | G64 4AH |
| ML5 2QT | G65 0AB |
| ML5 2QT | G66 1PN |
| ML5 2QT | G66 3UU |
| ML5 2QT | G67 4BG |
| ML5 2QT | G67 4JX |
| ML5 2QT | G68 9AA |
| ML5 2QT | G69 0PA |
| ML5 2QT | G71 6NZ |
| ML5 2QT | G71 7HN |
| ML5 2QT | G72 7TS |
| ML5 2QT | G72 7TS |
| ML5 2QT | G73 5NF |
| ML5 2QT | G74 1PW |
| ML5 2QT | G74 3HY |
| ML5 2QT | G74 3SH |
| ML5 2QT | G74 3XH |
| ML5 2QT | G74 4LY |
| ML5 2QT | G76 8NN |
| ML5 2QT | G76 9AN |
| ML5 2QT | ML1 2AQ |
| ML5 2QT | ML1 2BD |
| ML5 2QT | ML1 2LD |
| ML5 2QT | ML1 3ST |
| ML5 2QT | ML1 5AD |
| ML5 2QT | ML1 5RX |
| ML5 2QT | ML2 0JQ |
| ML5 2QT | ML2 7DX |
| ML5 2QT | ML3 7HL |
| ML5 2QT | ML3 8HJ |
| ML5 2QT | ML3 9BG |
| ML5 2QT | ML4 1HY |
| ML5 2QT | ML4 1RR |
| ML5 2QT | ML4 3NX |
| ML5 2QT | ML5 2BN |
| ML5 2QT | ML5 2BX |
| ML5 2QT | ML5 2QT |
| ML5 2QT | ML7 4EQ |
| ML5 2QT | ML7 5AR |
| ML5 2QT | ML7 5ES |
| ML5 2QT | ML8 5ET |
+---------+----------+Here in the above table

there no combinations like A->B or B->A combinations


If i am using

SELECT pc,resultpc

FROM PcTest1

WHERE pc <= resultpc

It is giving result like..

+---------+----------+
| pc | resultpc |
+---------+----------+
| ML5 2QT | ML5 2QT |
| ML5 2QT | ML7 4EQ |
| ML5 2QT | ML7 5ES |
| ML5 2QT | ML7 5AR |
| ML5 2QT | ML8 5ET |
+---------+----------+

Why i am getting this result







Re: Simple Query on one table

Chris Howarth

This works in SQL Server, not sure about MySQL, but give it a try...

Chris

Code Snippet

SELECT t1.[col1],

t1.[col2]

FROM @MyTable t1

WHERE t1.[col1] <= t1.[col2]

OR NOT EXISTS ( SELECT 1

FROM @MyTable t2

WHERE t2.[col1] = t1.[col2]

AND t2.[col2] = t1.[col1] )






Re: Simple Query on one table

VBE_programmer

I tried this query on my database.It is still running

And i run this query on my sample table

The table is:

+-------+-------+
| test1 | test2 |
+-------+-------+
| A | B |
| C | D |
| A | A |
| C | D |
| A | B |
+-------+-------+

The result

+-------+-------+
| test1 | test2 |
+-------+-------+
| A | B |
| C | D |
| A | A |
| C | D |
| A | B |
+-------+-------+

after executing the following query

SELECT t1.test1,
t1.test2
FROM testtest t1
WHERE t1.test1 <= t1.test2
OR NOT EXISTS ( SELECT 1 FROM testtest t2 WHERE t2.test1= t1.test2 AND t2.test2 = t1.test1)

result nothing changed.

Anything wrong in my query.

Thanks






Re: Simple Query on one table

Chris Howarth

Well, yes, that is what you asked for... The data in your most recent example doesn't contain any opposing pairs of data so the output will be the same as the input.

Are you after a distinct list If so then simply use SELECT DISTINCT... (then the remainder of the query) to return the A, B and C, D pairs only once.

Chris






Re: Simple Query on one table

VBE_programmer

Sorry ,

I had a mistake in the above example.

actually i want the result as i said in the first example

like ..This table contrains always distinct values.

Sorry about that.I am really confused

col1 col2

---------------------

A B

B A

C D

D C

P Q

Q P

Col1 col2

------------------------

A B

C D

P Q






Re: Simple Query on one table

Chris Howarth

The query I provided earlier returns the data as you specified in your first example....

Chris

Code Snippet

SELECT t1.[col1],

t1.[col2]

FROM @MyTable t1

WHERE t1.[col1] <= t1.[col2]

OR NOT EXISTS ( SELECT 1

FROM @MyTable t2

WHERE t2.[col1] = t1.[col2]

AND t2.[col2] = t1.[col1] )






Re: Simple Query on one table

VBE_programmer

yes , it is working perfectly on sample table.

I will check it out on my main tables