By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 663 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Basic Query Question

P: n/a
MX1
Here's a simple query question. I have tables. One is an order table and
one is an order detail table.

tOrder
tOrderDetail

The tOrder table contains basic info like customer name, date, a flat rate,
etc. The tOrderDetail table is linked to tOrder by a field called DetailID.
Some orders in the tOrderDetail table actually have related data in the
tOrderDetail table. I'm trying to write a simple query that will capture
all of the data from both tables - can't seem to do it. It seems as though
I can either capture records with data in tOrderDetail or records with out -
not both. Any thoughts would be greatly appreciated.

Thanks!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
What you need is probably a LEFT JOIN:

Select <field lists> FROM tOrder LEFT JOIN tOrderDetail ON
tOrder.DetailID=tOrderDetail.DetailID

Herbert

"MX1" <mx*@mx1.abc> 在郵件 news:X80Qb.126136$I06.1010570@attbi_s01 中撰寫...
Here's a simple query question. I have tables. One is an order table and
one is an order detail table.

tOrder
tOrderDetail

The tOrder table contains basic info like customer name, date, a flat rate, etc. The tOrderDetail table is linked to tOrder by a field called DetailID. Some orders in the tOrderDetail table actually have related data in the
tOrderDetail table. I'm trying to write a simple query that will capture
all of the data from both tables - can't seem to do it. It seems as though I can either capture records with data in tOrderDetail or records with out - not both. Any thoughts would be greatly appreciated.

Thanks!

Nov 12 '05 #2

P: n/a
MX1
Thanks Herbert. I was able to get it going with the wizard. However, I'm
getting multiple detail line items. For example, on order might have 4 or 5
detail lines in th detail table. I'd like to subtotal those amounts as a
single line item. I'm going to battle the wizard for this. Any thoughts?

"Herbert Chan" <he*****@chan.com> wrote in message
news:40**********@rain.i-cable.com...
What you need is probably a LEFT JOIN:

Select <field lists> FROM tOrder LEFT JOIN tOrderDetail ON
tOrder.DetailID=tOrderDetail.DetailID

Herbert

"MX1" <mx*@mx1.abc> 在郵件 news:X80Qb.126136$I06.1010570@attbi_s01

中撰寫...
Here's a simple query question. I have tables. One is an order table and one is an order detail table.

tOrder
tOrderDetail

The tOrder table contains basic info like customer name, date, a flat

rate,
etc. The tOrderDetail table is linked to tOrder by a field called

DetailID.
Some orders in the tOrderDetail table actually have related data in the
tOrderDetail table. I'm trying to write a simple query that will capture all of the data from both tables - can't seem to do it. It seems as

though
I can either capture records with data in tOrderDetail or records with

out -
not both. Any thoughts would be greatly appreciated.

Thanks!


Nov 12 '05 #3

P: n/a
Select <field1>, <field2>, sum(<field3>)
FROM tOrder LEFT JOIN tOrderDetail ON tOrder.DetailID=tOrderDetail.DetailID
GROUP BY tOrder.DetailID

Or learn something about GROUP BY either from the help or from the web.

Or actually you'd better pick up a book about SQL. I once, which was
actually just last September, picked up a "Learn SQL in 10 minutes"
published by Sams and thereafter could feel rather comfortable with SQL.
Before reading this book, I knew totally nothing about SQL. After reading
it, I found out that SQL was actually rather simple, although it certainly
took a long time to master. But at the same time, you don't need to master
it before you can get any good use out of it:)

"MX1" <mx*@mx1.abc> 在郵件 news:rK0Qb.129729$na.160212@attbi_s04 中撰寫...
Thanks Herbert. I was able to get it going with the wizard. However, I'm
getting multiple detail line items. For example, on order might have 4 or 5 detail lines in th detail table. I'd like to subtotal those amounts as a
single line item. I'm going to battle the wizard for this. Any thoughts?

"Herbert Chan" <he*****@chan.com> wrote in message
news:40**********@rain.i-cable.com...
What you need is probably a LEFT JOIN:

Select <field lists> FROM tOrder LEFT JOIN tOrderDetail ON
tOrder.DetailID=tOrderDetail.DetailID

Herbert

"MX1" <mx*@mx1.abc> 在郵件 news:X80Qb.126136$I06.1010570@attbi_s01

中撰寫...
Here's a simple query question. I have tables. One is an order table and one is an order detail table.

tOrder
tOrderDetail

The tOrder table contains basic info like customer name, date, a flat

rate,
etc. The tOrderDetail table is linked to tOrder by a field called

DetailID.
Some orders in the tOrderDetail table actually have related data in the tOrderDetail table. I'm trying to write a simple query that will capture all of the data from both tables - can't seem to do it. It seems as

though
I can either capture records with data in tOrderDetail or records with

out -
not both. Any thoughts would be greatly appreciated.

Thanks!



Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.