Basic Query Question

[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!

[Herbert Chan]

Re: Basic Query Question

What you need is probably a LEFT JOIN:

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

Herbert

"MX1" <mx1@mx1.abc> ¦b¶l¥ó news:X80Qb.1261 36$I06.1010570@ attbi_s01 ¤¤¼¶¼g...[color=blue]
> 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[/color]
rate,[color=blue]
> etc. The tOrderDetail table is linked to tOrder by a field called[/color]
DetailID.[color=blue]
> 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[/color]
though[color=blue]
> I can either capture records with data in tOrderDetail or records with[/color]
out -[color=blue]
> not both. Any thoughts would be greatly appreciated.
>
> Thanks!
>
>[/color]

[MX1]

Re: Basic Query Question

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" <herbert@chan.c om> wrote in message
news:4010942c$1 _1@rain.i-cable.com...[color=blue]
> What you need is probably a LEFT JOIN:
>
> Select <field lists> FROM tOrder LEFT JOIN tOrderDetail ON
> tOrder.DetailID =tOrderDetail.D etailID
>
> Herbert
>
> "MX1" <mx1@mx1.abc> ¦b¶l¥ó news:X80Qb.1261 36$I06.1010570@ attbi_s01[/color]
¤¤¼¶¼g...[color=blue][color=green]
> > Here's a simple query question. I have tables. One is an order table[/color][/color]
and[color=blue][color=green]
> > one is an order detail table.
> >
> > tOrder
> > tOrderDetail
> >
> > The tOrder table contains basic info like customer name, date, a flat[/color]
> rate,[color=green]
> > etc. The tOrderDetail table is linked to tOrder by a field called[/color]
> DetailID.[color=green]
> > Some orders in the tOrderDetail table actually have related data in the
> > tOrderDetail table. I'm trying to write a simple query that will[/color][/color]
capture[color=blue][color=green]
> > all of the data from both tables - can't seem to do it. It seems as[/color]
> though[color=green]
> > I can either capture records with data in tOrderDetail or records with[/color]
> out -[color=green]
> > not both. Any thoughts would be greatly appreciated.
> >
> > Thanks!
> >
> >[/color]
>
>[/color]

[Herbert Chan]

Re: Basic Query Question

Select <field1>, <field2>, sum(<field3>)
FROM tOrder LEFT JOIN tOrderDetail ON tOrder.DetailID =tOrderDetail.D etailID
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" <mx1@mx1.abc> ¦b¶l¥ó news:rK0Qb.1297 29$na.160212@at tbi_s04 ¤¤¼¶¼g...[color=blue]
> 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[/color]
5[color=blue]
> 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" <herbert@chan.c om> wrote in message
> news:4010942c$1 _1@rain.i-cable.com...[color=green]
> > What you need is probably a LEFT JOIN:
> >
> > Select <field lists> FROM tOrder LEFT JOIN tOrderDetail ON
> > tOrder.DetailID =tOrderDetail.D etailID
> >
> > Herbert
> >
> > "MX1" <mx1@mx1.abc> ¦b¶l¥ó news:X80Qb.1261 36$I06.1010570@ attbi_s01[/color]
> ¤¤¼¶¼g...[color=green][color=darkred]
> > > Here's a simple query question. I have tables. One is an order table[/color][/color]
> and[color=green][color=darkred]
> > > one is an order detail table.
> > >
> > > tOrder
> > > tOrderDetail
> > >
> > > The tOrder table contains basic info like customer name, date, a flat[/color]
> > rate,[color=darkred]
> > > etc. The tOrderDetail table is linked to tOrder by a field called[/color]
> > DetailID.[color=darkred]
> > > Some orders in the tOrderDetail table actually have related data in[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > tOrderDetail table. I'm trying to write a simple query that will[/color][/color]
> capture[color=green][color=darkred]
> > > all of the data from both tables - can't seem to do it. It seems as[/color]
> > though[color=darkred]
> > > I can either capture records with data in tOrderDetail or records with[/color]
> > out -[color=darkred]
> > > not both. Any thoughts would be greatly appreciated.
> > >
> > > Thanks!
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]