472,334 Members | 1,533 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,334 software developers and data experts.

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
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
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?

Feb 24 '08 #1
5 12150
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;
Though it is not obvious why do you use join in the example you have provided.

Feb 24 '08 #2
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?


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.
Feb 24 '08 #3
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
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.

Feb 24 '08 #5
32,511 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

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

Similar topics

by: Barry Young | last post by:
I am using Access to create a SQL query. It creates an Inner Join Query. I cut and paste the query into PL-SQL. When I try to execute the query I get...
by: Ike | last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL 4.0.20-standard. Thus, in trying to expedite the query, I am trying to set...
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple...
by: jason.evans | last post by:
Hi there. I am having an intrigueing problem. I have a query which left joins another query to itself twice. The original query is derived...
by: Nathan | last post by:
I have an application that uses an Access database to gather information on students' test scores. In the database there are three tables which...
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the...
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the...
by: Zeff | last post by:
Hi all, I have a relational database, where all info is kept in separate tables and just the id's from those tables are stored in one central...
by: teneesh | last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different...
by: AXESMI59 | last post by:
I created a query in the Access Query Builder that I modified to use with VBA Code so I could reuse it for any record I choose. The one that I...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.