To query all recs of a table including non-linked foreign keys?

[jetspin]

Hi. I've searched without a definitive answer.

How do I query all recs of a table regardless
of whether a FK is empty,blank,null? I've been
trying 'like "*", "or' Like *".

My main table to query is called 'tblorders' and
it has a foreign key from a 'tblphonenumbers' table.

I want to query all 'tblorders' regardless whether
there is a foreign key ordPnrID.

tblOrders Table Name
ordOrdID PK
ordPnrID FK to tblPhoneNumbers.prnPnrID PK
ordDate Data


tblPhoneNumbers Table Name
pnrPnrID PK
pnrNumber Data

THANKS

 

[KenReay]

Hi

If you want all rows returned just do not set any criteria, so

SELECT * FROM MyTable;



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Ah, maybe your question is not quite what I thought!

Make a query joing in the two tables on the Foreign Key

pull down the columns you want to see

Right click on the line forming the join

choose from the dialog, show all rows in tblOrders (ie you are making a Left (or right) Outer Join

is that what you want?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jetspin]

Hi.

Thanks for the quick reply... I was not specific enough. I want to be able to list all orders inluding those with links to phone numbers and those with no links. The following list only those with links.

SELECT tblOrders.ordOrdID,
tblOrders.ordPnrID,
tblOrders.ordDate,
tblPhoneNumbers.pnrPnrID, tblPhoneNumbers.pnrPhoneNumber
FROM tblPhoneNumbers INNER JOIN tblOrders ON tblPhoneNumbers.pnrPnrID = tblOrders.ordPnrID;

I tried what you said about clicking on the relationship line where a pop-up appears about 'relationship properties' and selected option (3) to select all recs from tblorders and only rec from tblphonenumbers where the joined fields are equal and another popup appears asking about 'left' and 'right' tables? Is this what you mean? I am not familiar with this yet. I want to put this query on a form for editing orders to select phone numbers, but because I am unable to do this, I am currently using a combobox query to get the phone numbers and load my form fields.... I also have an edit button to enter/edit new phone number by opening a another form. After closing this other form, I was left to either find out how to 'refresh' my unbound' phone text fields or perhaps try to incorporate the phone data into the form's query which led to this post. Hope that is not compicating the issue. Just wanted to show what I was trying to do.
Thanks.

 

[KenReay]

Hi

Yes that is what I mean

you should get three choices (not necessarily in this order)

1 show all records where there is a match on FK

2 show all records from Order table and only those from tblPhoneNumber where the FK matches

3 show all records from tblPhoneNumber and only those from tblOrder where the FK matches

you want option 2 in the list above



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jetspin]

Hi.

I tried it again and must of clicked wrong. I was able to select option 3 and it built the correct query...

SELECT tblOrders.ordOrdID, tblOrders.ordPnrID, tblOrders.ordDate, tblPhoneNumbers.pnrPnrID, tblPhoneNumbers.pnrPhoneNumber
FROM tblPhoneNumbers RIGHT JOIN tblOrders ON tblPhoneNumbers.pnrPnrID = tblOrders.ordPnrID;


I noticed it is now a 'RIGHT' JOIN'. I will try this form my form's query and see how it works. Thanks.