November 29, 2007 at 3:07 pm
I am trying to create a select statement for my customer search application that has 2 different nested select statements within the select statement. Needless to say this query takes way too long to execute and seems to be slightly inaccurate. What is the best way to handle an issue like this? The query is posted below if someone could please take a look and make any suggestions:
I should also note that if either the first or the second nested query (begining with Accounts.Account_ID IN) is without the other, then the query works beautifully but lacks the customers that fit the criteria the other query fills.
SELECT DISTINCT
Accounts.UID, Accounts.Fname + ' ' + Accounts.MI AS Fname, Accounts.Lname, Accounts.Addr, Accounts.Addr2, Accounts.City, Accounts.State,Accounts.Zip, Accounts.Phone, Accounts.DayPhone, Accounts.Email, Accounts.Company, Accounts.Fax, Accounts.Account_ID AS TXCNum, Accounts.Alt_ID AS TONum
FROM Accounts INNER JOIN
Invoice ON Invoice.UID = Accounts.UID INNER JOIN
Vendors ON Invoice.Vendor_ID = Vendors.Vendor_ID
WHERE (LEN(Accounts.Lname) > 1 OR
LEN(Accounts.Fname) > 1) AND (Invoice.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}') AND (Accounts.Type <> 'P') AND (Accounts.Account_ID IN
(SELECT DISTINCT Accounts.Account_ID
FROM Invoice INNER JOIN
Tickets ON Invoice.Invoice_Num = Tickets.Invoice_Num INNER JOIN
Seats ON Tickets.Seat_ID = Seats.Seat_ID LEFT OUTER JOIN
Accounts ON Invoice.UID = Accounts.UID
WHERE (Invoice.AUTHCODE IS NOT NULL) AND (Tickets.Status = 'S') AND (Tickets.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}')
AND ((Invoice.AUTHCODE IS NOT NULL) OR
(Invoice.Type = 'Receivable')))) OR
(Accounts.Account_ID IN
(SELECT DISTINCT Accounts.Account_ID
FROM Invoice INNER JOIN
InvoiceItems ON Invoice.I_GUID = InvoiceItems.I_GUID INNER JOIN
Seats ON InvoiceItems.SeatPackageClub_ID = Seats.Seat_ID INNER JOIN
Accounts ON Invoice.UID = Accounts.UID
WHERE (InvoiceItems.Type = 'TP') AND (Seats.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}') AND
(Invoice.AUTHCODE IS NOT NULL) OR
(Invoice.Type = 'Receivable')))
ORDER BY Accounts.Lname, Accounts.Fname
Thats for any help you can give and I appologize ahead of time for the layout of my code on this page.
November 29, 2007 at 4:40 pm
jreed,
It's difficult to really optimize the query without test data/tables, but here goes ...
First, I reformatted it a bit for ease of reading (at least for me).
Second, remove the DISTINCT from the subquery(s). There's no reason for the SQL engine to do that extra work of removing duplicates since your only checking for existance.
Third, You can combine the two subqueries into one. Since they both pull from the same tables and just join off of the TICKETS or INVOICE ITEMS move that into the "ON" of the join.
Now PLEASE Note, that without test data, this is a pretty complicated query to optimize.
Try this out and let me know ....
SELECT DISTINCT
Accounts.UID
,Accounts.Fname + ' ' + Accounts.MI AS Fname
,Accounts.Lname
,Accounts.Addr
,Accounts.Addr2
,Accounts.City
,Accounts.State
,Accounts.Zip
,Accounts.Phone
,Accounts.DayPhone
,Accounts.Email
,Accounts.Company
,Accounts.Fax
,Accounts.Account_ID AS TXCNum
,Accounts.Alt_ID AS TONum
FROM
Accounts
INNER JOIN Invoice ON Invoice.UID = Accounts.UID
INNER JOIN Vendors ON Invoice.Vendor_ID = Vendors.Vendor_ID
WHERE
(LEN(Accounts.Lname) > 1 OR LEN(Accounts.Fname) > 1)
AND (Invoice.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}')
AND (Accounts.Type <> 'P')
AND Accounts.Account_ID IN
(SELECT
Accounts.Account_ID
FROM
Invoice
INNER JOIN Accounts ON Invoice.UID = Accounts.UID
LEFT JOIN Tickets ON Invoice.Invoice_Num = Tickets.Invoice_Num
LEFT JOIN InvoiceItems ON Invoice.I_GUID = InvoiceItems.I_GUID
INNER JOIN Seats
ON Tickets.Seat_ID = Seats.Seat_ID
OR InvoiceItems.SeatPackageClub_ID = Seats.Seat_ID
WHERE
(Invoice.AUTHCODE IS NOT NULL) OR (Invoice.Type = 'Receivable')
AND (
(InvoiceItems.Type = 'TP')
AND (Seats.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}')
)
OR
((Invoice.AUTHCODE IS NOT NULL) AND (Tickets.Status = 'S')
AND (Tickets.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}')
)
)
ORDER BY
Accounts.Lname
,Accounts.Fname
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2007 at 8:59 pm
I appologize ahead of time for the layout of my code on this page.
Heh... if you took 5 minutes out ahead of time to fix it, you wouldn't have to appologize ahead of time 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2007 at 4:52 am
Jeff Moden (11/29/2007)
I appologize ahead of time for the layout of my code on this page.
Heh... if you took 5 minutes out ahead of time to fix it, you wouldn't have to appologize ahead of time 😉
jreed, I respectfully suggest you take a few minutes and read this post. Most of us on here will shy away from a post such as yours (without preparation scripts, sample data and expected output). It may seem like a lot of work to prepare the post ahead of time, but you'd be surprised how quickly you'll get responses if you do.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
BTW, let me know if my previous suggestion helps at all. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 30, 2007 at 6:51 am
Jason and Jeff:
Thanks for the etiquette suggestions. I will read that post after I get done sending this one.
Jason:
I tried your query and it returned about 688 users where there should only be 4. It also took 2:33 seconds to execute. I think we are still missing something. When I made this post I was mainly trying to figure out if it was good practice to even have multiple nested selects in a single statement.
I'll see If can get you guys some data to work with.
Thanks for your help thus far.
Kind Regards,
JReed
November 30, 2007 at 7:26 am
Here are a few things to look at to help you out:
- you don't want null Account_ID's (since you're doing an inner join in the outer query). Assuming that's true - your first sub-query should NOT have a LEFT OUTER JOIN in it.
- You encapsulate (Invoice.AUTHCODE IS NOT NULL) OR (Invoice.Type = 'Receivable') between extra paren's in the first, but NOT in the second. That means that in the second, (Invoice.Type = 'Receivable') is all that is required to qualify.
The bigger one might be - perhaps it's a time to use a temp table to get this data together FIRST, and then incorporate them into the query.
Something like:
create table #tempaccts (account_ID int UNIQUE Primary Key clustered )
insert #tempaccts
SELECT Accounts.Account_ID
FROM
Invoice
INNER JOIN InvoiceItems ON Invoice.I_GUID = InvoiceItems.I_GUID
INNER JOIN Seats ON InvoiceItems.SeatPackageClub_ID = Seats.Seat_ID
INNER JOIN Accounts ON Invoice.UID = Accounts.UID
WHERE
(InvoiceItems.Type = 'TP')
AND (Seats.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}')
AND ((Invoice.AUTHCODE IS NOT NULL)
OR(Invoice.Type = 'Receivable'))
UNION
SELECT Accounts.Account_ID
FROM
Invoice
INNER JOIN Tickets ON Invoice.Invoice_Num = Tickets.Invoice_Num
INNER JOIN Seats ON Tickets.Seat_ID = Seats.Seat_ID
INNER JOIN Accounts ON Invoice.UID = Accounts.UID
WHERE
(Invoice.AUTHCODE IS NOT NULL)
AND (Tickets.Status = 'S')
AND (Tickets.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}')
AND ((Invoice.AUTHCODE IS NOT NULL)
OR (Invoice.Type = 'Receivable')
)
Go
Then your query gets a LOT simpler....
SELECT DISTINCT
Accounts.UID,
Accounts.Fname + ' ' + Accounts.MI AS Fname,
Accounts.Lname,
Accounts.Addr,
Accounts.Addr2, Accounts.City, Accounts.State,Accounts.Zip,
Accounts.Phone, Accounts.DayPhone, Accounts.Email,
Accounts.Company, Accounts.Fax, Accounts.Account_ID AS TXCNum,
Accounts.Alt_ID AS TONum
FROM
Accounts
INNER JOIN Invoice ON Invoice.UID = Accounts.UID
INNER JOIN Vendors ON Invoice.Vendor_ID = Vendors.Vendor_ID
Inner join #tempaccts on Accounts.accountd_id=#tempaccts.account_id
WHERE
(LEN(Accounts.Lname) > 1
OR LEN(Accounts.Fname) > 1)
AND (Invoice.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}')
AND (Accounts.Type <> 'P')
ORDER BY Accounts.Lname, Accounts.Fname
drop table #tempaccts
I'd also venture to guess that the two together should take quite a bit less timeto process as well.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 30, 2007 at 7:39 am
Hey Matt,
You are dead on. When running the queries separately it takes much less time. I just ran your queries in Query Analyzer and it returned all 4 Accounts correctly! On one of our other pages we did pretty much what you did in a stored procedure and created a temporary table there also. I did not realize you could do that in inline sql and then simply drop the temp table. That is brilliant!
Thanks a lot!
JReed
November 30, 2007 at 8:11 am
JReed,
I find all the unneeded brackets in code makes it difficult to read. I have reformatted it as follows:
SELECT DISTINCT
    Accounts.UID
    ,Accounts.Fname + ' ' + Accounts.MI AS Fname
    ,Accounts.Lname
    ,Accounts.Addr
    ,Accounts.Addr2
    ,Accounts.City
    ,Accounts.State
    ,Accounts.Zip
    ,Accounts.Phone
    ,Accounts.DayPhone
    ,Accounts.Email
    ,Accounts.Company
    ,Accounts.Fax
    ,Accounts.Account_ID AS TXCNum
    ,Accounts.Alt_ID AS TONum
FROM Accounts
    INNER JOIN Invoice
        ON Invoice.UID = Accounts.UID
    INNER JOIN Vendors
        ON Invoice.Vendor_ID = Vendors.Vendor_ID
WHERE (LEN(Accounts.Lname) > 1 OR LEN(Accounts.Fname) > 1)
    AND Invoice.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}'
    AND Accounts.Type <> 'P'
    AND Accounts.Account_ID IN (
            SELECT DISTINCT Accounts.Account_ID
            FROM Invoice
                INNER JOIN Tickets ON Invoice.Invoice_Num = Tickets.Invoice_Num
                INNER JOIN Seats ON Tickets.Seat_ID = Seats.Seat_ID
                -- Totally fail to see the point of the outer join here
                LEFT OUTER JOIN Accounts ON Invoice.UID = Accounts.UID
            WHERE Invoice.AUTHCODE IS NOT NULL
                AND Tickets.Status = 'S'
                AND Tickets.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
                AND (Invoice.AUTHCODE IS NOT NULL OR Invoice.Type = 'Receivable')
        )
    OR Accounts.Account_ID IN (
            SELECT DISTINCT Accounts.Account_ID
            FROM Invoice
                INNER JOIN InvoiceItems ON Invoice.I_GUID = InvoiceItems.I_GUID
                INNER JOIN Seats ON InvoiceItems.SeatPackageClub_ID = Seats.Seat_ID
                INNER JOIN Accounts ON Invoice.UID = Accounts.UID
            WHERE InvoiceItems.Type = 'TP'
                AND Seats.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
                AND Invoice.AUTHCODE IS NOT NULL
                OR Invoice.Type = 'Receivable'
        )
ORDER BY Accounts.Lname, Accounts.Fname
If this is correct the fundamental speed problem is all the JOINs in the sub-queries. (The optimizer always seems to use nested loops for these which is rarely the best option.) This is also why using #temp tables works quicker. I would be inclined to re-write the lot to use EXISTS subqueries along the lines of:
SELECT
    UID
    ,Fname + ' ' + MI AS Fname
    ,Lname
    ,Addr
    ,Addr2
    ,City
    ,State
    ,Zip
    ,Phone
    ,DayPhone
    ,Email
    ,Company
    ,Fax
    ,Account_ID AS TXCNum
    ,Alt_ID AS TONum
FROM Accounts A
WHERE EXISTS (
        SELECT *
        FROM Invoice I
        WHERE I.UID = A.UID
            AND I.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}'
            AND EXISTS (
                    SELECT *
                    FROM Vendors V
                    WHERE V.Vendor_ID = I.Vendor_ID
                )
    )
    AND (
            (LEN(Lname) > 1 OR LEN(Fname) > 1)
                AND [Type] <> 'P'
                AND EXISTS (
                        SELECT *
                        FROM Invoice I1
                        WHERE I1.UID = A.UID
                            AND (I1.AUTHCODE IS NOT NULL OR I1.[Type] = 'Receivable')
                            AND EXISTS (
                                    SELECT *
                                    FROM Tickets T1
                                    WHERE T1.Invoice_Num = I1.Invoice_Num
                                        AND T1.Status = 'S'
                                        AND T1.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
                                        AND EXISTS (
                                                SELECT *
                                                FROM Seats S1
                                                WHERE S1.Seat_ID = T1.Seat_ID
                                            )
                                )
                    )
                OR EXISTS (
                        SELECT *
                        FROM Invoice I2
                        WHERE I2.UID = A.UID
                            AND EXISTS (
                                    SELECT *
                                    FROM InvoiceItems II2
                                    WHERE II2.I_GUID = I2.I_GUID
                                        AND II2.[Type] = 'TP'
                                        AND EXISTS (
                                                    SELECT *
                                                    FROM Seats S2
                                                    WHERE S2.Seat_ID = II2.Seat_ID
                                                        AND S2.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
                                            )
                                )
                            AND I2.AUTHCODE IS NOT NULL
                            -- Should this be part of the AND?[/color]
                            OR I2.[Type] = 'Receivable'
                    )
        )
ORDER BY Lname, Fname
November 30, 2007 at 8:28 am
Interesting Ken. Thanks for that info as well. I will try your query out and get back to you on that as well.
I am still a beginner in SQL server and just got my first real job as a developer. I really appreciate everyones help on this issue. I never imagined queries could get this complex!
Cheers all!
November 30, 2007 at 10:41 am
jreed (11/30/2007)
Interesting Ken. Thanks for that info as well. I will try your query out and get back to you on that as well.I am still a beginner in SQL server and just got my first real job as a developer. I really appreciate everyones help on this issue. I never imagined queries could get this complex!
Cheers all!
Welcome to our new challenging world - you ain't seen nothing yet!!!!
Let us know if you still need some help with this. Assuming you do make some progress past this current point - post the updated code and hopefully we can improve on it again.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 30, 2007 at 11:35 am
Ken: I just tried your suggestion and after adding the brackets around the bottom in Gray, it worked beautifully. It got all 4 of the accounts in approximately 1 second. I cannot thank you guys enough. Next time I will try and have queries to build the tables and data as well.
Cheers to all!
JReed
SELECT
UID
,Fname + ' ' + MI AS Fname
,Lname
,Addr
,Addr2
,City
,State
,Zip
,Phone
,DayPhone
,Company
,Fax
,Account_ID AS TXCNum
,Alt_ID AS TONum
FROM Accounts A
WHERE EXISTS (
SELECT *
FROM Invoice I
WHERE I.UID = A.UID
AND I.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}'
AND EXISTS (
SELECT *
FROM Vendors V
WHERE V.Vendor_ID = I.Vendor_ID
)
)
AND (
(LEN(Lname) > 1 OR LEN(Fname) > 1)
AND [Type] <> 'P'
AND EXISTS (
SELECT *
FROM Invoice I1
WHERE I1.UID = A.UID
AND (I1.AUTHCODE IS NOT NULL OR I1.[Type] = 'Receivable')
AND EXISTS (
SELECT *
FROM Tickets T1
WHERE T1.Invoice_Num = I1.Invoice_Num
AND T1.Status = 'S'
AND T1.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
AND EXISTS (
SELECT *
FROM Seats S1
WHERE S1.Seat_ID = T1.Seat_ID
)
)
)
OR EXISTS (
SELECT *
FROM Invoice I2
WHERE I2.UID = A.UID
AND EXISTS (
SELECT *
FROM InvoiceItems II2
WHERE II2.I_GUID = I2.I_GUID
AND II2.[Type] = 'TP'
AND EXISTS (
SELECT *
FROM Seats S2
WHERE S2.Seat_ID = II2.SeatPackageClub_ID
AND S2.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
)
)
AND (I2.AUTHCODE IS NOT NULL
OR I2.[Type] = 'Receivable')
)
)
ORDER BY Lname, Fname
November 30, 2007 at 12:15 pm
This might work better if I understood the breakdown correctly, however might have a hair more room for improvement.
SELECT DISTINCT
Accounts.UID
, Accounts.Fname + ' ' + Accounts.MI AS Fname
, Accounts.Lname
, Accounts.Addr
, Accounts.Addr2
, Accounts.City
, Accounts.State
, Accounts.Zip
, Accounts.Phone
, Accounts.DayPhone
, Accounts.Email
, Accounts.Company
, Accounts.Fax
, Accounts.Account_ID AS TXCNum
, Accounts.Alt_ID AS TONum
FROM
dbo.Accounts Accounts
INNER JOIN
dbo.Invoice Invoice
INNER JOIN
dbo.Vendors Vendors
ON
Invoice.Vendor_ID = Vendors.Vendor_ID
LEFT JOIN
dbo.Tickets Tickets
INNER JOIN
sbo.Seats SeatsT
ON
Tickets.Seat_ID = SeatsT.Seat_ID
ON
Invoice.Invoice_Num = Tickets.Invoice_Num
AND Invoice.AUTHCODE IS NOT NULL
AND Tickets.Status = 'S'
AND Tickets.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
LEFT JOIN
dbo.InvoiceItems InvoiceItems
INNER JOIN
dbo.Seats SeatsII
ON
InvoiceItems.SeatPackageClub_ID = SeatsII.Seat_ID
AND SeatsII.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
ON
Invoice.I_GUID = InvoiceItems.I_GUID
AND InvoiceItems.Type = 'TP'
ON
Invoice.UID = Accounts.UID
WHERE
(
LEN(Accounts.Lname) > 1
OR LEN(Accounts.Fname) > 1
)
AND Invoice.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}'
AND Accounts.Type <> 'P'
AND (
Invoice.AUTHCODE IS NOT NULL
OR Invoice.Type = 'Receivable'
)
AND COALESCE(Tickets.Seat_ID, InvoiceItems.SeatPackageClub_ID) IS NOT NULL
ORDER BY
Accounts.Lname
, Accounts.Fname
November 30, 2007 at 12:22 pm
OK this might work a hair better and reduces code a bit
SELECT DISTINCT
Accounts.UID
, Accounts.Fname + ' ' + Accounts.MI AS Fname
, Accounts.Lname
, Accounts.Addr
, Accounts.Addr2
, Accounts.City
, Accounts.State
, Accounts.Zip
, Accounts.Phone
, Accounts.DayPhone
, Accounts.Email
, Accounts.Company
, Accounts.Fax
, Accounts.Account_ID AS TXCNum
, Accounts.Alt_ID AS TONum
FROM
dbo.Accounts Accounts
INNER JOIN
dbo.Invoice Invoice
INNER JOIN
dbo.Vendors Vendors
ON
Invoice.Vendor_ID = Vendors.Vendor_ID
LEFT JOIN
dbo.Tickets Tickets
ON
Invoice.Invoice_Num = Tickets.Invoice_Num
AND Invoice.AUTHCODE IS NOT NULL
AND Tickets.Status = 'S'
AND Tickets.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
LEFT JOIN
dbo.InvoiceItems InvoiceItems
ON
Invoice.I_GUID = InvoiceItems.I_GUID
AND InvoiceItems.Type = 'TP'
INNER JOIN
dbo.Seats Seats
ON
Tickets.Seat_ID = SeatsT.Seat_ID
OR (
InvoiceItems.SeatPackageClub_ID = Seats.Seat_ID
AND Seats.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
)
ON
Invoice.UID = Accounts.UID
WHERE
(
LEN(Accounts.Lname) > 1
OR LEN(Accounts.Fname) > 1
)
AND Invoice.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}'
AND Accounts.Type <> 'P'
AND (
Invoice.AUTHCODE IS NOT NULL
OR Invoice.Type = 'Receivable'
)
ORDER BY
Accounts.Lname
, Accounts.Fname
November 30, 2007 at 12:26 pm
Last one I think not knowing more about your design, but sometimes moving things from the where to the ON for an inner join can improve performance, so here is another iteration of the prior stuff I posted.
SELECT DISTINCT
Accounts.UID
, Accounts.Fname + ' ' + Accounts.MI AS Fname
, Accounts.Lname
, Accounts.Addr
, Accounts.Addr2
, Accounts.City
, Accounts.State
, Accounts.Zip
, Accounts.Phone
, Accounts.DayPhone
, Accounts.Email
, Accounts.Company
, Accounts.Fax
, Accounts.Account_ID AS TXCNum
, Accounts.Alt_ID AS TONum
FROM
dbo.Accounts Accounts
INNER JOIN
dbo.Invoice Invoice
INNER JOIN
dbo.Vendors Vendors
ON
Invoice.Vendor_ID = Vendors.Vendor_ID
AND Invoice.Vendor_ID = '{48BC97F6-A2AC-446D-9E0A-C7D04354B2A5}'
LEFT JOIN
dbo.Tickets Tickets
ON
Invoice.Invoice_Num = Tickets.Invoice_Num
AND Invoice.AUTHCODE IS NOT NULL
AND Tickets.Status = 'S'
AND Tickets.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
LEFT JOIN
dbo.InvoiceItems InvoiceItems
ON
Invoice.I_GUID = InvoiceItems.I_GUID
AND InvoiceItems.Type = 'TP'
INNER JOIN
dbo.Seats Seats
ON
Tickets.Seat_ID = SeatsT.Seat_ID
OR (
InvoiceItems.SeatPackageClub_ID = Seats.Seat_ID
AND Seats.Venue_GUID = '{1A10E4AA-348B-4A89-9562-4CFD83ADFC1B}'
)
ON
Invoice.UID = Accounts.UID
AND (
Invoice.AUTHCODE IS NOT NULL
OR Invoice.Type = 'Receivable'
)
WHERE
(
LEN(Accounts.Lname) > 1
OR LEN(Accounts.Fname) > 1
)
AND Accounts.Type <> 'P'
ORDER BY
Accounts.Lname
, Accounts.Fname
November 30, 2007 at 1:49 pm
wow.. I'm learning more and more.. I'll let you know if that last one work as well. Thanks!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply