NEWBIE - Join in SQL Query result is omitting records

Greetings,

I have two tables with the following information (simplified):

a.. InvoiceDetail [PA23203]
a.. Invoice Line Item Number
b.. Line Item amount
c.. Line Item Bill Note Index (joined to [PA01601])
b.. BillNotes [PA01601]
a.. Bill Note Index (joined to [PA23203])
b.. Billing Note


I would like to display a grid with the information in invoice detail and
the billing note in the same grid. If I do a standard join on the tables,
the query works and all, but it omits all rows that do not have a billing
note. The result of the query is only showing the invoice detail line items
with an attached note.



I'd appreciate any help you would be willing and able to give.



Below is the SQL statement if you are interested (sorry it's messy and
long):



SELECT [PA23203].[PACostDate], [PA23203].[PACOSTCATID],
[PA23203].[PACostOwner], [PA23203].[PAApproved_Quantity],
[PA23203].[PAUnitCost], [PA23203].[PATotCst], [PA23203].[PAAPPROVMKUPPCT],
[PA23203].[PAApproved_Billing_Amou], [PA01601].[PATX500] FROM [PA23203],
[PA01601] WHERE ([PA23203].[PABillNoteIDX] = [PA01601].[PABillNoteIDX]) AND
[PA23203].[PADocnumber20] = '" &
datagrid2.items.item(datagrid2.selecteditem.itemindex).Cells(0).Text & "'
ORDER BY [PA23203].[PACOSTDATE], [PA23203].[PACOSTOWNER]



Thank you,



-Dave

RockNRoll
Fri Aug 20 14:33:53 CDT 2004

I solved my own problem - I had to use a "LEFT OUTER JOIN"
"RockNRoll" <daveweber@yahoo.com> wrote in message
news:ujiSIfuhEHA.3928@TK2MSFTNGP11.phx.gbl...
> Greetings,
>
> I have two tables with the following information (simplified):
>
> a.. InvoiceDetail [PA23203]
> a.. Invoice Line Item Number
> b.. Line Item amount
> c.. Line Item Bill Note Index (joined to [PA01601])
> b.. BillNotes [PA01601]
> a.. Bill Note Index (joined to [PA23203])
> b.. Billing Note
>
>
> I would like to display a grid with the information in invoice detail and
> the billing note in the same grid. If I do a standard join on the tables,
> the query works and all, but it omits all rows that do not have a billing
> note. The result of the query is only showing the invoice detail line
items
> with an attached note.
>
>
>
> I'd appreciate any help you would be willing and able to give.
>
>
>
> Below is the SQL statement if you are interested (sorry it's messy and
> long):
>
>
>
> SELECT [PA23203].[PACostDate], [PA23203].[PACOSTCATID],
> [PA23203].[PACostOwner], [PA23203].[PAApproved_Quantity],
> [PA23203].[PAUnitCost], [PA23203].[PATotCst], [PA23203].[PAAPPROVMKUPPCT],
> [PA23203].[PAApproved_Billing_Amou], [PA01601].[PATX500] FROM [PA23203],
> [PA01601] WHERE ([PA23203].[PABillNoteIDX] = [PA01601].[PABillNoteIDX])
AND
> [PA23203].[PADocnumber20] = '" &
> datagrid2.items.item(datagrid2.selecteditem.itemindex).Cells(0).Text & "'
> ORDER BY [PA23203].[PACOSTDATE], [PA23203].[PACOSTOWNER]
>
>
>
> Thank you,
>
>
>
> -Dave
>
>

William
Sat Aug 21 13:32:21 CDT 2004

Instead of using a Join, I'd seriously consider using a dataRelation - that
way you'll be able to use the DataAdapters to update your table as well and
it's probably going to be more efficient.
http://www.knowdotnet.com/articles/datarelation.html

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
"RockNRoll" <daveweber@yahoo.com> wrote in message
news:OH0yjyuhEHA.1972@TK2MSFTNGP09.phx.gbl...
> I solved my own problem - I had to use a "LEFT OUTER JOIN"
> "RockNRoll" <daveweber@yahoo.com> wrote in message
> news:ujiSIfuhEHA.3928@TK2MSFTNGP11.phx.gbl...
> > Greetings,
> >
> > I have two tables with the following information (simplified):
> >
> > a.. InvoiceDetail [PA23203]
> > a.. Invoice Line Item Number
> > b.. Line Item amount
> > c.. Line Item Bill Note Index (joined to [PA01601])
> > b.. BillNotes [PA01601]
> > a.. Bill Note Index (joined to [PA23203])
> > b.. Billing Note
> >
> >
> > I would like to display a grid with the information in invoice detail
and
> > the billing note in the same grid. If I do a standard join on the
tables,
> > the query works and all, but it omits all rows that do not have a
billing
> > note. The result of the query is only showing the invoice detail line
> items
> > with an attached note.
> >
> >
> >
> > I'd appreciate any help you would be willing and able to give.
> >
> >
> >
> > Below is the SQL statement if you are interested (sorry it's messy and
> > long):
> >
> >
> >
> > SELECT [PA23203].[PACostDate], [PA23203].[PACOSTCATID],
> > [PA23203].[PACostOwner], [PA23203].[PAApproved_Quantity],
> > [PA23203].[PAUnitCost], [PA23203].[PATotCst],
[PA23203].[PAAPPROVMKUPPCT],
> > [PA23203].[PAApproved_Billing_Amou], [PA01601].[PATX500] FROM [PA23203],
> > [PA01601] WHERE ([PA23203].[PABillNoteIDX] = [PA01601].[PABillNoteIDX])
> AND
> > [PA23203].[PADocnumber20] = '" &
> > datagrid2.items.item(datagrid2.selecteditem.itemindex).Cells(0).Text &
"'
> > ORDER BY [PA23203].[PACOSTDATE], [PA23203].[PACOSTOWNER]
> >
> >
> >
> > Thank you,
> >
> >
> >
> > -Dave
> >
> >
>
>

RockNRoll
Tue Aug 24 11:30:07 CDT 2004

William,

Thank you for the suggestion, but my application is a read-only information
portal.

"William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
news:uXVxU16hEHA.3944@tk2msftngp13.phx.gbl...
> Instead of using a Join, I'd seriously consider using a dataRelation -
that
> way you'll be able to use the DataAdapters to update your table as well
and
> it's probably going to be more efficient.
> http://www.knowdotnet.com/articles/datarelation.html
>
> --
> W.G. Ryan MVP Windows - Embedded
>
> Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
> Let Microsoft know!
> https://www.windowsembeddedeval.com/community/newsgroups
> "RockNRoll" <daveweber@yahoo.com> wrote in message
> news:OH0yjyuhEHA.1972@TK2MSFTNGP09.phx.gbl...
> > I solved my own problem - I had to use a "LEFT OUTER JOIN"
> > "RockNRoll" <daveweber@yahoo.com> wrote in message
> > news:ujiSIfuhEHA.3928@TK2MSFTNGP11.phx.gbl...
> > > Greetings,
> > >
> > > I have two tables with the following information (simplified):
> > >
> > > a.. InvoiceDetail [PA23203]
> > > a.. Invoice Line Item Number
> > > b.. Line Item amount
> > > c.. Line Item Bill Note Index (joined to [PA01601])
> > > b.. BillNotes [PA01601]
> > > a.. Bill Note Index (joined to [PA23203])
> > > b.. Billing Note
> > >
> > >
> > > I would like to display a grid with the information in invoice detail
> and
> > > the billing note in the same grid. If I do a standard join on the
> tables,
> > > the query works and all, but it omits all rows that do not have a
> billing
> > > note. The result of the query is only showing the invoice detail line
> > items
> > > with an attached note.
> > >
> > >
> > >
> > > I'd appreciate any help you would be willing and able to give.
> > >
> > >
> > >
> > > Below is the SQL statement if you are interested (sorry it's messy and
> > > long):
> > >
> > >
> > >
> > > SELECT [PA23203].[PACostDate], [PA23203].[PACOSTCATID],
> > > [PA23203].[PACostOwner], [PA23203].[PAApproved_Quantity],
> > > [PA23203].[PAUnitCost], [PA23203].[PATotCst],
> [PA23203].[PAAPPROVMKUPPCT],
> > > [PA23203].[PAApproved_Billing_Amou], [PA01601].[PATX500] FROM
[PA23203],
> > > [PA01601] WHERE ([PA23203].[PABillNoteIDX] =
[PA01601].[PABillNoteIDX])
> > AND
> > > [PA23203].[PADocnumber20] = '" &
> > > datagrid2.items.item(datagrid2.selecteditem.itemindex).Cells(0).Text &
> "'
> > > ORDER BY [PA23203].[PACOSTDATE], [PA23203].[PACOSTOWNER]
> > >
> > >
> > >
> > > Thank you,
> > >
> > >
> > >
> > > -Dave
> > >
> > >
> >
> >
>
>