NEWBIE - Join in SQL Query result is omitting records

  • Thread starter Thread starter RockNRoll
  • Start date Start date
R

RockNRoll

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
 
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 said:
I solved my own problem - I had to use a "LEFT OUTER JOIN"
RockNRoll said:
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
Click to expand...
Click to expand...
 
William,

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

William Ryan eMVP said:
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 said:
I solved my own problem - I had to use a "LEFT OUTER JOIN"
RockNRoll said:
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] =
Click to expand...
[PA01601].[PABillNoteIDX])
AND
[PA23203].[PADocnumber20] = '" &
datagrid2.items.item(datagrid2.selecteditem.itemindex).Cells(0).Text & "'
ORDER BY [PA23203].[PACOSTDATE], [PA23203].[PACOSTOWNER]



Thank you,



-Dave
Click to expand...
Click to expand...
Click to expand...
 
Back
Top