I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per event or category of event per record in the output CSV.
The database structure requires that I use nested select statements to get the necessary data into columns for each record--each column containing a quantity of tickets for that type of event. I know how to do this in theory, but each embedded select statement is so large that the full report would blow through the 8,000 character limit on my DB interface. One of these statements looks like this:
(select count (*) FROM guest ig, event2 e, eventseat es, "order" o WHERE ig.guestid = g.guestid and ig.guestid = o.guestid and o.orderid = es.orderid and e.eventid = es.eventid and e.incometype = 'T' and e.eventtype in ('SS', 'BMF') and es.status in ('2','4') and es.price <> '0.00' and es.price <> '5.00' and e.year = '2010' <-- THESE LINES ARE THE ONLY ONES THAT and e.run in ('SS-DANCE') <-- CHANGE PER SELECT STATEMENT ) as 'SS-Dance',
That's about 400 characters, which only leaves enough room for <20 iterations in a single query without going over the character limit.
Is there a way to reduce this or generalize it so that I don't need to waste all those characters on duplicated information in each SELECT?
I've put together an example at the following link which contains a simplified version of my DB structure and some sample data. Objective is to get the same result data without using so many characters per nested SELECT.
INNER JOINsyntax is going to be much easier for most of your audience to read and parse, and much less prone for you to inadvertently derive the Cartesian product. Also can you explain where