By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,727 Members | 1,342 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,727 IT Pros & Developers. It's quick & easy.

Comparing two columns for differences

maylortaylor
P: 72
This might be hard to explain what I am going for, but i will try.

Ok, I have two tables: (1)"ZIPS" which contains zipcodes,states,and counties for everywhere in the U.S. and (2) "UserCriteria" which contains username, state, county, rank, and a few other criteria.


In the UserCriteria table, I can have one user (mtaylor) representing many states..let's say 10. Each state is assigned a Rank based on how well that user knows that state (typically 1 - 3...3 being 'they do not understand it at all').

So let's say mtaylor's first 8 states have a Rank of 1, and his 9th and 10th state has a Rank of 2....well I want to let the table know that the rest of the 40 states that mtaylor does not have should have a rank of 3 ('meaning he should not do these states')


I am clueless on how to do this.


As of now, I have in the Usercriteria table, in the State column, I have the word "REST" (meaning rest of the states) and the Rank given to "REST" is 3....but how would i go about finding out what the 'rest of the states' are?

My SQL strings are very rough, but i have this so far...

Expand|Select|Wrap|Line Numbers
  1. Function RestOfStates(ByVal _user As String)
  2.         Dim TaxConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ConfigurationManager.AppSettings("Database")
  3.         Dim dbConnection As OleDbConnection = New OleDbConnection(TaxConnStr)
  4.         Try
  5.  
  6.             Dim queryString As String
  7.             queryString = "SELECT DISTINCT State from Zips"
  8.             Dim dbCommand As OleDbCommand = New OleDbCommand
  9.             dbCommand.CommandText = queryString
  10.             dbCommand.Connection = dbConnection
  11.  
  12.  
  13.             Dim queryString2 As String
  14.             queryString2 = "SELECT UserName, State, Rank from UserCriteria WHERE State = 'REST' AND Username = '" & _user & "'"
  15.         Catch ex As Exception
  16.             Console.WriteLine(ex.Message)
  17.             myLogger.Log(ex.Message)
  18.         Finally
  19.             dbConnection.Close()
  20.         End Try
  21.     End Function
...the queryString2 section is unfinished cause i dont know how to compare what i collect in the first queryString to the second queryString....any ideas?
Apr 5 '13 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
First you'll need a cross join query to list every combination of state and distinct user. Then you outer join that back to your user criteria which will show you which ones are missing from the user criteria.
Apr 5 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.