Solved

Compare column data

Posted on 2007-04-05
2
190 Views
Last Modified: 2010-03-19
Hi Experts,

Here is my table:
Account (AccountId, UserId, Currency)
                 1                   1            USD
                 2                    1           USD
                 3                    2           EUR
                 4                    1           USD
                 5                    2           USD

I need to compare the currencies because 1 user must only have 1 currency.
So, in my  example, User1 currency is USD (no problem) so I can  return the currency of User1.
But User2 has 2 currencies so I need to throw an  error.

Thanks in advance for your help.
                 
0
Comment
Question by:noulouk
2 Comments
 
LVL 5

Assisted Solution

by:Yttribium
Yttribium earned 150 total points
ID: 18856814
Why not have the User Table contain the Currency, and join it to this Account table?

Like
TABLE: [User]
ID
Name
Currency

TABLE [Account]
AccountID
UserID


VIEW AccountCurrency
SELECT     Account.AccountID, Account.UserID, User.Currency
FROM         Account INNER JOIN
                      User ON Account.UserID = User.UserID
0
 
LVL 6

Accepted Solution

by:
chiragkhabaria earned 350 total points
ID: 18856940
Something like this

PS Not i have added some records in the table for more testing...

The query will return all the user name whoese has more then one currency in the Currency column...



-- Create the Table Varaible for checking..
Declare @Account Table
(
      AccountId int,
      UserId int,
      Currency varchar(10)
)

-- Insert the Values in the TAble VAraible for testing.
Insert @Account
 Select 1,1, 'USD' Union All
Select 2, 1,'USD' Union All
Select 3, 2,'EUR' Union All
Select 4, 1,'USD' Union All
Select 6, 2,'USD' Union All
Select 7, 1,'EUR' Union All
Select 8, 3,'USD' Union All
Select 9, 3,'USD' Union All
Select 10, 4 ,'USD' Union All
Select 11, 4,'EUR'


-- Now this is the query which will extract the duplicate records

Select Distinct UserID  From @Account a
Where UserID In
(
      Select  UserID From @Account b where a.UserID = b.UserID And a.Currency <> b.Currency
)

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question