Professional Web Applications Themes

Tricky Join Query - MySQL

Hello. I have a really tricky sql problem and would appreciate any help I can get. My database stores clients, jobs, contacts and notes. Each note can potentially (but not necessarily) be related to a client, a job and a contact. This relationship is maintained by a by a small relationship table which stores the note id, the note's owner's id and the type of owner. At the moment, if I'm dealing with a particular client, I can retrieve all their notes using something like the following: SELECT notes.id, notes.content, notes.date FROM notes, clients, notes_relations WHERE notes.id = notes_relations.note_id AND ...

  1. #1

    Default Tricky Join Query

    Hello.

    I have a really tricky sql problem and would appreciate any help I can
    get.

    My database stores clients, jobs, contacts and notes. Each note can
    potentially (but not necessarily) be related to a client, a job and a
    contact. This relationship is maintained by a by a small relationship
    table which stores the note id, the note's owner's id and the type of
    owner.

    At the moment, if I'm dealing with a particular client, I can retrieve
    all their notes using something like the following:

    SELECT notes.id, notes.content, notes.date
    FROM notes, clients, notes_relations

    WHERE notes.id = notes_relations.note_id
    AND notes_relation.fid = clients.id
    AND notes_relations.fid_type = 5 /* some predefined code for clients
    */

    Here's the issue: a note returned from the above could potentially be
    related to a job or a contact and I need to know if that's the case.
    Because I'm using PHP, I can extract the note id from the result and
    find a related job using:

    SELECT jobs.id
    FROM notes, jobs, notes_relations

    WHERE notes.id = __EXTRACTED_NOTE_ID__
    AND notes.id = notes_relations.note_id
    AND notes_relations.fid = jobs.id
    AND notes_relations.fid_type = 10 /* some predefined code for jobs */

    This is definitely achieving the correct result; unfortunately it's
    just taking way too long. The first transaction takes under a second;
    subsequently determining related jobs and contacts adds at least 10
    seconds to processing time.

    Also, I'm sure there's a way using JOIN to extract the required info in
    a single query.

    Can anyone help me?

    battle.chris@gmail.com Guest

  2. #2

    Default Re: Tricky Join Query

    com wrote:
     

    I haven't studied your post in detail but it sounds like a structural
    problem. As I understand it, a note can refer to one or more of the
    following - client,job,contact - and MUST refer to at least one of
    them. If a note can refer to, say, a client in the context of more than
    one contact or job (but not ALL jobs or contacts associated with that
    client) then I suspect you could represent that with a db that looks
    like this (untested):

    clients(client_id*,client)
    jobs(job_id*,job)
    contacts(contact_id*,contact)
    notes(note_id*,note)
    note_relationships(client_id*,job_id*,contact_id*, note_id*)

    Note how the PK of the note_relationships table comprises the PKs of
    ALL the other tables - this allows for 2 or more notes to be applied to
    the same job, client,contact, or (I think) for 1 or more of those
    values to appear as NULL in the relationships table - like this:

    client_id | job_id | contact_id | note_id
    1 NULL NULL 1
    1 1 NULL 2
    2 1 NULL 2
    1 NULL NULL 3

    Anyway, if I'm wrong about using NULL values in a PK, then you just
    need to add another id column to this table, and make that the PK
    instead.

    Now it's easy to select all notes based upon client, contact, job or
    any combination of those. This might not be the most readily scalable
    solution - but it doesn't sound like that's going to be too much of an
    issue.

    strawberry Guest

  3. #3

    Default Re: Tricky Join Query


    strawberry wrote: 
    >
    > I haven't studied your post in detail but it sounds like a structural
    > problem. As I understand it, a note can refer to one or more of the
    > following - client,job,contact - and MUST refer to at least one of
    > them. If a note can refer to, say, a client in the context of more than
    > one contact or job (but not ALL jobs or contacts associated with that
    > client) then I suspect you could represent that with a db that looks
    > like this (untested):
    >
    > clients(client_id*,client)
    > jobs(job_id*,job)
    > contacts(contact_id*,contact)
    > notes(note_id*,note)
    > note_relationships(client_id*,job_id*,contact_id*, note_id*)
    >
    > Note how the PK of the note_relationships table comprises the PKs of
    > ALL the other tables - this allows for 2 or more notes to be applied to
    > the same job, client,contact, or (I think) for 1 or more of those
    > values to appear as NULL in the relationships table - like this:
    >
    > client_id | job_id | contact_id | note_id
    > 1 NULL NULL 1
    > 1 1 NULL 2
    > 2 1 NULL 2
    > 1 NULL NULL 3
    >
    > Anyway, if I'm wrong about using NULL values in a PK, then you just
    > need to add another id column to this table, and make that the PK
    > instead.
    >
    > Now it's easy to select all notes based upon client, contact, job or
    > any combination of those. This might not be the most readily scalable
    > solution - but it doesn't sound like that's going to be too much of an
    > issue.[/ref]

    Thanks for your reply!

    This definitely a potential solution. My only concern is this: the
    scenario I outlined in the original post is a simplified version of the
    real schema in that notes can be related to many other entity types;
    e.g.: interviews, consultants, references, etc. which would mean that
    the notes_relations table would need as many fields as there are
    relationship types. E.g.:

    client_id | job_id | contact_id | interview_id | consultant_id |
    reference_id | .... | note_id

    Is this good database design? Is there a better solution?

    Thanks again.

    battle.chris@gmail.com Guest

  4. #4

    Default Re: Tricky Join Query

    com wrote: 
    >> I haven't studied your post in detail but it sounds like a structural
    >> problem. As I understand it, a note can refer to one or more of the
    >> following - client,job,contact - and MUST refer to at least one of
    >> them. If a note can refer to, say, a client in the context of more than
    >> one contact or job (but not ALL jobs or contacts associated with that
    >> client) then I suspect you could represent that with a db that looks
    >> like this (untested):
    >>
    >> clients(client_id*,client)
    >> jobs(job_id*,job)
    >> contacts(contact_id*,contact)
    >> notes(note_id*,note)
    >> note_relationships(client_id*,job_id*,contact_id*, note_id*)
    >>
    >> Note how the PK of the note_relationships table comprises the PKs of
    >> ALL the other tables - this allows for 2 or more notes to be applied to
    >> the same job, client,contact, or (I think) for 1 or more of those
    >> values to appear as NULL in the relationships table - like this:
    >>
    >> client_id | job_id | contact_id | note_id
    >> 1 NULL NULL 1
    >> 1 1 NULL 2
    >> 2 1 NULL 2
    >> 1 NULL NULL 3
    >>
    >> Anyway, if I'm wrong about using NULL values in a PK, then you just
    >> need to add another id column to this table, and make that the PK
    >> instead.
    >>
    >> Now it's easy to select all notes based upon client, contact, job or
    >> any combination of those. This might not be the most readily scalable
    >> solution - but it doesn't sound like that's going to be too much of an
    >> issue.[/ref]
    >
    > Thanks for your reply!
    >
    > This definitely a potential solution. My only concern is this: the
    > scenario I outlined in the original post is a simplified version of the
    > real schema in that notes can be related to many other entity types;
    > e.g.: interviews, consultants, references, etc. which would mean that
    > the notes_relations table would need as many fields as there are
    > relationship types. E.g.:
    >
    > client_id | job_id | contact_id | interview_id | consultant_id |
    > reference_id | .... | note_id
    >
    > Is this good database design? Is there a better solution?
    >
    > Thanks again.
    >[/ref]

    IMHO, this is a very poor design.

    Rather, maybe 3 columns: NoteId, ReferenceId, ReferenceType

    A NoteID will refer to a specific ReferenceType (i.e. client_id, job_id,
    etc.) with the id of ReferenceId.

    Anytime you have multiple columns with basically the same information,
    especially when many of the columns are NULL.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Tricky Join Query



    On Jan 24, 11:37 am, Jerry Stuckle <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]





    >
    > Rather, maybe 3 columns: NoteId, ReferenceId, ReferenceType
    >
    > A NoteID will refer to a specific ReferenceType (i.e. client_id, job_id,
    > etc.) with the id of ReferenceId.
    >
    > Anytime you have multiple columns with basically the same information,
    > especially when many of the columns are NULL.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    I replied to this post about 12hrs ago so I'm going to assume the post
    isn't going to show up; sorry if it does.

    Thanks for your reply Jerry.

    The schema you've suggested seems exactly the same as the one we
    started out with with different column names; i.e.: NoteId, ReferenceId
    and ReferenceType correlate with note_id, fid and fid_type.

    I think this is the best representation, but it still leaves the
    original problem: I need to be able to retrieve all notes that relate
    to a particular entity of some type (e.g. a Client) and, using the same
    query, retrieve details of other entities related to the retrieved
    notes.

    E.g.
    - The interface renders the notes for CompanyX.
    - One of those notes states 'Job987 was completed today. Invoice
    sent.'
    - Job987 is also related to the note via a notes_relations record, as
    is 'Terry Hatcher', the consultant who undertook the work.
    - Clicking on the Job987 or 'Terry Hatcher' links will refresh the
    browser with that entities' details.

    So the query needs to not only retrieve the notes, but other related
    entities as required.

    Thanks again for your help.

    battle.chris@gmail.com Guest

  6. #6

    Default Re: Tricky Join Query

    On 24 Jan, 23:53, com wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    > isn't going to show up; sorry if it does.
    >
    > Thanks for your reply Jerry.
    >
    > The schema you've suggested seems exactly the same as the one we
    > started out with with different column names; i.e.: NoteId, ReferenceId
    > and ReferenceType correlate with note_id, fid and fid_type.
    >
    > I think this is the best representation, but it still leaves the
    > original problem: I need to be able to retrieve all notes that relate
    > to a particular entity of some type (e.g. a Client) and, using the same
    > query, retrieve details of other entities related to the retrieved
    > notes.
    >
    > E.g.
    > - The interface renders the notes for CompanyX.
    > - One of those notes states 'Job987 was completed today. Invoice
    > sent.'
    > - Job987 is also related to the note via a notes_relations record, as
    > is 'Terry Hatcher', the consultant who undertook the work.
    > - Clicking on the Job987 or 'Terry Hatcher' links will refresh the
    > browser with that entities' details.
    >
    > So the query needs to not only retrieve the notes, but other related
    > entities as required.
    >
    > Thanks again for your help.- Hide quoted text -- Show quoted text -[/ref]
    I have just done a similar thing with user and company tables.

    Could you post the schema (as CREATE TABLE statements), some relevant
    sample data (as INSERT statements)
    and some required results?

    Captain Guest

  7. #7

    Default Re: Tricky Join Query

    com wrote: 

    Why does it have to be the same query? In fact, your description
    indicates it probably should be 2 queries - the first one returning the
    notes, then after clicking, a second query to retrieve the associated data.

    You wouldn't want to retrieve all information for all notes. That could
    be a lot of unnecessary information if there are say 100 notes and the
    user only wants info on one of them.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. query/right join/
    By Coldfusionstudent in forum Coldfusion Database Access
    Replies: 6
    Last Post: November 1st, 05:22 PM
  2. Add another join to a query
    By Conti in forum Coldfusion Database Access
    Replies: 6
    Last Post: March 20th, 06:23 PM
  3. A tricky query problem
    By quiero mas in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 12th, 07:17 AM
  4. Tricky query for me, should be simple for you
    By Tom in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: July 31st, 12:39 PM
  5. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139