473,695 Members | 2,577 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Sep 15 '05 #1
1 4213
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" <bu********@yah oo.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.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.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

Sep 15 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
10051
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def, serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur where def.TYPEDETABLEDECODES = 4
7
31554
by: Steve | last post by:
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.")
3
19475
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2 have the same type of operator, without using the OUTER JOIN syntax ?
14
5708
by: mike | last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly. There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key of Item, and is also a field in LogEvent. Some ItemIDs in LogEvent do not correspond to ItemIDs in Item, and periodically we need to purge the non-matching ItemIDs from LogEvent. The query is: delete from LogEvent where EventType != 'i' and ItemID in
0
1558
by: SKB | last post by:
Hi, I want to implement the "outer join" functionality in Java. Can somebody explain the pseudo code for the same. OR what needs to be done to extend the hash-join Java code of equijoin(I have the hash join code for equijoin with me). Regds
1
1658
by: new2php | last post by:
Hi,I'm new to PHP...I currently doing a PHP project....I don't understand this code..can someone please kindly explain to me..Thanks select * from category c left join category2 c2 on c2.cat1_id=c.cat_id where c.cat_id=" . $_GET . " and c2.cat2_id=" . $_GET What does the c & c2 do? & what is the output result? Here the database table: CREATE TABLE `category` (
1
3275
by: nico3334 | last post by:
I have a query that currently pulls data from a main table and a second table using LEFT OUTER JOIN. I know how to do make another LEFT OUTER JOIN with the main table, but I want to add another LEFT OUTER JOIN to the second table. So I want the third table to be joined through the second table, not the main table. Here is my original code that joins the main table and the second table SELECT t1.supply, t2.inventory, FROM MAIN_TABLE...
14
2888
by: kpfunf | last post by:
I have a select query that uses an "OR" join. With that join in place, I cannot edit the data in the query datasheet; if I delete the "OR" and only use one join, then I can edit the data. I read this article that explains I can't edit because of the SQL specific statement. Is there a way around this so that I don't have to open this query, look for what I want to change, then go back and open the table to edit? Thanks.
2
1501
BeemerBiker
by: BeemerBiker | last post by:
I thought the following would work, but I thought wrong: SELECT DISTINCT AvailableCourses.* FROM AvailableCourses INNER JOIN PlannedCourses ON PlannedCourses.CourseID<>AvailableCourses.CourseID WHERE PlannedCourses.EmplID=?; I want to display courses that are available for an employee to take, but I do not want to display courses that are already planned. If I change <> to "=" then i get the courses that are planned. I want the ones...
0
9112
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8975
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8826
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8818
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5832
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4338
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2996
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2261
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.