Querying Multiple Tables

[tavie]

I have writers block.....I have 3 tables. I need to query table1 for the current record which will always be flag=1
I then need to query the same table for acct where flag=0 and type is not equal to type on acct where flag=1. I then need to take the auditnumber value of the second query and query table2 to see if the date the type changed is equal to current date or equal to current date -1 (Previous date). if condition is true then I need to query table3 for the typedesc value and insert that value and the Acct into another table. I have tried joins and subqueries, but I cannot return the info I need. I do not want to use a cursor or Temp table. Please help, I' just spinning my wheels.................


Table1
Acct Type Flag AuditNumber
12345 c 1 987
12345 b 0 986
12345 a 0 985

Table2
AuditNumber AuditDate
987 5/14/03
986 5/13/03
985 5/12/03

Table3
Type TypeDesc
a Current
b Pending
c Closed

 

[SQLSister]

Could you show us what data you want returned from the query that would help me visualize what you want Just the results which would trigger an update is fine.

 

[tavie]

SQLSister...I want the TypeDesc and Acct field values returned. Basically if I query table1 and return the current acct and type values where flag = 1, I then query the table again for the same acct where the flag = 0 and the type value is different (current type value is c but previous type value is b). If they are different then I query table2 using the auditnum value and find a date matching current day or current day -1. If there are no matches then I'm done. If there are matches then I insert the Acct field values and typedesc values from table3. I will use the type value in table1 to return the typedesc value from table3. This SP will run daily to fill a rreporting table so I only need to know if type value has changed for current or previous day. More than one day is not required....I am expecting this to return multiple rows. This will basically be run against tables with 30-50 million records........

 

[TheBugSlayer]

Temporary tables seem to be your best bet. The tax on performance is much less than if you used embedded queries and joins.

Why don't you try both options and pick one based on performance?

I see a minor problem if Q1 (or any of the subqueries for that matter) returns more than one record.

 

[tavie]

I would agree, but I have been instructed not to use temp tables since it will severly hinder the performance of the server. The DB currently has 90 Million records and there would be considerable I/O if temp tables were used...

 

[SQLSister]

Let's try to get the select part of the statement right fisrt, then we can work on putting it into an insert.

Once we get the selct selecting the correct records, then we can work on whether there is a way to improve performance and then we can go to the insert statement.
First see if this will get you the records you want to use in the insert:

SELECT Type, Desc FROM Table1
JOIN Table3
ON Table1.Type = Table3.Type
WHERE AuditNumber IN
(SELECT AuditNumber FROM Table1
JOIN Table2
ON Table1.AuditNumber = Table2.AuditNumber
WHERE Max (flag) = 1 and Count(Flag) >1
and (AuditDate = GetDate() or AuditDate = GetDate()-1)

If it doesn't, please tell me what is wrong with the information returned, what is missing or what is added that shouldn't be there. I'm still not sure I completely understand the problem so I may have misinterpreted what you need.

 

[tavie]

I'm getting this when running the query

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ')'.

thanks for you help............

 

[SQLSister]

SELECT Type, Desc FROM Table1
JOIN Table3
ON Table1.Type = Table3.Type
WHERE AuditNumber IN
(SELECT AuditNumber FROM Table1
JOIN Table2
ON Table1.AuditNumber = Table2.AuditNumber
WHERE Max (flag) = 1 and Count(Flag) >1
and (AuditDate = GetDate() or AuditDate = GetDate()-1))

Oops I missed a final Parenthesis. Try that.

 

[tavie]

I caught it two minutes after I posted the reponse...Now it returns:
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name Auditnumber
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name Auditdate
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name Auditdate

 

[tavie]

I discovered that if I remove the field that is used for the join, then the query will run.

SELECT desc FROM Table1
JOIN Table3
ON Table1.Type = Table3.Type

will actually execute without errors. I have since completed this fix on the other select statements but of now I recieve the following error:

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

 

[SQLSister]

try replacing the where auditnumber in with where Exists and instead of saying select auditNumber say SELECT *

 

[tavie]

Recieve the following:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 147, Level 15, State 1, Line 8
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

This is the challege I have been staring at for 8 day's Syntax that should work and does work for some queries just dies when attempting this one I know I could do it with a temp table and cursor, but the SR DBA says No Way do to the amount of records...

 

[SQLSister]

Do not under any circumstances use a cursor on a table with 90 million records! Your dba is right about that.

Let's try:

SELECT TypeDesc, Acct FROM Table1
JOIN Table3
ON Table1.Type = Table3.Type
JOIN
(SELECT Table1.AuditNumber FROM Table1
JOIN Table2
ON Table1.AuditNumber = Table2.AuditNumber
Group BY Table1.AuditNuUmber, AuditDate
HAVING Max (flag) = 1 and Count(Flag) >1
and (AuditDate = GetDate() or AuditDate = GetDate()-1)
) d
ON Table1.AuditNumber = d.AuditNumber

 

[tavie]

OK SQLSister...I made some minor mods and the code seems to work except it is returning the Type value for the previous record and not the current....I have included the actual table and column names in the example.

Declare @current as smalldatetime
DECLARE @previous AS smalldatetime
Select @current = getdate()
SELECT @previous = Getdate() - 1
SELECT test.acct, test.type FROM test
JOIN types
ON test.Type = types.Type
JOIN
(
SELECT test.AuditNum FROM test
JOIN audit
ON test.AuditNum = audit.AuditNum
Group BY test.AuditNum, audit.aDate
HAVING Max (flag) = 1 and Count(Flag) >1
and left(audit.aDate, 11) = left(@current, 11) or left(audit.aDate, 11) = left(@previous, 11)
)d
ON test.AuditNum = d.AuditNum

If you look at my original post, Table1=Test, Table2=Audit and Table3=types. Your query would return

acct Type
12345 b

But I need it to return
Acct Type
12345 c

Since the latter has a status flag that indicates that it is the current record. Your query basically returns the previous record if type is different between previous and current. but as you see I need it to return the current record....Thanks for you help because you have me on the right track....I will keep attempting to modify the code while you assimilate this post.....

 

[SQLSister]

Declare @current as smalldatetime
DECLARE @previous AS smalldatetime
Select @current = getdate()
SELECT @previous = Getdate() - 1
SELECT test.acct, test.type FROM test
JOIN types
ON test.Type = types.Type
JOIN
(
SELECT test.AuditNum FROM test
JOIN audit
ON test.AuditNum = audit.AuditNum
Group BY test.AuditNum, audit.aDate
HAVING Max (flag) = 1 and Count(Flag) >1
and left(audit.aDate, 11) = left(@current, 11) or left(audit.aDate, 11) = left(@previous, 11)
)d
ON test.AuditNum = d.AuditNum
Where Flag = 1

 

[tavie]

I tried that condition...Where flag = 1 and it returns 0 rows when it should return at least 1.....If I set Count(Flag)>0 then it returns the correct current values for all days, not just current or previous days....So if an account type changed Tueday and it's now Thursday, then that account would be inserted into the table when I only want accounts for Wednesday(Previous day) and Thursday(Current Day) inserted. Once I get this routine figured out then I should be home free since the other 49 conditions that can change will use the same syntax......

 

[tavie]

SQLSister...I was wondering if you had anymore thoughts....I am still struggling to get this to work....Thanks.....