Solved

General SQL - Query Master/Detail Tables

Posted on 1997-09-27
20
1,184 Views
Last Modified: 2008-03-17
I have two tables in an Master-Detail relationship, Activities and Locations respectively.  As the names imply, the Activities table contains things to do, such as Restaurants, Parks, Plays, Movies, etc.  The Locations table contains all locations for an Activity if there is MORE than one location.  The master table has an Address and a City field.  If there is only one location, the address & city is stored on the Mater Table.  If there is more than one location, "Various" is inserted in the Address & City fields on the Master table and the 'real' addresses and Cities for that Activity are stored in the Locations table.

Now, how do I find a particular CITY in EITHER table using SQL or some other method?  Below is the English translation of what I want to do.

****Find every MASTER record where they have a Location in "SOME CITY".****

This of course would require the query to search the master table for SOME CITY then search the Locations table for SOME CITY - and if either were found - select its master record.

I'm looking for someone to correct my query string OR tell me a better way of doing this.  I tried this SQL string below in Delphi, Paradox, and Access 97 - and all of the applications hung - so I don't know if this works or not.  I have about 3,000 records in both the Master and Detail tables, but all fields being searched are indexed (although I can't figure out if Delphi uses the index or not).  The tables are Paradox 4.5/5.0 tables locally in D2 Win95, although I don't think that matters since this is really an SQL issue, not necessarily Delphi.  

Note: Name & State are the Primary Key & Referential Integrity Links

SELECT DISTINCT D.Name, D.State, D.Address, D.City
FROM "Activity.DB" D, "Locations.DB" D1      
WHERE
(D.City = 'Atlanta')
 AND (D1.Name = D.Name)
 AND (D1.State = D.State)
 OR (D1.City = 'Atlanta');
0
Comment
Question by:d4jaj1
  • 11
  • 9
20 Comments
 

Expert Comment

by:vtsien
Comment Utility
Your design was wrong.  It violates 2nd normal form.  You need to
get rid of the repeating group.  A design is important before the
physical database creation.  No matter how small the database is.
You need to know the relationship.  In your case, one activity has many locations.  Therefore the relationship is one-to-many.
I would do this way:
1. create an autonum in Access for the activity table:

activity_code number,
activity_name text (unique)

2. create location table with the activity_code in it:
activity_code number,
state text,
address text,
city text

one activity row can have one to many location rows (the relationship (RI) is master/detail as you said or parent/child used in data modeling.

3. create a relationship between activity and location with one to many relationship.

4. For your sql, you can use:
select activity_name, state, address, city
from activity a, location b
where a.activity_code = b.activity_code

5. If you are not clear yet, look at my data modeling course material at: http://www.doc.anl.gov/dmclass/ which I taught every six months inside the company.  At the end, there is a demo modeling tool (erwin) that you can download.  It can only handle 8 entities, but enough for your test run.  After installation, change the target server to your database such as paradox, access.  After modeling, use schema generation to create access basic to create the tables for you.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
Most of what you said is correct and I am aware of the techniques.  I generaly don't assign text values as keys to tables (I didn't build this table), but for this particular table - Name  & State are already the keys.  My customer doesn't want to take the time & money necesary to rebuild their entire database)

Any any case, the SQL you offered (4), although correct, still doesn't solve the problem I have.  The Master table has Various in the City field if there are more than one Locations.  So a query on the Master table for Atlanta wouldn't necessarlly bring back all Locations since some Atlanta location are in the Locations table.  How do I get around this limitation?

Also, what do you mean "violates the 2nd Normal form" and "the repeating group"
0
 

Expert Comment

by:vtsien
Comment Utility
The way you did was by no means a relational table.  It is a hierachical model like ibm's ims/db - first generation of database.  It is a tree structure, an upside-down tree.  The root is the activity and each root may have one to many occurance of child.  If you really like this type of model, you should contact Microfocus.  They have IMS on the PC platform.

By putting a various on the root level without relationship on the location table, only IMS can handle that with a pointer.  relationship table like Access cannot handle it unless you put the unique key for activity on the child table.  I cannot help someone insist on his/her own design and asking others to work on this/her design.  If you really need help, at least write down the table name and each column, data type, and primary key, foreign key of each table and their relationship and some sample rows, and your relationship between the two tables.  maybe people can help.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
As I said the first time, I DID NOT BUILD THIS TABLE.  This is someone else's mess I was asked to clean up.  The customer doesn't have the resources or the time to rebuild all of their tables, forms, reports, etc.  This is a telephone support organization, thus they can't simple stop tracking their calls Simply because they are in a bad situation, doesn't mean I can't explore alturnatives for them.  If I wanted to, I could simply perform 2 queries, but I'm assuming there is a way to do this in one.  So STOP complaining about the database structure and come up with some of ideas/solutions using the knowledge you claim to have so much of!

As for your help, I would hate to be in your class!!  I would suggest you work on you communications skills. You come across very offensive - which is not the impression you want to give when your supposed to be 'helping' someone.
0
 

Accepted Solution

by:
vtsien earned 70 total points
Comment Utility
Sorry about that.  I did not know that was the case.  I thought you were designing something.  In that case I thought why you did not want to do the way I thought everybody should know.  forgive me.  You were right.  Actually I just found out you were pretty good at front-end.  I thought if I were doing some front-end and other people thought that was easy enough why I did not understand, I would felt him offensive too.  Thanks for reminding me of it.  I might offend students in class without realizing that.  You might feel me stupit when talking about front-end. Well that certainly is good of you to say that.  I will correct that.  Well, to show my apollogy.  Let us work together to have it solved if you are still open to my suggestions.  Or I will quit this and I am sure there will be other people who can handle this well.  thanks very much.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
I that a strong person to admit when they made a mistake, and I commend you for it.  Yes, I am open to any ideas/suggestions you may have to fix this problem.  Thanks.
0
 

Expert Comment

by:vtsien
Comment Utility
In fact I thought that you needed a model to replace the old one.    Therefore, I thought why you did not use the new model.  Now I know that what you need is not a model but an sql to solve the problem. To go back to your topic, your sql:

SELECT DISTINCT D.Name, D.State, D.Address, D.City
FROM "Activity.DB" D, "Locations.DB" D1
WHERE
(D.City = 'Atlanta')
AND (D1.Name = D.Name)
AND (D1.State = D.State)
OR (D1.City = 'Atlanta');

It is a Cartesian join.  The result returns you about 9,000,000 rows to be sorted in memory into distinct combination. Since you don't have that much memory, your query hangs.  If you can give me how many Atlanta you have in activity table and how many Atlanta in location table and the same name I can calculate exactly how many rows you have.  In fact you can take away distinct... and put a count(*) and you will see I am right.  The way to get it work is to change your "or" to "and" ((D.City = 'Atlanta') AND (D1.Name = D.Name) AND (D1.State = D.State) and (D1.City = 'Atlanta').  Since I have not seen the contents of your tables and am not clear about the purpose of your sql (while you are clear about your sql).  You may want such query as:
SELECT DISTINCT D.Name, D.State, D.Address, D.City
FROM "Activity.DB" D, "Locations.DB" D1
WHERE
(D.City = 'Atlanta')
AND (D1.Name = D.Name)
AND (D1.State = D.State)
union
SELECT DISTINCT D1.Name, D1.State, D1.Address, D1.City
FROM "Activity.DB" D, "Locations.DB" D1
WHERE (D1.City = 'Atlanta')
and (D.City = 'Various')
AND (D1.Name = D.Name)
AND (D1.State = D.State)

You need to change some if you have null for name or city in your loation table.  Again without the contents of your table it is just a guess.  If it doesn't work, please list out the fields for each table and their primary key and the contents (or about some typical 30 rows of both your tables).  I can certainly fix it.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
I think we're almost there.  I made a few modifications to the SQL string (although I'm not sure I made the right changes) to give you a better idea of what I'm looking for.  Here's my current string;

SELECT DISTINCT D.Name, D.State, D.Address, D.City
FROM "Activity.DB" D, "Locations.DB" D1
WHERE
(D.City = 'Atlanta')
AND (D1.Name = D.Name)
AND (D1.State = D.State)
union
SELECT D.Name, D.State, D.Address, D.City  {I don't actually want to select any of the Locations.db records.  I also removed the DISTINCT because I got an error saying I must use UNION ALL}
FROM "Activity.DB" D, "Locations.DB" D1
WHERE (D1.City = 'Atlanta')
and (D.City = 'Various')
AND (D1.Name = D.Name)
AND (D1.State = D.State)

The string above 'almost' works, meaning it finds 2 of the 3 instances I wanted to find.  It finds Master records that;

1)  Have 'Various' in the Activity.db City field and 'Atlanta' in the Locations.db City field.
2)  Have 'Atlanta' in the Activity.db City field and 'Atlanta' in the Locations.db City field. (This is of course what the query is supposed to do, but 'Atlanta' in both tables isn't really valid since all locations greater than 1 should be in the Locations table.  Don't worry about this though, it's not your problem since the query worked in this case as it should.  I'll this problem on my own.)

The instance it does NOT find is below.

3)  Records that have 'Atlanta' in the master table, but not in the Locations table, i.e., records with only 1 location (no detail records).

So that's the problem now and if I knew a little more about SQL statements, I could probably figure out why it isn't working, but I will rely on your expertise in this area instead of making any more changes on my own.  It seems like the section after the union 'overwrites' the first section (I think).  As for the keys, there are as I mentioned in the original message.

Table            Primary Key
========================
Activity            Name, State
Locations      Name, State, Address

Referential Integrity Link

Activity.db Name & State --- TO ---  Locations.db Name & State.

0
 

Expert Comment

by:vtsien
Comment Utility
Use "union all" instead of "union".  Originally when I saw distinct in your sql statement, I thought you only want to return one copy of each set of duplicate rows selected.  The difference between "union all" and "union" is the former displays all the rows you fetched the latter displays only one copy of the the rows you selected.  Otherwise, check the Name on both tables for that row to see if they are identical.  If not, they will not display.  Also union all and union are standard sql (sql-89?), but not every database vendor supports that.  If it doesn't work, check the user's guide.  The vendor may not have it ready for that version.  One more word, when you use distinct, remember it takes extra time to process because the database engine takes the rows your selected and sorted to return you one row for each set of identical rows.  If you don't have enough memory, it could be very slow.  In fact if I were you I would merge both tables into one activity table because your database would run faster without  joining both tables.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
For some reason, it still didn't return any records where a master records doesn't have any detail records, i.e., 'Atlanta' in the Activity table and 0 records in the detail (Locations) table (because there is only one location).  Here are some statistics;

Total records in table = 2,451
Total records w/only 1 location and in 'Atlanta' = 1,104
Total records that have 'Various' in the Master table's City field (although they may NOT have Atlanta in the detail table) = 299
Total # of records the old query returned = 277.
Of this 277, ALL of them either have Atlanta in both tables City field OR have Various in the Master city field & Atlanta in the detail.

So, I started to play with it a bit and finally got the code below to display all the values I needed;

SELECT Name, State, Address, City
FROM "Activity.DB"
WHERE City = 'Atlanta'
union all
SELECT DISTINCT D.Name, D.State, D.Address, D.City
FROM "Activity.DB" D, "Locations.DB" D1
WHERE (D1.City = 'Atlanta')
and (D.City = 'Various')
AND (D1.Name = D.Name)
AND (D1.State = D.State)

Problem now is I can't use the select all (*) feature due to a parameter mismatch and/or incorrect token.   I tried the code below

SELECT * FROM "Activity.DB"
WHERE City = 'Atlanta'
union all
SELECT DISTINCT *
FROM "Activity.DB" D, "Locations.DB" D1
WHERE (D1.City = 'Atlanta')
and (D.City = 'Various')
AND (D1.Name = D.Name)
AND (D1.State = D.State)

Not sure what the above errors mean.  Also, I know the DISTINCT won't work since one of the fields being returned is a memo.  I tried to use DISTINCTROW, but I guess local SQL doesn't support it.  Is there another way of accomplishing what DISTINCTROW or DISTINCT does.  I guess I should have told you I needed all the fields in the table, but I shortened the question to make it easier to understand and assumed it was just a matter of added the asterisk *.

I hate when I think I have a problem solved, only to find out I still have a ways to go :-{
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Expert Comment

by:vtsien
Comment Utility
On Friday, I had my apache webserver blown up, mail server crashed on my unix machine and hard drive i/o error on NT machine.  So far webserver and mailserver is up and running but the hard drive is still in trouble.  I didn't have time for other things.  Now back to your problem.

As far as the problem is concerned, you said, "I guess I should have told you I needed all the fields in the table, but I shortened the question to make it easier to understand and assumed it was just a matter of added the asterisk *."  And I felt the same way, which means I have fulfilled your original request and had your problem solved.  The * problem is something you just raised.  Don't you think I deserve the original 50 points or even more instead of asking me to do some extra things without reward?  You know the level of difficulty and the time I spent on it.  So think about it even it is a game only.

As for your problem, there are two more ways to solve it.  Again solving it with my mind's eye is more time-consuming than with some actual rows to play on it.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
Your right.  As soon as I sent the message, I thought I should gave told you what the fields were in the tables.  I think I'm getting the parameter mismatch because the fields in the Master table aren't the same as the detail table.  I guess I could copy the structure of the master table to the detail, but that defeats the purpose of the master-detail relationship and requires me to restructure their tables (which as I mentioned before, I can't do).  In any case, even though I figured out the final working answer myself, I do believe your assistance got me there and you deserve an 'A' for your efforts.  If you can answer my additional question (and it isn't restructure the table), I would be willing to raise the points to 75. If you look at my history in grading, you'll see I always give A's - as long as you answer it correctly.  Here's the field layout (keys & Ref Int are in a prev msg)

Master - Activity
=============
Name                  String
State                      String
Address                   String
EntertainmentType       String
Phone                String
EventType       String
City                   String
DinningType       String
CuisineType        String
Visa,MC,AMEX,Discover    Logical
TimeOpen,TimeClose     String
HighCost,LowCost      Money
County           String
Comments      Memo

Detail - Locations
==============
Name        All Strings
State
Address
City    
County
Phone            
0
 

Expert Comment

by:vtsien
Comment Utility
Hello, d4jaj1 of San Diego,

Fortuately, you put the column names here for both activity and location tables.  Otherwise, there will be problem again.  The number of columns are not equal.  Therefore you cannot use * in your select statement.  You have 14 fields in the activity and 6 in the location.  In the "union all" query, you want to put * for both the upper select and lower select in the union query.  The net effect is you are selecting 14 fields in the first part of the query and 20 fields in the second part of the query.  It will not work.  If you want it this way, you have to spell out the names of each field for both parts of the query and then add 4 spaces in the first query to make them the same length.  It will work in that case.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
Just got back from vacation.  I understand the query won't work because the table definitions aren't the same.  What I don't understand is what you mean by adding '4 spaces' to the first query.  Do you mean add 4 dummy fields to the query or really add 4 spaces.  Also, why 4 - don't you mean 6?  An example would be most helpful.

Also, as I meantioned before, I will give you an A for the initial question, whether you answer this one correct or not.  If you can answer this correctly, I will add an additional 25 points (A as well)

Thanks.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
Any comments on the second question?
0
 

Expert Comment

by:vtsien
Comment Utility
Hi, I had a bad time during the last few weeks.  All Western Digital Drives on my NT are 'broken'. The notorious WD AC31600 drive.  Just take a look at www.wdc.com/quality/.  Even WD admits the problem.  In additiion to that two Seagate Hard drives on Sun server also went bad (hardware error, according to messages).  I tried to fix, but there was no use.  No two new drives are mounted and all Oracle applications must be moved to the new drives with min downtime.  It is really fun!

Now back to your story.  Your columns look like:
                 Name String
                 State String
                 Address String
                 EntertainmentType String
                 Phone String
                 EventType String
                 City String
                 DinningType String
                 CuisineType String
                 Visa,MC,AMEX,Discover Logical
                 TimeOpen,TimeClose String
                 HighCost,LowCost Money
                 County String
                 Comments Memo

                 Detail - Locations
                 ==============
                 Name All Strings
                 State
                 Address
                 City
                 County
                 Phone
You use the first table union the the joining result of the two tables.  You will get rid of the identical columns in both tables and sequence the field names in certain order and in your first
table you have the identical field names for the columns.  You
need to match the column of the first part with the second part of the union.  In case the field name appears in the second part but fails to show in the first part, you should use ' ', to replace the missing field name in your first part thus you can make the first part and second part the same number of columns and position the same.  I am sure this will work.  I still have two more ways to do the query but this will work.  Good luck.  Because of the work, I can't show up in this site as often as before.
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
How can I get around the memo field issues.  In teh second section of the query, I need to select Distinct records. SQL won't allow distinct on memo fields.  Id there are way to get around this?
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
Adjusted points to 75
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
ANY WORD ON MY LAST QUESTION?  I WANT TO GO AHEAD AND GRADE THE QUESTION NOW.
0
 

Expert Comment

by:vtsien
Comment Utility
Can you send me the layout (including data type) of both tables and some records for each.  I will work out an answer for you.  I hate working in dark and always being told of some new issues, which seemed to me like a bottomless pitch.  I work for the Fed Government and have no interest in your field even if I can guess out who you work for.  I plan to quit this exchange.  My email is vtsien@iris.ctd.anl.gov
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now