Show line results even if field is blank or null

[j.t.w]

Hi All,

I am trying to make a query that will display line comments for each
line item on an order.

The following SQL statement works if any of the line items has a
comment in the dbo_OELINCMT_SQL table. If there are no comments for any
of the line items on the order, no records appear.

This is what I have so far:

SELECT dbo_OEORDHDR_SQL.*, dbo_OEORDLIN_SQL.*, dbo_OELINCMT_SQL.cmt
FROM (dbo_OEORDLIN_SQL INNER JOIN dbo_OEORDHDR_SQL ON
dbo_OEORDLIN_SQL.ord_no = dbo_OEORDHDR_SQL.ord_no) INNER JOIN
dbo_OELINCMT_SQL ON (dbo_OEORDLIN_SQL.line_seq_no =
dbo_OELINCMT_SQL.line_seq_no) AND (dbo_OEORDLIN_SQL.ord_no =
dbo_OELINCMT_SQL.ord_no)
WHERE (((dbo_OEORDHDR_SQL.ord_no)=[Order Number]));

dbo_OEORDHDR_SQL table (pretty standard):
ord_type pk
ord_no pk
cus_no
bill_to_name
inv_no
etc.

dbo_OEORDLIN_SQL table (pretty standard):
ord_type pk
ord_no pk
line_seq_no pk
item_no
item_desc
qty_to_ship
uom
etc.

dbo_OELINCMT_SQL table:
ord_type pk
ord_no pk
line_seq_no pk
lvl_no pk
cmt_type pk
cmt_seq_no pk
cmt
etc.

I am trying to retrieve a list of all line items, whether or not they
have a comment associated with a line item.

If someone could help me out with this, I would greatly appreciate it.

Thanks,
j.t.w

 

[John Vinson]

Hi All,

I am trying to make a query that will display line comments for each
line item on an order.

The following SQL statement works if any of the line items has a
comment in the dbo_OELINCMT_SQL table. If there are no comments for any
of the line items on the order, no records appear.

Change the INNER JOIN on the OELINCMT table to a LEFT JOIN:

This is what I have so far:

SELECT dbo_OEORDHDR_SQL.*, dbo_OEORDLIN_SQL.*, dbo_OELINCMT_SQL.cmt
FROM (dbo_OEORDLIN_SQL INNER JOIN dbo_OEORDHDR_SQL ON
dbo_OEORDLIN_SQL.ord_no = dbo_OEORDHDR_SQL.ord_no) LEFT JOIN
dbo_OELINCMT_SQL ON (dbo_OEORDLIN_SQL.line_seq_no =
dbo_OELINCMT_SQL.line_seq_no) AND (dbo_OEORDLIN_SQL.ord_no =
dbo_OELINCMT_SQL.ord_no)
WHERE (((dbo_OEORDHDR_SQL.ord_no)=[Order Number]));

The LEFT JOIN will show all records in OEORDLIN and any matching
records in OELINCMT.

John W. Vinson[MVP]

 

[JohnFol]

Change the INNER JOIN to a LEFT JOIN

 

[j.t.w]

Thank you for your help. That was just what I wanted.

If you don't mind me asking, I notice that when I run this query and
there are no comments, "#Deleted" shows up in the cmt field. Why does
this happen? Just wondering.

Thanks again,
j.t.w

 

[j.t.w]

Thank you.

j.t.w

 

[John Vinson]

Thank you for your help. That was just what I wanted.

If you don't mind me asking, I notice that when I run this query and
there are no comments, "#Deleted" shows up in the cmt field. Why does
this happen? Just wondering.

Thanks again,
j.t.w

It shouldn't, not with Access tables! Are these tables in SQL/Server
or some other database system? Have the records in fact been deleted
(in some systems, a deleted record isn't actually removed but just
flagged for deletion)?

John W. Vinson[MVP]

 

[j.t.w]

Yes, the tables are stored in SQL Server 2000 tables.

If the cmt field is already blank, then "#Delete" shows up in the
query. Nothing actually gets deleted (as far as I'm aware). If there is
a comment in the cmt field, the data shows up correctly in the query.

On the report, the data displays correctly either way.

Thanks for your help.
j.t.w