Creating a one-to-one relationship (tblOrder, tblInvoice)

[ozonic]

A have a created a SQL database and two of the tables are tblOrder and tblIinvoice. In the database diagram I have specified a one-to-one relationship between them.

In my application I have a form to create Orders and a form to create Invoices. When creating an Invoice the user has to choose the Order for which he wants to create the the Invoice, so I display all the Order_ID's in a combobox (Order_ID = Foreign Key in tblInvoice & Primary Key in tblOrder).

But since it is a one-to-one relationship the combobox should display only those Order_ID's that HAVEN'T BEEN Invoiced. So in other words the Order_ID's for the Orders that HAVE BEEN Invoiced should not be displayed in the Combobox. How can I possibly achieve this???

I thought of using a filter statement for the tblOrderBindingSource.Filter on the Invoice form to filter the Order_ID's that will be displayed on the combobox, but I have no idea what should write for the filter statement. Please help.

 

[cjard]

Erm.. Did you consider that tables that are related 1:1 should actually be the same table? A WHERE clause would thus be the necessary restriction

 

[ozonic]

I'm sorry but maybe I didn't state my question properly.

In my opinion I think you're wrong about the 1:1 relationship being the same table. I think that you're actually thinking of a Unary relationship (a relatiionship between records of the same table), which is not the same as a 1:1 relationship.

But you were right about the WHERE clause, it worked, thank you.

 

[cjard]

I wasnt intending to talk about unary relationships.. I was observing that if we have a table X such that each row in X is matched to at least and at most 1 row in table Y, then there is no clear argument for two tables to exist and house the information; the data should be stored in one table:

Compare

tblPersonNames
----------------
id, name
1, "fred"

tblPersonAges
--------------
id,age
1, 33


vs


tblPerson
---------
id,name,age
1,"fred",33


That's what I meant

 

[ozonic]

Oh, I see now what you mean, you're right, it is better to use a single table. Thank you for your help, it's much appreciated.