By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

UNION Query for Alternate Names

100+
P: 675
Assume 2 tables
tblP {Primary Table}
tblP.Key {AutoNumber and all that}
tblP.Name {Name of EE, Movie, Bird Species, or Whatever}

tblA {Alternate Table}
tblA.Key {AutoNumber and all that}
tblA.PtrToP {Link to tblP.Key so tblA can be joined, or displayed in form}
tblA.AltName {Alternate Name for EE, Movie, Bird Species, or Whatever}

Assume I am working with 2 tables with movie titles. Title for a movie might be "2 Days", or is it "Two Days" I want the query to find this, whether user types "2 Day" or "Two Day" So my primary table, tblP, has a row with key=7 and title = "2 Days" and tblA has a row with PtrToP=7 and AltName="Two Days"

My user enters "Two Days"

SELECT PtrToP FROM tblA WHERE (((AltName) Like "*Two Days*")) UNION SELECT tAAA.Key FROM tblP WHERE (((Name) Like "*Two Days*"))

This query gives me one row, with one field, and that field has the value 7. No problem.

Now I want to display all titles found. I somehow need to do another union, this time for the Movie Name, but will each half of the union have an inner join?

The best example I can come with is to go to "www.Netflix.com" and type "Rogue" into the search box. "Rogue" is an alternate title to a movie called "War", and their search finds both a movie called "Rogue" and a movie called "War" with an alternate title "Rogue". That is what I am trying to do!

OldBirdman
Dec 15 '07 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, OldBirdman.

I think the current table structure is not suited well for it.
The following scheme will be more suitable.

tblIdentities
keyIdentityID Autonumber(Long), PK
lngPrimaryName Number(Long), FK(tblIdentityNames.keyIdentityNameID)

tblIdentityNames
keyIdentityNameID Autonumber(Long), PK
keyIdentityID Number(Long), FK(tblIdentities.keyIdentityID)
txtIdentityName Text

Regards,
Fish
Dec 15 '07 #2

100+
P: 675
I knew that. As this program is for my personal use, managing a table with the name in it is easier, rather than a table of names. "Good" database design says you are correct.

It seemed so easy to get a list of keys and then query both tables. I was doing this for a learning experience as well as a solution. What started as a simple list to keep track of movies I have rented has become a fairly complex program.

Thank you again for your thoughts and ideas. As I do not know anybody who uses Access, this forum is my only place to exchange ideas.

OldBirdman
Dec 15 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Well.

Seems that you'll need to use your query returning keys to filter both tables (using INNER JOIN) and then union both datasets - the cost of tables' structure current design. ;)
I appreciate "good" database design because it makes things easier.

Regards,
Fish
Dec 15 '07 #4

100+
P: 675
Thanks again for humoring me. Sometimes "Good" database design conflicts with simple table design. As there is only one user, me (at least for now, but I'm trying to get some local DVD Rental stores to put in a slightly different version as a kiosk for location. Instead of "Seen"/"Not Seen", it would return a classification or location, ie Drama, Comedy, . . .)

I'll probably move all titles to their own table, but I may play awhile longer to see if I can get it to work this way, as a learning exercise.

OldBirdman
Dec 15 '07 #5

100+
P: 675
After 2 months of meditation and cogitation (and some rumination), I have decided that this question belongs in an AddressBook environment. I have begun rewriting my address book, and I certainly do not want UNION queries.

See also "SQL INNER JOIN with another SELECT Query" also posted to this forum.

When I first wrote my Address Book program, everyone had 1 phone, 1 address, and 1 spouse.

I now look at my primary table (tAAA) and note that every field can have multiple entries, therefore these are related tables, with fields pointing to the Key field in tAAA.

Name = "Foreign Auto Repair" or "Dave"
Address = "123 Main, Miami, FL" or "c/o FedEx, 876 Central, Miami, FL"
Phone = Cell: ; Land: ; VoiceMail: ;
etc

Has anyone designed a system such that the primary/central/main table has only 1 field, the KEY field? That seems to be where I am headed, but I never heard of such a thing.

Please point me in the direction of discussions, white papers, blogs, etc. on this concept.

OldBirdman
Feb 24 '08 #6

Post your reply

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