| Author |
 Topic  |
|
|
pearlyred
Starting Member
7 Posts |
 Posted - 01/03/2013 : 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_unit
Does 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
India
47023 Posts |
Posted - 01/03/2013 : 00:49:50
|
you want comparison be done for each users or just between two tables?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 01/03/2013 : 00:53:39
|
May be this?
SELECT t2.*
FROM tableA t1 JOIN tableB t2
ON t1.[cost unit] = t2.[cost unit] AND t1.userID = t2.userID AND t1.officeID = t2.officeID
WHERE t1.[cost unit] != t2.[excl cost unit] AND t1.userID != t2.[excl userID] AND t1.officeID != t2.[excl officeID]
--
Chandu |
Edited by - bandi on 01/03/2013 00:54:09 |
|
|
|
pearlyred
Starting Member
7 Posts |
Posted - 01/03/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
pearlyred
Starting Member
7 Posts |
Posted - 01/03/2013 : 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 lot
quote:
Originally posted by bandi
May be this?
SELECT t2.*
FROM tableA t1 JOIN tableB t2
ON t1.[cost unit] = t2.[cost unit] AND t1.userID = t2.userID AND t1.officeID = t2.officeID
WHERE 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
India
47023 Posts |
Posted - 01/03/2013 : 01:18:16
|
select a.*
from tableA a
join tableB b
On b.cost_unit = a.cost_unit
and b.userID = a.user_ID
and b.officeID = a.officeID
would return you data as per criteria set in tableB
didt understand significance of excl columns though as automatically they'll get filtered
Are the columns in tableB nullable?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
pearlyred
Starting Member
7 Posts |
Posted - 01/03/2013 : 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 a
join tableB b
On b.cost_unit = a.cost_unit
and b.userID = a.user_ID
and b.officeID = a.officeID
would return you data as per criteria set in tableB
didt understand significance of excl columns though as automatically they'll get filtered
Are the columns in tableB nullable?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/03/2013 : 01:44:26
|
select a.*
from tableA a
join tableB b
On ((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 MVP
http://visakhm.blogspot.com/
|
|
|
|
pearlyred
Starting Member
7 Posts |
Posted - 01/03/2013 : 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 a
join tableB b
On ((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 MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/03/2013 : 01:56:27
|
nope. if columns contain more than one value you need to use like this
select a.*
from tableA a
join tableB b
On ((',' + 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
pearlyred
Starting Member
7 Posts |
Posted - 01/03/2013 : 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
India
47023 Posts |
|
| |
Topic |
|
Select based on logic table
