Show line results even if field is blank or null

J

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
 
J

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.
Click to expand...

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]));
Click to expand...

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

John W. Vinson[MVP]
 
J

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

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
Click to expand...

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

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
 
Top