Check if any two columns are equal..help !!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Check if any two columns are equal..help !!!!

Author

Topic

anmfaisal
Starting Member

2 Posts

Posted - 2004-04-16 : 16:54:54

I have seven columns in a table and I want to check if any two has equal values...
How do I do that without writing one long
Select * from table
where col1 = col2
or col1 = c0l3
or col1 = col4
....you get the idea
Please email me at anm_faisal@yahoo.com

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-04-16 : 17:26:29

If you're doing this once write it out,
if you doing this alot write a sql statement that uses the sysobjects or information schema views to generate the sql for you.

Of course you can also do it like this but it may impact performance depending on data volume.

select * from
tbl where id =
(
select min(a.id)
from
(
select id, col1 as testcol from tbl union all
select id, col2 as testcol from tbl union all
select id, col3 as testcol from tbl union all
select id, col4 as testcol from tbl union all
select id, col5 as testcol from tbl union all
select id, col6 as testcol from tbl union all
select id, col7 as testcol from tbl
) a
group by a.testcol
having count(*) > 1
)

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-04-16 : 22:09:35

If I understand your question you want to check to see if any 2 columns in a row have an equal value. If this is the case try this.

SELECT *
FROM table
WHERE col1 IN (col2, col3, col4, col5, col6, col7)
OR col2 IN (col3, col4, col5, col6, col7)
OR col3 IN (col4, col5, col6, col7)
OR col4 IN (col5, col6, col7)
OR col5 IN (col6, col7)
OR col6 IN (col7)

Dustin Michaels

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-16 : 22:18:03

nice one, dustin ... and of course, the need for solutions to this problem usually indicates a poor database design .

- Jeff

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-04-16 : 23:20:55

Thanks for the compliment jsmith8858.

And ya this problem seems to indicate a bad DB design.

Why do you need to be able to do this anmfaisal?
Perhaps someone can help you with your table design.

Dustin Michaels

anmfaisal
Starting Member

2 Posts

Posted - 2004-05-07 : 11:23:26

Everybody,
Thank you for all the help. I am tracking commission for a network marketing company.
I have a table where among others, I have two columns;
repID, SponsorRepID. SponsorReID clumn has the repid of the sponsor of newly joined rep.

Now, we report a rep's 7 level UPline.

notation --> = sposorsored by

RepID SponsorRepID
-------------------
E --> D
D --> C
C --> B
B --> A
T --> C

RepID is unique.

If I were to track T's upline tree, it would be

Level 1 = C
Level2 = D
Level3 = E

somebody was doing this by populating a table every morning that consists of RepID, Level1, level2,.... ,level7. 500 reps will have 500 rows; and no single sponsor should show more than once in a row.

I know that if they ask for 20 level report, this will be a mess, and I am changing this table to go vertical soon; repID, sponsorid, level. Any other/better idea?

Now, in the above example, how do I prevent them from changing D's sponsoreRepID to B or A? in other words, A rep(D)
cannot have a sponsor that is already in his(D) downline tree. Downline tree is just the oppsite of upline.
Please let me know if I am not clear.... any help would be greatly appreciated. I can send the whole table (600 rows) in
CSV format if it is hard to understand from my poor english.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources