Posted by John Bell on 09/15/05 11:50

Hi

This should be exactly the same as your but I prefer not to specify the
INNER JOINS in the where clause.

SELECT E.entry_amount,
T.credit_card_exp_date,
E.entry_datetime,
E.employee_id,
E.voucher_explanation,
E.card_reader_used_ind,
S.room_id,
G.Guest_lastname,
G.guest_firstname,
S.arrival_time,
S.departure_time,
S.arrival_date,
S.original_departure_date,
S.no_show_status,
S.cancellation_date,
F.house_acct_id,
F.group_code,
T.original_receipt_id
FROM dbo.LEDGER_ENTRY E
JOIN dbo.LEDGER_TRANSACTION T ON E.trans_id = T.trans_id
JOIN dbo.FOLIO F ON F.folio_id = T.folio_id
LEFT JOIN dbo.STAY_FOLIO O ON F.folio_id = O.folio_id
LEFT JOIN dbo.STAY S ON O.stay_id = S.stay_id
LEFT JOIN dbo.GUEST G ON F.guest_id = G.guest_id
WHERE E.payment_method='3737******6100'
AND E.property_id='abc123'
ORDER BY E.entry_datetime DESC

Make sure that your have indexes in place that can be used and they are not
fragmented, also check that the statistics are up-to-date. To do this check
out the Query Execution plan. You may want to try running the query in Query
Analyser and using the Index Tuning Wizard to see if there are any
suggestions you may want to implement. See topics on DBCC SHOWCONTIG, DBCC
DBREINDEX, UPDATE STATISTICS and the section on Optimizing Database
Performance in Books Online.

John

"Steve" <budgethelp@yahoo.com> wrote in message
news:1126754238.811500.118130@o13g2000cwo.googlegroups.com...
>I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
> "Left Outer Joins" in order to return every transaction for a specific
> set of criteria.
>
> Using three "Left Outer Joins" slows the system down considerably.
>
> I've tried creating a temp db, but I can't figure out how to execute
> two select commands. (It throws the exception "The column prefix
> 'tempdb' does not match with a table name or alias name used in the
> query.")
>
> Looking for suggestions (and a lesson or two!) This is my first attempt
> at SQL.
>
> Current (working, albeit slowly) Query Below
>
> TIA
>
> SELECT
>
> LEDGER_ENTRY.entry_amount,
> LEDGER_TRANSACTION.credit_card_exp_date,
> LEDGER_ENTRY.entry_datetime,
> LEDGER_ENTRY.employee_id,
> LEDGER_ENTRY.voucher_explanation,
> LEDGER_ENTRY.card_reader_used_ind,
> STAY.room_id,
> GUEST.guest_lastname,
> GUEST.guest_firstname,
> STAY.arrival_time,
> STAY.departure_time,
> STAY.arrival_date,
> STAY.original_departure_date,
> STAY.no_show_status,
> STAY.cancellation_date,
> FOLIO.house_acct_id,
> FOLIO.group_code,
> LEDGER_TRANSACTION.original_receipt_id
>
> FROM
>
> mydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,
> mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,
>
> mydb.dbo.FOLIO FOLIO
> LEFT OUTER JOIN
> mydb.dbo.STAY_FOLIO STAY_FOLIO
> ON
> FOLIO.folio_id = STAY_FOLIO.folio_id
> LEFT OUTER JOIN
> mydb.dbo.STAY STAY
> ON
> STAY_FOLIO.stay_id = STAY.stay_id
> LEFT OUTER JOIN
> mydb.dbo.GUEST GUEST
> ON
> FOLIO.guest_id = GUEST.guest_id
>
> WHERE
>
> LEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_id
> AND FOLIO.folio_id = LEDGER_TRANSACTION.folio_id
> AND LEDGER_ENTRY.payment_method='3737******6100'
> AND LEDGER_ENTRY.property_id='abc123'
>
> ORDER BY
>
> LEDGER_ENTRY.entry_datetime DESC
>

[Back to original message]