Select based on logic table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select based on logic table

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_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

52326 Posts

Posted - 2013-01-03 : 00:49:50

you want comparison be done for each users or just between two tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 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

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 MVP
http://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 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

52326 Posts

Posted - 2013-01-03 : 01:18:16

[code]
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
[/code]

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 - 2013-01-03 : 01:26:20

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

52326 Posts

Posted - 2013-01-03 : 01:44:26

[code]
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)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 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

52326 Posts

Posted - 2013-01-03 : 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 - 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

Posted - 2013-01-03 : 03:43:18

it would be a bit slow. if performance is a concern you need to use string parsing logic using a UDF like

scenario 4

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

the string parsing udf will be like below

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources