Selecting from 2 tables...

[Live2BidDotCom]

Hi all - I have 2 tables that I am needing to get data out of. The first contains all widgets and the other contains only widgets that have had orders. I need to find all widgets that have not been ordered by John Doe. The common id between the 2 tables is the widget id.

Sounds simple, but I am having difficulty with the 2nd table since there can be multiple orders and sometimes John Doe is one of the orders and sometimes not.

Thanks

 

[CamaroLT]

I'm not sure exactly where you're going with this, but to make a very basic order tracking database, here is what I would do:

{not tested code}

create table Customers (CustID integer primary key autonumber, CustName varchar(255));

create table Items (ItemID integer primary key autonumber, ItemName varchar(2550));

create table Orders (OrderID integer primary key autonumber, PostDate datetime, fkCustID integer);

create table OrderItems (fkOrderID integer, fkItemID Integer, NumberOrdered integer);

You have a table with all your widgets.
You have a table with all your customers.
You have a table with orders made by your customers. The customer owns that order.
You have a table with all the widgets for each order. The orders table owns OrderItems.

So if you want to find all the orders that don't belong to Mr Doe, (Assume Mr Doe has CustID=1)

select OrderID from Orders where fkCustID<>1;

Bottom line, the structure you have on your machine is going to need to know how to relate the particular order belongs to Mr Doe.


-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job