Just a quick note to Ed and Amer,
You two were right on, with this. I took the plunge and re-thought my db as
you both suggested. Sage advice, as successive queries and relationships
have been much easier to achieve with the new allocation table. It's clear
to see that this method also will offer much more flexibility as my project
evolves in the future.
Thanks again,
Verdon
To begin with I wrote:
>> Essentially...
>>
>> 1) I have a table of portfolio 'pieces', each row of which has a unique ID
>>
>> 2) I have a table of 'projects', which are comprised of a number of
>> 'pieces'. There is a column in this projects table called 'pieces', which
>> contains a delimitated string of all the 'pieces' related to that 'project',
>> in the format of '1||2||3||5||12||16||17||24||25'
>>
>> 3) When viewing a data sheet of a single row in the table 'pieces', I'd like
>> to be able to include a reference to any 'projects' that the said piece may
>> be a member of.
>>
>> I've tried a few things like
>>
>> $ID = '1';
>>
>> SELECT * from projects WHERE pieces LIKE '$ID%'
>> - which will find anything that starts with $ID, such as
>> 1||3||4
>>
>> SELECT * from projects WHERE pieces LIKE '%$ID%'
>> - which will find anything that contains $ID, such as
>> 1||3||4
>> 5||1||72||8
>> 3||5||21||9
>> 3||5||17||9
>>
>> What I can't figure out how to do is to select this
>> 3||5||1||9
>>
>> Without selecting something like
>> 3||5||21||9 or
>> 3||5||17||9
>>
>> Is this possible in a single sql statement, or do I have to select all rows
>> of 'projects' and use php to somehow loop through the results, explode the
>> delimitated strings, and look for the exact match this way, somehow.
>>
Then Ed and Amer answered:
> From: Ed Leafe <[EMAIL PROTECTED]>
>
> While this is a clever approach, a more robust approach is to create a
> third table, usually referred to as an allocation table or a
> many-to-many table, which contains the links between pieces and
> projects. Assuming that both Pieces and Projects have PK fields, the
> allocation table would consist of 3 columns: its own PK, the PK of the
> Project record, and the PK of the Pieces record. There would be as many
> records for a given Project PK as there are Pieces in the Project.
>
> You can then join the Project to its Pieces as follows:
>
> select Project.*, Pieces.*
> from Project left join ProjPieces on Project.projPK = ProjPieces.projPK
> left join Pieces on ProjPieces.piecePK = Pieces.piecePK
> where Project.projPK = [some value]
>
> Note that using this design, it is just as easy to get all the pieces
> for a given project as it is to get all the projects a given piece
> belongs to.
>
> From: Amer Neely <[EMAIL PROTECTED]>
>
> It may be possible to cruft a query for what you want, but I'd suggest a
> slight re-design of your tables. Do some reading up on designing
> relational databases ("Database design for mere mortals" by Michael J.
> Hernandez is an excellent choice. ISBN: 0-201-69471-9).
>
> Any time you have more than one value in a field is an idication you
> should consider making that field a separate table. In your case I'd
> make a new table of 'pieces' and populate it with something that
> identifies which client AND project each piece belongs to. Maybe a
> combination of 2 fields (client id + project id). Then your select would
> include 2 'where' conditions checking client id and project id.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
