Query with related tables question

[jpkeller55]

Is there a way to return a record from a query between two related tables if a record exists in table1 but there are no related records in table2? I have a number of records in table1 that may or may not have a related record in table2 and I would like my query to find all records in table1 with the associated related records but also get records from table1 with no related records in table2.

So in table1 I have a one to many relationship between RecordID and NameID in table2. Note that table2 has no related record for id#3 (Bill)


Table1:

RecordID|Name
1|Jim
2|Dave
3|Bill
4|Rick

Table2

DetailID|NameID|Car
1|1|Buick
2|1|Dodge
3|2|Chevy
4|2|Ford
5|4|Honda
6|4|Toyota

When I run a simple select query

SELECT Table1.RecordID, Table1.Name, Table2.Car
FROM Table1 INNER JOIN Table2 ON Table1.RecordID = Table2.NameID;

I get the following result:

RecordID|Name|Car
1|Jim|Buick
1|Jim|Dodge
2|Dave|Chevy
2|Dave|Ford
4|Rick|Honda
4|Rick|Toyota

Is there any way to get RecordID 3 (Bill) to show in a query even though he has no related records in Table2?

Thanks much.

[ajetrumpet]

What happened with your other situation Jim? Did you get it worked out?

As far as this goes, I have to ask the question, WHY? The purpose of an INNER JOIN like that is to not return the records you're asking for here. Maybe there is another way to do it?

As far as a solution is concerned, try using a LEFT OUTER JOIN. The purpose of that is to return all of the records from a LEFT table. Similarly to the way the relationships builder identifies LEFT and RIGHT tables. Usually on the LEFT side, you have a primary key and on the RIGHT side you have a foreign key.

I don't use these very often, but I believe (don't quote me) that the record will return NULLS in all of the fields that accompany the LEFT side record. So in essence, you will see this:

RecordID|Name|Car
1|Jim|Buick
1|Jim|Dodge
2|Dave|Chevy
2|Dave|Ford
3|Bill|NULL
4|Rick|Honda
4|Rick|Toyota

<edit> 11pm CST

Another thing I just thought of Jim, is concepts demonstrated here: http://allenbrowne.com/subquery-01.html

There's a lot of incredible information in Allen's query pages as he's obviously experienced with almost all of it. He explains there that using the "unmatched query" wizard does what you want. The problem here though is that you have to include the INNER JOINED records as well. If all else fails, you can always write a subquery using the code that the unmatched wizard outputs.

[jpkeller55]

Hey Adam,No luck yet with the other issue...I may be able to use what I have already.The reason I need to see all records is that my database has a two tables one containing questions and the other responses. I need to run a query to show specific questions based on certain criteria. Some questions do not have responses...they are more like alerts then questions. In any event, I need to see all the questions. With the inner join, I lose the records with no responses.In any event, thanks for the suggestions. I will give them a try later this weekend to find the solution. Take care.

[jpkeller55]

Left Outer Join instead of Inner Join did the trick and returned the data in the format that I need. Thanks Adam!

SELECT Table1.RecordID, Table1.Name, Table2.Car
FROM Table1 left outer join Table2 ON Table1.RecordID = Table2.NameID;

Produces:

RecordID|Name|Car
1|Jim|Buick
1|Jim|Dodge
2|Dave|Chevy
2|Dave|Ford
3|Bill|
4|Rick|Honda
4|Rick|Toyota

[ajetrumpet]

I have a page on queries too Jim. I learned a little bit myself too! if you want any more info on this sort of stuff in the future, you may want to read through that section on my website: http://www.ajenterprisesonline.com/ab/_queries/#joins

Glad you got it fixed!