1 to many with accepted =yes. If no I get no records

[aunixguru]

I have a one to many relationship setup between quote and quote-detail tables.

In the details table i have accepted (yes/no) field.

Sometimes there are records that do not have any accepted line items.

I wrote a query that pulls everything ok as long as there is an item that has accepted = yes in the line items table.

If there were none I cannot get just the main table record.

Here's the sql. Hope it helps.

SELECT Orders.Ordernum, Orders.Quotenum, Orders.Customernum, Orders.QuoteDesc, Orders.Manufacturer, Orders.Cost, Projects.Projectname, Projects.ProjectDescription, Customers.CustomerName, Orders.Markup, Orders.CustomerCost, [Orders-Add].OrderLineNum, [Orders-Add].[add-Description], [Orders-Add].[add-Cost], [Orders-Add].[add-markup], [Orders-Add].[add-Accepted], [Orders-Add].[add-Total], Orders.Accepted, Orders.Description, Orders.Total, Orders.[Add-DeductTotal], Orders.Projectnum, Orders.Estimatenum, Orders.ManEstimateNum, Orders.Scope, Orders.Exclusions, Orders.From, Orders.Pages, Orders.RequestedShipDate, Orders.ManufacturerOrderNum, Orders.Createdon, Orders.ShiptoLine1, Orders.Shiptoline2, Orders.Shiptoline3, Orders.[EFCO Shop Drawings], Orders.[Customer Shop Drawings], Orders.

, Orders.[Make Order Size], Orders.TotalnoMarkup, Orders.Commtotal, [Orders-Add].[Add-comm], Orders.Comments
FROM ((Orders INNER JOIN Projects ON Orders.Projectnum = Projects.Projectnum) INNER JOIN Customers ON Orders.Customernum = Customers.Customernum) INNER JOIN [Orders-Add] ON Orders.Ordernum = [Orders-Add].Ordernum
WHERE ((([Orders-Add].[add-Accepted])=Yes));

How can I write this to get records even if there are no detail records accepted?


Thanks.

 

[tlbroadbent]

You need to use a LEFT JOIN rather than an INNER JOIN when JOINING the [Orders-Add] table.

FROM (Orders
INNER JOIN Projects
ON Orders.Projectnum = Projects.Projectnum
INNER JOIN Customers
ON Orders.Customernum = Customers.Customernum)
LEFT JOIN [Orders-Add]
ON Orders.Ordernum = [Orders-Add].Ordernum
WHERE [Orders-Add].[add-Accepted]=Yes
OR [Orders-Add].[add-Accepted] Is Null;

You may need to modify the last part of the WHERE clause - [Orders-Add].[add-Accepted] Is Null - changing [add-Accepted] to another column on the [Orders-Add] table. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[aunixguru]

I understand the left join. Changed that.

Problem is that accepted is a yes/no field.

It will never be null.

Not sure what you mean by this:

"You may need to modify the last part of the WHERE clause - [Orders-Add].[add-Accepted] Is Null - changing [add-Accepted] to another column on the [Orders-Add] table. "

Can you clarify?

 

[tlbroadbent]

The last comment in my post related to the fact that [add-Accepted] appeared to be a Yes/No column and would not be NULL. Use another column instead of [add-Accepted] for the NULL test.

For example:
WHERE [Orders-Add].[add-Accepted]=Yes
OR [Orders-Add].[Ordernum] Is Null;
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin