What would be your approach? (Applying filters from another table)

Question

What would be your approach? (Applying filters from another table)

Sean Grebey-262535

SSCommitted

Points: 1724
More actions
October 6, 2005 at 9:37 am

#91410

I have a set of True/False filters stored as a row in one table that I am trying to apply in a HAVING statement in a SELECT Statement on another table. What would be the best way to link that data as there isn't really a field I can JOIN them on per say so much as I need that one row in the Filter table to apply to all rows in my SELECT statement on the other table if that makes sense?
It seems like there hsould be a straight forward way to do this but I'll be damned if I know how.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Please post DDL for the two tables. Otherwise, you will likely receive a generic select that may or may not make sense in your specific situation.

Wayne

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

And please post sample data too along with the required results.

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 3

Here is the generic solution I mentioned earlier. Hope this helps.

create table #FlagTable (Flag1 int, Flag2 int, flag3 int)

INSERT INTO #FlagTable (Flag1, Flag2, Flag3) VALUES (0,1,1)

create table #DataTable (LastName VARCHAR(25), FirstName VARCHAR(25), Flag1 INT, FLAG2 INT, FLAG3 INT)

INSERT INTO #DataTable (LastName, FirstName, Flag1, Flag2, Flag3)

SELECT 'Washington', 'George', 1,0,1 UNION

SELECT 'James', 'Jesse', 0,1,1 UNION

SELECT 'Clinton', 'Bill', 1,1,1 UNION

SELECT 'Clinton', 'Hillary', 0,0,0

SELECT DT.*

FROM #DataTable DT INNER JOIN #FlagTable FT

ON DT.Flag1 = FT.Flag1

AND DT.Flag2 = FT.Flag2

AND DT.Flag3 = FT.Flag3

DROP TABLE #FlagTable

DROP TABLE #DataTable

Wayne

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 4

Hmmm....that is kind of an issue....I am more looking for general cooncepts than specifics. I'm trying to convert somebody's old Access into SQL and the stuff is an utter mess. I don't really have a table definiton as the "table" I am SELECTING from is actually nested views joined with tables. Below is the SELECT Statement in Access so you have an idea of what I am trying to do. Basically that monsterous Having statement had a whole lot of iif statements with VBA Functions imbedded in them. I want to get rid of the embedded VBA Functions and pull that data from another table. It's a mess...

SELECT DISTINCTROW qryDLEmpList.Emp_HireDate, qryDLEmpList.Emp_Dist, qryDLEmpList.Emp_Dept, qryDLEmpList.Emp_ID, qryDLEmpList.FullName, qryDLEmpList.Emp_ClassName, qryDLEmpList.Emp_Phone1, qryDLEmpList.Emp_Phone2, Max(qryDLCallOutsCentral.CallDate) AS MaxOfCallDate

FROM qryDLEmpList INNER JOIN qryDLCallOutsCentral ON qryDLEmpList.Emp_ID = qryDLCallOutsCentral.Emp_ID

GROUP BY qryDLEmpList.Emp_HireDate, qryDLEmpList.Emp_Dist, qryDLEmpList.Emp_Dept, qryDLEmpList.Emp_ID, qryDLEmpList.FullName, qryDLEmpList.Emp_ClassName, qryDLEmpList.Emp_Phone1, qryDLEmpList.Emp_Phone2

HAVING ((((qryDLEmpList.Emp_Dist) Like (IIf(getDLCentralDNoF()=True,"*","")) Or (qryDLEmpList.Emp_Dist)=(IIf(getCentralStores()=True,"","CEN")) Or (qryDLEmpList.Emp_Dist)=(IIf(getDLCentralDENG()=True,"ENG","")) Or (qryDLEmpList.Emp_Dist)=(IIf(getDLCentralDFS()=True,"FS","")) Or (qryDLEmpList.Emp_Dist)=(IIf(getDLCentralDOPS()=True,"OPS","")) Or (qryDLEmpList.Emp_Dist)=(IIf(getDLCentralDPH()=True,"PH","")) Or (qryDLEmpList.Emp_Dist) Like (IIf(getDLCentralDRET()=True,"RE*","")) Or (qryDLEmpList.Emp_Dist)=(IIf(getDLCentralDSE()=True,"SE","")) Or (qryDLEmpList.Emp_Dist)=(IIf(getDLCentralDWH()=True,"WH",""))) And (qryDLEmpList.Emp_Dist)<>"NOR" And (qryDLEmpList.Emp_Dist)<>"EST") AND ((qryDLEmpList.Emp_ClassName) Like (IIf(getDLCentralCNoF()=True,"*","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC1()=True,"1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC2()=True,"2","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC3()=True,"3","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCLH()=True,"LH","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCF1()=True,"F1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCF2()=True,"F2","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCL1()=True,"L1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCMT1()=True,"MT1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCNA()=True,"NA","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCRE()=True,"RE","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCS1()=True,"S1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCST()=True,"ST","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCSU()=True,"SU","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCSV()=True,"SV","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCT1()=True,"T1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCT2()=True,"T2","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCW1()=True,"W1",""))) AND ((Max(qryDLCallOutsCentral.CallDate))>=Date()-getDLCentralAge() Or (Max(qryDLCallOutsCentral.CallDate)) Is Null)) OR (((qryDLEmpList.Emp_Dept)=IIf(getCentralStores()=True,"WH","")) AND ((qryDLEmpList.Emp_ClassName) Like (IIf(getDLCentralCNoF()=True,"*","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC1()=True,"1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC2()=True,"2","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC3()=True,"3","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCLH()=True,"LH","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCF1()=True,"F1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCF2()=True,"F2","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCL1()=True,"L1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCMT1()=True,"MT1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCNA()=True,"NA","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCRE()=True,"RE","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCS1()=True,"S1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCST()=True,"ST","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCSU()=True,"SU","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCSV()=True,"SV","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCT1()=True,"T1","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCT2()=True,"T2","")) Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCW1()=True,"W1",""))) AND ((Max(qryDLCallOutsCentral.CallDate))>=Date()-getDLCentralAge() Or (Max(qryDLCallOutsCentral.CallDate)) Is Null))

ORDER BY Max(qryDLCallOutsCentral.CallDate) DESC;

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 5

Here is the access query reformatted to spread things out a bit...

SELECT DISTINCTROW

qryDLEmpList.Emp_HireDate,

qryDLEmpList.Emp_Dist,

qryDLEmpList.Emp_Dept,

qryDLEmpList.Emp_ID,

qryDLEmpList.FullName,

qryDLEmpList.Emp_ClassName,

qryDLEmpList.Emp_Phone1,

qryDLEmpList.Emp_Phone2,

Max(qryDLCallOutsCentral.CallDate) AS MaxOfCallDate

FROM qryDLEmpList

INNER JOIN qryDLCallOutsCentral

ON qryDLEmpList.Emp_ID = qryDLCallOutsCentral.Emp_ID

GROUP BY

qryDLEmpList.Emp_HireDate,

qryDLEmpList.Emp_Dist,

qryDLEmpList.Emp_Dept,

qryDLEmpList.Emp_ID,

qryDLEmpList.FullName,

qryDLEmpList.Emp_ClassName,

qryDLEmpList.Emp_Phone1,

qryDLEmpList.Emp_Phone2

HAVING (( ( (qryDLEmpList.Emp_Dist) Like (IIf(getDLCentralDNoF()=True,"*","" ))

Or (qryDLEmpList.Emp_Dist)= (IIf(getCentralStores()=True,"","CEN" ))

Or (qryDLEmpList.Emp_Dist)= (IIf(getDLCentralDENG()=True,"ENG","" ))

Or (qryDLEmpList.Emp_Dist)= (IIf(getDLCentralDFS()=True,"FS","" ))

Or (qryDLEmpList.Emp_Dist)= (IIf(getDLCentralDOPS()=True,"OPS","" ))

Or (qryDLEmpList.Emp_Dist)= (IIf(getDLCentralDPH()=True,"PH","" ))

Or (qryDLEmpList.Emp_Dist) Like (IIf(getDLCentralDRET()=True,"RE*","" ))

Or (qryDLEmpList.Emp_Dist)= (IIf(getDLCentralDSE()=True,"SE","" ))

Or (qryDLEmpList.Emp_Dist)= (IIf(getDLCentralDWH()=True,"WH","" ))

)

And (qryDLEmpList.Emp_Dist)<>"NOR"

And (qryDLEmpList.Emp_Dist)<>"EST"

)

AND ((qryDLEmpList.Emp_ClassName) Like (IIf(getDLCentralCNoF()=True,"*",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC1()=True,"1",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC2()=True,"2",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralC3()=True,"3",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCLH()=True,"LH",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCF1()=True,"F1",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCF2()=True,"F2",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCL1()=True,"L1",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCMT1()=True,"MT1",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCNA()=True,"NA",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCRE()=True,"RE",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCS1()=True,"S1",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCST()=True,"ST",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCSU()=True,"SU",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCSV()=True,"SV",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCT1()=True,"T1",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCT2()=True,"T2",""))

Or (qryDLEmpList.Emp_ClassName)=(IIf(getDLCentralCW1()=True,"W1","")))

AND ((Max(qryDLCallOutsCentral.CallDate))>=Date()-getDLCentralAge()

Or (Max(qryDLCallOutsCentral.CallDate)) Is Null))

OR (((qryDLEmpList.Emp_Dept)=IIf(getCentralStores()=True,"WH",""))