Author |
Topic |
pearlyred
Starting Member
7 Posts |
Posted - 2013-01-03 : 00:16:49
|
Hi All,I'm trying to put together a query and am very stuck, hopefully someone can help out and enlighten me.I want to select all records from TableA that match all of the criteria in a separate logic table (tableB).The tables are like this:tableA(cost unit, userID, officeID)tableB(cost unit, excl cost unit, userID, excl userID, officeID, excl officeID).The columns in tableB can contain more than one piece of data, so I want to select where the values may match, but the 'excl' tables are NOT's.ie. where tableA.cost_unit = tableB.cost_unit AND tableA.cost_unit != tableB.excl_cost_unitDoes that make sense? I'm not sure if this is the best way to do it, so if a change to the table structure is required i'd be happy to look at that aswell.Thanks~P |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-03 : 00:49:50
|
you want comparison be done for each users or just between two tables?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-03 : 00:53:39
|
May be this?SELECT t2.*FROM tableA t1 JOIN tableB t2ON t1.[cost unit] = t2.[cost unit] AND t1.userID = t2.userID AND t1.officeID = t2.officeIDWHERE t1.[cost unit] != t2.[excl cost unit] AND t1.userID != t2.[excl userID] AND t1.officeID != t2.[excl officeID]--Chandu |
|
|
pearlyred
Starting Member
7 Posts |
Posted - 2013-01-03 : 01:14:20
|
Just the tables.Basically tableA will contain several thousand rows, I want to filter it based on set criteria in the logic table (will be using a select list to change the filter).The logic table also has to be adaptable, so new logic's can be added.quote: Originally posted by visakh16 you want comparison be done for each users or just between two tables?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
pearlyred
Starting Member
7 Posts |
Posted - 2013-01-03 : 01:18:10
|
Thanks for this suggestion.It's the rows from tableA I want to retrieve, can I simply change the first line to SELECT t1.* ?Also, as the columns may have more than 1 value in them, is it ok to swap all = and != to IN or something similar?thanks a lotquote: Originally posted by bandi May be this?SELECT t2.*FROM tableA t1 JOIN tableB t2ON t1.[cost unit] = t2.[cost unit] AND t1.userID = t2.userID AND t1.officeID = t2.officeIDWHERE t1.[cost unit] != t2.[excl cost unit] AND t1.userID != t2.[excl userID] AND t1.officeID != t2.[excl officeID]--Chandu
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-03 : 01:18:16
|
[code]select a.*from tableA ajoin tableB bOn b.cost_unit = a.cost_unitand b.userID = a.user_IDand b.officeID = a.officeID[/code]would return you data as per criteria set in tableBdidt understand significance of excl columns though as automatically they'll get filteredAre the columns in tableB nullable?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
pearlyred
Starting Member
7 Posts |
Posted - 2013-01-03 : 01:26:20
|
Thanks Visakh.The columns are nullable. I will try and explain them a little better.example: I want to retrieve all rows with userID 'user10', but excluding any that have cost_unit '10'.so tableB will have a row, like this:| Null | 10 | user10 | Null | 12,13,15 | Null |quote: Originally posted by visakh16
select a.*from tableA ajoin tableB bOn b.cost_unit = a.cost_unitand b.userID = a.user_IDand b.officeID = a.officeID would return you data as per criteria set in tableBdidt understand significance of excl columns though as automatically they'll get filteredAre the columns in tableB nullable?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-03 : 01:44:26
|
[code]select a.*from tableA ajoin tableB bOn ((b.cost_unit = a.cost_unit) or b.cost_unit is null)and ((b.userID = a.user_ID) or b.user_id is null)and ((b.officeID = a.officeID) or b.officeID is null)where (a.cost_unit != b.excl_cost_unit or b.excl_cost_unit is null)and (a.userid != b.excl_userId or b.excl_userid is null)and (a.officeid != b.excl_officeid or b.excl_officeid is null)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
pearlyred
Starting Member
7 Posts |
Posted - 2013-01-03 : 01:49:03
|
Thanks! that's really helpful.As above, if the columns contain more than one value (comma delimited), can I use IN /NOT IN or something instead of = / != ?quote: Originally posted by visakh16
select a.*from tableA ajoin tableB bOn ((b.cost_unit = a.cost_unit) or b.cost_unit is null)and ((b.userID = a.user_ID) or b.user_id is null)and ((b.officeID = a.officeID) or b.officeID is null)where (a.cost_unit != b.excl_cost_unit or b.excl_cost_unit is null)and (a.userid != b.excl_userId or b.excl_userid is null)and (a.officeid != b.excl_officeid or b.excl_officeid is null) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-03 : 01:56:27
|
nope. if columns contain more than one value you need to use like thisselect a.*from tableA ajoin tableB bOn ((',' + cast(b.cost_unit as varchar(1000)) + ',' LIKE '%,' + CAST(a.cost_unit AS varchar(5)) + ',%') or b.cost_unit is null)and ((',' + cast(b.userID as varchar(1000)) + ',' LIKE '%,' + CAST(a.user_ID AS varchar(5)) + ',%') or b.user_id is null)and ((',' + cast(b.officeID as varchar(1000)) + ',' LIKE '%,' + CAST(a.officeID AS varchar(5)) + ',%') or b.officeID is null)where (a.cost_unit != b.excl_cost_unit or b.excl_cost_unit is null)and (a.userid != b.excl_userId or b.excl_userid is null)and (a.officeid != b.excl_officeid or b.excl_officeid is null) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
pearlyred
Starting Member
7 Posts |
Posted - 2013-01-03 : 03:17:02
|
Given tableA could be up to 50-60 thousand rows, would this be very slow and inefficient?I realise having multiple values in the same column is probably bad practice, but I can't see how to do it another way.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|