SQL INNER JOIN with another SELECT Query

[OldBirdman]

I am attempting to assign a .RowSource to a ListBox using an SQL statement. I want to build the SQL statement "On-the-Fly", based on values in several controls on the form. I would like to restrict this discussion to Native Access, because when these discussions get beyond this, I don't understand, I cannot make them work, or I don't have the necessary resources.

For this question, I have built a simple DataBase, an AddressBook, because that is understandable to everyone, I don't need to spend effort explaining my issue, I can confine my discussion to Access. I have 2 tables.

Table = tAAA is as follows:

Key     Data
1       Wanted to be President of USA
2       Was President of USA
3       WWII General who became President of USA

Table = tNames is as follows:

Key     Pointer      Type   Name
1       1            2      Bobby Kennedy
2       3            2      Ike Eisenhower
3       2            2      Jack Kennedy
4       1            3      Robt. Kennedy
5       2            1      John F. Kennedy
6       3            1      Dwight David Eisenhower
7       1            1      Robert F. Kennedy

In tNames, Pointer is the Key in tAAA to link rows. Type is to determine if that name is displayed, after selection (see below).

To keep it simple here, there are 4 controls on the Form fAAA
txtSearch, a TextBox where User enters partial name to display in lbxNames
chkShowAlt, a CheckBox to specify whether Type=2 names are displayed. Type=1 are always displayed, Type=3 are never displayed.
lbxNames, a Listbox to display names as qualified by txtSearch and chkShowAlt
txtDisplay, a TextBox to display all bound data, but for this example, the field tAAA.Data. This is a simple test program, remember.

3 examples -
1) User enters "Kennedy", lbxNames should contain:
4 names if chkShowAlt=True
Bobby Kennedy
Jack Kennedy
John F. Kennedy
Robert F. Kennedy
2 names if chkShowAlt=Fals e
John F. Kennedy
Robert F. Kennedy

2) User enters "Robt", lbxNames should contain:
2 names if chkShowAlt=True
Bobby Kennedy
Robert F. Kennedy
1 name if chkShowAlt=Fals e
Robert F. Kennedy

3) User enters "Bob", lbxNames should contain:
2 names if chkShowAlt=True
Bobby Kennedy
Robert F. Kennedy
1 name if chkShowAlt=Fals e
Robert F. Kennedy

The SQL Statement "SELECT tNames.Pointer, tNames.Name FROM tNames WHERE (((tNames.Name) Like ""*Bob*"")) ;" returns the row with Key=1 & Name="Bobby Kennedy". I have saved this query as qStep1.
The SQL Statement

"SELECT tNames.Key, tNames.Name
FROM tNames INNER JOIN qStep1
ON tNames.Pointer = qStep1.Pointer
WHERE (((tNames.Type)<3))
ORDER BY tNames.Name;"

returns 2 rows, Key=1 and Key=9 when Type<3, and one row (Key=9) when Type<2.
That is exactly correct, and exactly what I want to do!!! But . . . I want a single SQL statement, if possible. I want to replace the "qStep1" with "SELECT tNames.Pointer . . .". Does this go in parens "()"? How do I refer to "qStep1.Pointer " when qStep1 becomes a SELECT Statement?

OIdBirdman

[FishVal]

Hi, OldBirdman.

A general syntax is the following:
[code=sql]
SELECT tAlias.fieldnam e FROM t1 INNER JOIN (SELECT ... FROM t2) AS tAlias;
[/code]
Though it is not obvious why do you use join in the example you have provided.

Regards.
Fish

[OldBirdman]

Thank you for the answer. And I thought it was obvious why I needed a JOIN. If I can do this without the JOIN, and therefore without the SubQuery, I would be much happier.

This seems (to me) to be a 2-step process. First, I search for "*Jack*", which gives me the record Key=3 Pointer=2 Type=2 Name=Jack Kennedy. But I want the Key=5 to appear in my ListBox, as all Type=1 names must show.

In an AddressBook, I want to search for a name using a nickname, or search for an Auto Repair using my mechanic's name.
In a movie, book, or music list, I want to be able to search by alternate title but display the true title. The movie "The Offence" also has the name "Something Like the Truth", and I might want to display both names. Is it "Rocky 3" or "Rocky III"? Either search would display "Rocky III" but not both.
In working with the program to keep track of my bird sightings, I find that certain birds have more than one common name as well as its scientific name. Locations (places I have visited to look at birds) may have multiple names, or have parks within parks. Zoos are often contained within larger parks, as are arboretums.

Same problem over and over and over . . . So I thought I would solve it in the general case, and use the solution in many applications. The problem is "Display the desired record with a minimum amount of keyboard or mouse effort". There are 10,000 species of birds. I have visited 1,700 places to view birds. My "To See/Seen" movie list is 2,800 titles. I have 6.700 web addresses rated for usefulness. Currently my address book has 1,000 names, and I am using Access's "Navigation Buttons". They are 1)Too small 2)Located on the wrong part of form 3)Not convienent for anything more than 100 records from either end of table 4)Require me to guess row # in a very little box, and press enter, then guess again. I want 1)Large controls and text 2)To control entry order (left to right, top to bottom) 3)Be able to use mouse OR keyboard to get to record, not mouse AND keyboard 4)Get closer with each try.
Currently I can find any movie (of 2800+) with 4 mouse clicks, and no keyboard. I am working toward improving my "Filter", and so my questions about subqueries. I hear about a movie "Something * Truth", and it appears that I do not know about that title. My filter is wrong, and needs to be better.

So how can I address all these issues without a JOIN?

OldBirdman

P.S. Is there a Biography section that would allow me to describe myself (beyond the introduction when I joined), or view other's Bio. It would be helpful to know some background about your members, and would therefore improve answers.
OB

[FishVal]

Thank you for the answer. And I thought it was obvious why I needed a JOIN ...

Sorry, I have overlooked that. Now I quite agree and consider your solution to be right.

Good luck.

[OldBirdman]

You do not know how much I appreciate that statement. I don't post lightly here. I spend about 40 hours working on a post. For you to confirm something is correct, when I agonize for hours over it is a reward in itself.

Thank you for your solution to my question.

OldBirdman

[NeoPa]

Most of our members spend far too little time and effort preparing their posts. This could never be said of you OB :) We appreciate your efforts.

In answer to your question I thought you may be interested in Subqueries in SQL. I know you have a good answer already, but seeing more detail may be more helpful still (I hope anyway).