469,602 Members | 1,727 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

SQL INNER JOIN with another SELECT Query

675 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. Key     Data
  2. 1       Wanted to be President of USA
  3. 2       Was President of USA
  4. 3       WWII General who became President of USA
  5.  
Table = tNames is as follows:
Expand|Select|Wrap|Line Numbers
  1. Key     Pointer      Type   Name
  2. 1       1            2      Bobby Kennedy
  3. 2       3            2      Ike Eisenhower
  4. 3       2            2      Jack Kennedy
  5. 4       1            3      Robt. Kennedy
  6. 5       2            1      John F. Kennedy
  7. 6       3            1      Dwight David Eisenhower
  8. 7       1            1      Robert F. Kennedy
  9.  
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=False
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=False
Robert F. Kennedy

3) User enters "Bob", lbxNames should contain:
2 names if chkShowAlt=True
Bobby Kennedy
Robert F. Kennedy
1 name if chkShowAlt=False
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
Expand|Select|Wrap|Line Numbers
  1. "SELECT tNames.Key, tNames.Name
  2. FROM tNames INNER JOIN qStep1
  3. ON tNames.Pointer = qStep1.Pointer
  4. WHERE (((tNames.Type)<3))
  5. 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
Feb 24 '08 #1
5 11780
FishVal
2,653 Expert 2GB
Hi, OldBirdman.

A general syntax is the following:
Expand|Select|Wrap|Line Numbers
  1. SELECT tAlias.fieldname FROM t1 INNER JOIN (SELECT ... FROM t2) AS tAlias;
  2.  
Though it is not obvious why do you use join in the example you have provided.

Regards.
Fish
Feb 24 '08 #2
OldBirdman
675 512MB
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
Feb 24 '08 #3
FishVal
2,653 Expert 2GB
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.
Feb 24 '08 #4
OldBirdman
675 512MB
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
Feb 24 '08 #5
NeoPa
32,200 Expert Mod 16PB
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).
Feb 25 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Barry Young | last post: by
3 posts views Thread by Ike | last post: by
3 posts views Thread by Prem | last post: by
4 posts views Thread by Nathan | last post: by
3 posts views Thread by Zeff | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.