Query help: many to one?

T

Teresa Redmond

Hi, All;

I have one query left that I need help with, it's from one table to
another, like:

Additional:
Add_PKEY
Add_Num

Seat:
Seat_PKEY
Add_PKEY1
Add_PKEY2
Add_PKEY3
..
..
..
Add_PKEY12

I can't figure this out at all. Each record in Seat can have from 0
to 11 Add_PKEY values, the value can be either 0 or some text that
will match up with a record in Additional. I need one record as a
result of a query that will tell me the Seat_PKEY as well as the
Add_Num that corresponds to each of the Add_PKEYs in Seat. I am
totally stumped, have been trying things I'm seeing here, nothing is
quite it. This latest gives me a "missing operator" error:

SELECT Additional_CLINs
FROM Additional_CLIN_Nums ac INNER JOIN Seat_ID_Nums si ON
(ac.Add_CLINs_PKEY = si.Addl_CLIN12_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN11_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN10_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN9_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN8_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN7_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN6_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN5_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN4_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN3_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN2_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN1_PKEY);

I know this is very incorrect, I am just too close to it so that I
can't even see it anymore. Any help is greatly appreciated!

Thanks,
Teresa
--
~teresa~
AFH Barwench

^..^ "Never try to outstubborn a cat." Robert A. Heinlein ^..^
http://pixelmeow.com/ http://www.heinleinsociety.org/
http://pixelmeow.com/Book_Exchange/index.htm
http://pixelmeow.com/forum/
aim: pixelmeow msn: (e-mail address removed)
 
M

Marshall Barton

Teresa said:
I have one query left that I need help with, it's from one table to
another, like:

Additional:
Add_PKEY
Add_Num

Seat:
Seat_PKEY
Add_PKEY1
Add_PKEY2
Add_PKEY3
.
.
.
Add_PKEY12

I can't figure this out at all. Each record in Seat can have from 0
to 11 Add_PKEY values, the value can be either 0 or some text that
will match up with a record in Additional. I need one record as a
result of a query that will tell me the Seat_PKEY as well as the
Add_Num that corresponds to each of the Add_PKEYs in Seat. I am
totally stumped, have been trying things I'm seeing here, nothing is
quite it. This latest gives me a "missing operator" error:

SELECT Additional_CLINs
FROM Additional_CLIN_Nums ac INNER JOIN Seat_ID_Nums si ON
(ac.Add_CLINs_PKEY = si.Addl_CLIN12_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN11_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN10_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN9_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN8_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN7_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN6_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN5_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN4_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN3_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN2_PKEY)
INNER JOIN ac on (ac.Add_CLINs_PKEY= si.Addl_CLIN1_PKEY);
Click to expand...


The field names in the query do not match the field names
you posted for the tables, so I don't know what to think.

At this point, the best I can suggest is to change the INNER
JOINs to LEFT Joins.
 
T

Teresa Redmond

The field names in the query do not match the field names
you posted for the tables, so I don't know what to think.

At this point, the best I can suggest is to change the INNER
JOINs to LEFT Joins.
Click to expand...

Sorry, had to jump up and go to a meeting. I hit "send" without
changing the query to reflect the example... I changed the query
above and I'll go try the LEFT join that you suggested, and I'll add
what I want to see:

Seat_PKEY Add_Num Add_Num Add_Num
Seat_PKEY Add_Num
Seat_PKEY Add_Num Add_Num

etc.

Thanks,
Teresa
--
~teresa~
AFH Barwench

^..^ "Never try to outstubborn a cat." Robert A. Heinlein ^..^
http://pixelmeow.com/ http://www.heinleinsociety.org/
http://pixelmeow.com/Book_Exchange/index.htm
http://pixelmeow.com/forum/
aim: pixelmeow msn: (e-mail address removed)
 
M

Marshall Barton

Teresa said:
Sorry, had to jump up and go to a meeting. I hit "send" without
changing the query to reflect the example... I changed the query
above and I'll go try the LEFT join that you suggested, and I'll add
what I want to see:

Seat_PKEY Add_Num Add_Num Add_Num
Seat_PKEY Add_Num
Seat_PKEY Add_Num Add_Num
Click to expand...


I don't have much experience working with the messy affais
you run into when working with unnormalized tables. I
**think** this might get you closer to what you want???

SELECT s.Seat_PKEY,
a.Add_Num,
b.Add_Num,
c.Add_Num,
. . .
FROM Seat s
LEFT JOIN Additional a
ON s.Add_PKEY1 = a.Add_PKEY
LEFT JOIN Additional b
ON s.Add_PKEY1 = b.Add_PKEY
LEFT JOIN Additional a
ON s.Add_PKEY1 = c.Add_PKEY
. . .

If that doesn't meet your immediate needs, then I have to
strongly suggest that you properly normalize your table
structure before attempting to continue with this exercise.
In fact, even if this is adequate, you should take time out
to normalize so the next problem doesn't become
insurmountable.
 
T

Teresa Redmond

I don't have much experience working with the messy affais
you run into when working with unnormalized tables. I
**think** this might get you closer to what you want???

SELECT s.Seat_PKEY,
a.Add_Num,
b.Add_Num,
c.Add_Num,
. . .
FROM Seat s
LEFT JOIN Additional a
ON s.Add_PKEY1 = a.Add_PKEY
LEFT JOIN Additional b
ON s.Add_PKEY1 = b.Add_PKEY
LEFT JOIN Additional a
ON s.Add_PKEY1 = c.Add_PKEY
. . .

If that doesn't meet your immediate needs, then I have to
strongly suggest that you properly normalize your table
structure before attempting to continue with this exercise.
In fact, even if this is adequate, you should take time out
to normalize so the next problem doesn't become
insurmountable.
Click to expand...

I am open to suggestion, I didn't think this was a wonderful way to do
this but I really couldn't think of a better way. What do you
suggest? I'd appreciate even an RTFM, if you have the FM I should
R... :)

I'll be researching normalization in the meantime; it's been a good
long while since I have studied that. This particular problem is the
last one with this database, although I would prefer a "cleaner"
solution than the one I came up with last night. It got the results I
wanted, but it really isn't exactly it, not being a single record.
I'm just hiding the excess text boxes on the form, so that it "looks"
right.

Thank you so much for your patience with me...

--
Teresa Redmond
--
~teresa~
AFH Barwench

^..^ "Never try to outstubborn a cat." Robert A. Heinlein ^..^
http://pixelmeow.com/ http://www.heinleinsociety.org/
http://pixelmeow.com/Book_Exchange/index.htm
http://pixelmeow.com/forum/
aim: pixelmeow msn: (e-mail address removed)
 
T

Teresa Redmond

If that doesn't meet your immediate needs, then I have to
strongly suggest that you properly normalize your table
structure before attempting to continue with this exercise.
In fact, even if this is adequate, you should take time out
to normalize so the next problem doesn't become
insurmountable.
Click to expand...

I found this from a post from Mr. Vinson in another thread:
A better design would be to have *THREE* tables: your current main
table (less the ten fields); a table of valid job operations; and a
"resolver" table with the primary key of your current table and
another with the job operation (the actual value or the ID, if the Job
Operations table has a numeric primary key).
Click to expand...

Is this like what you mean? I am trying this out now with my tables,
to see what I get with it. Thank you all!
 
M

Marshall Barton

Teresa said:
I found this from a post from Mr. Vinson in another thread:


Is this like what you mean? I am trying this out now with my tables,
to see what I get with it. Thank you all!
Click to expand...


John's describing the standard Many to Many relationship
structure. Each Additional can have many (0, 1 or more)
seats and each seat can be used for many Additionals. So
you need to use a "junction" table, SeatsForAdditionals,
that has a Many to One relationship with each of the other
tables.

This may change a lot of what you've already done, but most
of it should be quite a bit simpler, certainly this query
problem will be tons easier.
 
T

Teresa Redmond

John's describing the standard Many to Many relationship
structure. Each Additional can have many (0, 1 or more)
seats and each seat can be used for many Additionals. So
you need to use a "junction" table, SeatsForAdditionals,
that has a Many to One relationship with each of the other
tables.
Click to expand...

I've created the table, called Seat_Add_XREF, with a pkey(autonumber),
the seat_pkey, and the add_pkey from the Seat table. I've got all the
data in there (using VB, much easier for me). The Add_PKEY value
correspondes to the Add_PKEY value in the Add_Nums table, so now I
have (the names are pseudo-names due to the length of the real ones):

Seat:
Seat_PKEY
User_PKEY
....

Add_Nums:
Add_PKEY
Add_CLIN

Seat_Add_XREF:
S_A_PKEY
Seat_PKEY
Add_PKEY

I like this much better than what I had before.
This may change a lot of what you've already done, but most
of it should be quite a bit simpler, certainly this query
problem will be tons easier.
Click to expand...

You know, this is the sort of thing where it's *better* to get rid of
what you've done, because like you say, the rest will be much easier.
The only thing I really have to change is the part of the form where I
want to display this information, all the rest is fine.

I am going to go back to work on this, and if I have further problems,
I will be sure to ask. Thank you so much!
 
M

Marshall Barton

Marshall Barton scribbled:
Teresa said:
I've created the table, called Seat_Add_XREF, with a pkey(autonumber),
the seat_pkey, and the add_pkey from the Seat table. I've got all the
data in there (using VB, much easier for me). The Add_PKEY value
correspondes to the Add_PKEY value in the Add_Nums table, so now I
have (the names are pseudo-names due to the length of the real ones):

Seat:
Seat_PKEY
User_PKEY
...

Add_Nums:
Add_PKEY
Add_CLIN

Seat_Add_XREF:
S_A_PKEY
Seat_PKEY
Add_PKEY

I like this much better than what I had before.


You know, this is the sort of thing where it's *better* to get rid of
what you've done, because like you say, the rest will be much easier.
The only thing I really have to change is the part of the form where I
want to display this information, all the rest is fine.

I am going to go back to work on this, and if I have further problems,
I will be sure to ask. Thank you so much!
Click to expand...


Would that everyone would grasp this issue so readily ;-)
 
Top