Need Help with "Left Outer Join"...

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Steve
    #1

    Need Help with "Left Outer Join"...

    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.en try_amount,
    LEDGER_TRANSACT ION.credit_card _exp_date,
    LEDGER_ENTRY.en try_datetime,
    LEDGER_ENTRY.em ployee_id,
    LEDGER_ENTRY.vo ucher_explanati on,
    LEDGER_ENTRY.ca rd_reader_used_ ind,
    STAY.room_id,
    GUEST.guest_las tname,
    GUEST.guest_fir stname,
    STAY.arrival_ti me,
    STAY.departure_ time,
    STAY.arrival_da te,
    STAY.original_d eparture_date,
    STAY.no_show_st atus,
    STAY.cancellati on_date,
    FOLIO.house_acc t_id,
    FOLIO.group_cod e,
    LEDGER_TRANSACT ION.original_re ceipt_id

    FROM

    mydb.dbo.LEDGER _ENTRY LEDGER_ENTRY,
    mydb.dbo.LEDGER _TRANSACTION LEDGER_TRANSACT ION,

    mydb.dbo.FOLIO FOLIO
    LEFT OUTER JOIN
    mydb.dbo.STAY_F OLIO STAY_FOLIO
    ON
    FOLIO.folio_id = STAY_FOLIO.foli o_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.tr ans_id = LEDGER_TRANSACT ION.trans_id
    AND FOLIO.folio_id = LEDGER_TRANSACT ION.folio_id
    AND LEDGER_ENTRY.pa yment_method='3 737******6100'
    AND LEDGER_ENTRY.pr operty_id='abc1 23'

    ORDER BY

    LEDGER_ENTRY.en try_datetime DESC

    Tags: None
    • John Bell
      #2
      Re: Need Help with "Left Outer Join"...

      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_e xp_date,
      E.entry_datetim e,
      E.employee_id,
      E.voucher_expla nation,
      E.card_reader_u sed_ind,
      S.room_id,
      G.Guest_lastnam e,
      G.guest_firstna me,
      S.arrival_time,
      S.departure_tim e,
      S.arrival_date,
      S.original_depa rture_date,
      S.no_show_statu s,
      S.cancellation_ date,
      F.house_acct_id ,
      F.group_code,
      T.original_rece ipt_id
      FROM dbo.LEDGER_ENTR Y E
      JOIN dbo.LEDGER_TRAN SACTION 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_metho d='3737******61 00'
      AND E.property_id=' abc123'
      ORDER BY E.entry_datetim e 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@yah oo.com> wrote in message
      news:1126754238 .811500.118130@ o13g2000cwo.goo glegroups.com.. .[color=blue]
      >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.en try_amount,
      > LEDGER_TRANSACT ION.credit_card _exp_date,
      > LEDGER_ENTRY.en try_datetime,
      > LEDGER_ENTRY.em ployee_id,
      > LEDGER_ENTRY.vo ucher_explanati on,
      > LEDGER_ENTRY.ca rd_reader_used_ ind,
      > STAY.room_id,
      > GUEST.guest_las tname,
      > GUEST.guest_fir stname,
      > STAY.arrival_ti me,
      > STAY.departure_ time,
      > STAY.arrival_da te,
      > STAY.original_d eparture_date,
      > STAY.no_show_st atus,
      > STAY.cancellati on_date,
      > FOLIO.house_acc t_id,
      > FOLIO.group_cod e,
      > LEDGER_TRANSACT ION.original_re ceipt_id
      >
      > FROM
      >
      > mydb.dbo.LEDGER _ENTRY LEDGER_ENTRY,
      > mydb.dbo.LEDGER _TRANSACTION LEDGER_TRANSACT ION,
      >
      > mydb.dbo.FOLIO FOLIO
      > LEFT OUTER JOIN
      > mydb.dbo.STAY_F OLIO STAY_FOLIO
      > ON
      > FOLIO.folio_id = STAY_FOLIO.foli o_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.tr ans_id = LEDGER_TRANSACT ION.trans_id
      > AND FOLIO.folio_id = LEDGER_TRANSACT ION.folio_id
      > AND LEDGER_ENTRY.pa yment_method='3 737******6100'
      > AND LEDGER_ENTRY.pr operty_id='abc1 23'
      >
      > ORDER BY
      >
      > LEDGER_ENTRY.en try_datetime DESC
      >[/color]


        Comment

        Previous template Next
        Working...