Developer Forum

I have a database that contains a main table contining items, numerous
other tables that contain descriptions and additional tables that show
the relationship between the main table and the description tables.
Here is a simple example:

Main table:
ID
Animal Name

Description tables:

Size Table
ID
Description (ie small, medium, large, heavy light etc, each as a
record)

Color Table
ID
Description (ie black, white, gray, red, blue etc each as a record)

Attriubute Table
ID
Description (ie smart, stupid, curious, timid etc each as a record)

Relationship Tables:

SizeRelationship (one record for each relationship)
ID
Key to Animal Table
Key to Size Table

ColorRelationship (one record for each relationship)
ID
Key to Animal Table
Key to Color Table

AttributeRelationship (one record for each relationship)
ID
Key to Animal Table
Key to Attribute Table

Using this setup, If I wanted all animals that are smart, I would
select all record from the AttributeRelationship Table that contained
the key to "smart" in the Attrubute table and get all the keys to the
Animal table. This part is simple.

But if I want all animals that are Smart, Small and Red I have only
been able to come up with the model of selecting all record in the
Attribute that are Smart, then taking those the Keys for the animal
table returned in that match I would select out all records that
Matched the animal key and the Small key from the SizeAttribute Table.
Again taking those results I would select out all records in that
matched the animal key and the Color key in the Color Attribute table
to get a final result.

This is cumbersome, but works, but in actuality I have 8 different
relationship tables.

Is there a better way to do this using MySQL only instead of using php
to use the returned results to make the next query etc till I get the
real results? I can almost envision a SELECT that uses SELECTS in the
WHERE section ( is this possible?) to narrow it down.

Bill H

Use JOIN, see
http://www.yqcomputer.com/
http://www.yqcomputer.com/
--

//Aho

THanks - That is what I am looking for one question though, part of
the where statement needs to include the id from the current record
being looked at? Is this possible?

Bill H

Since you were already looking at the manual, why didn't you just look
at the examples instead of asking here?
http://www.yqcomputer.com/

A question for you. Can an Animal have more than one entry in each of
the Relationship tables I.E. can an animal have a SMALL and a LARGE
entry? If not, these should be fields on the main Animal table.

Yes they can have multiple entries, else I would have just put it in
the main table

Bill H

Well with such woolly attributes such as small, medium, large, heavy
light etc, its difficult to tell. After all, things like this are all
relative. For example, comparing animal types, a mouse would be small
and an elephant big. In that case, I would say that there would only
be one entry for each animal. However, within the category elephant,
you can have small ones and big ones. So I can say that an elephant is
small and a mouse is small, even though an elephant is never as small
as a mouse. Mixing weight with size is IMO a very bad move and only
serves to muddy the waters even more.

On Tue, 27 Jan 2009 06:23:01 -0800 (PST), Captain Paralytic






They are on Saturday Morning :)


Agreed. Two fields in one table, one for height and one for weight would make
more sense than a separate table that would allow for large and small to be
applied to the same item.
--
gburnore@databasix dot com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | | DataBasix | | 3 4 1 4 2 6 9 0 6 9 Black Helicopter Repair Svcs Division | Official Proof of Purchase
===========================================================================
Want one? GET one! http://www.yqcomputer.com/
===========================================================================

n Jan 27, 9:36m, Gary L. Burnore < XXXX@XXXXX.COM > wrote:
> ---------------------------------------------------------------------->--
> How you look depends on w>ere you go.
> ------------------------------------------------------------->-----------
> Gary L. Burnore | gt;
> > |
> DataBasix > | >> | 3 4 1 4 2 6 9 0 6 > > Black Helicopter Repair Svcs Division | Official Proof of Purchase> > ===================================================>=======================
> Want one? ET one! ttp://signup.databasix.com
> =============>==>==========================================================- Hide quoted text -
>
> - Show quoted text -

I have figured out most of it, but I can't seem to figure out how
within a select statement to know what the primary key of the record
being looked at is. Here is a >ample of what I have:
< SELECT `id` f>om `mouldings` WHERE<`id` !="0" AND `width` >="0" AND
`width` <="13" AND `height` >="0" AND `height` <="97" AND (SELECT `id`
from `styleLookup` WHERE `sawNumberIDX`="" AND `lookupIDX`="2") AND
(SELECT `id` from `centuryLookup` WHERE `sawNumberIDX`="" AND
`lookupIDX`="1") ORDER BY `sawNumber` ASC

What I need to do is be able to put the primary key of teh record
being looked at in `sawNumberIDX`="".

The only way I have figured out how to do that is to break thi> down
into 2 querys:
< SELECT `id` f>om `mouldings` WHERE<`id` !="0" AND `width` >="0" AND
`width` <="13" AND `height` >="0" AND `height` <="97"

to get a list of matching id's in an array and then do:

SELECT `id` from `mouldings` WHERE `id` ="$record" AND (SELECT `id`
from `styleLookup` WHERE `sawNumberIDX`="" AND `lookupIDX`="2") AND
(SELECT `id` from `centuryLookup` WHERE `sawNu>berIDX`="" AND
`lookupIDX`="1")

on each record. I can almost envision a "$this->id" structure to use.

Bill H