SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select based on logic table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pearlyred
Starting Member

7 Posts

Posted - 01/03/2013 :  00:16:49  Show Profile  Reply with Quote
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
52317 Posts

Posted - 01/03/2013 :  00:49:50  Show Profile  Reply with Quote
you want comparison be done for each users or just between two tables?

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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/03/2013 :  00:53:39  Show Profile  Reply with Quote
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
Go to Top of Page

pearlyred
Starting Member

7 Posts

Posted - 01/03/2013 :  01:14:20  Show Profile  Reply with Quote
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/



Go to Top of Page

pearlyred
Starting Member

7 Posts

Posted - 01/03/2013 :  01:18:10  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/03/2013 :  01:18:16  Show Profile  Reply with Quote

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/

Go to Top of Page

pearlyred
Starting Member

7 Posts

Posted - 01/03/2013 :  01:26:20  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/03/2013 :  01:44:26  Show Profile  Reply with Quote

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/

Go to Top of Page

pearlyred
Starting Member

7 Posts

Posted - 01/03/2013 :  01:49:03  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/03/2013 :  01:56:27  Show Profile  Reply with Quote
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/

Go to Top of Page

pearlyred
Starting Member

7 Posts

Posted - 01/03/2013 :  03:17:02  Show Profile  Reply with Quote
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..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/03/2013 :  03:43:18  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000