Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Question Unanswered: Query problem with related data in the same table

    Hi All,

    I have developed a database for a independent educational course.

    As part of the specification they wanted all the students to have inderpendent records in the system. OK, also often there is a relationship between the students (Married Couples / Partners living together, etc). This should be recorded and preferably only one copy of the address for both people.

    I implemented this with a 'couple' flag to indicate that there is a related party and a 'couplePersonID' as the key of the other person in the same table. This works fine for most of time but I have hit a snag...

    I set-up MyODBC to link into the database with MS's Query program so that the data could be accessed with Excel. Works fine. In writing a query to try to pull in the data to do a mail-merge in Word my SQL skills ran dry.

    The output should read:-
    Mr and Mrs Smith
    123 Park Road
    or
    Mr Smith and Mrs Jones
    123 Park Road
    or
    Mr Smith
    123 Park Road

    How can I go about to query this?

    Thanks for any help on this.

    Indego

    Table extract:-
    mysql> describe person;
    +----------------+------------------+------+-----+------------------------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------+------------------+------+-----+------------------------+----------------+
    | personID | int(10) unsigned | | PRI | NULL | auto_increment |
    | title | varchar(10) | YES | | NULL | |
    | forename | varchar(25) | YES | MUL | NULL | |
    | surname | varchar(25) | YES | MUL | NULL | |
    | address | tinytext | YES | | NULL | |
    | postcode | varchar(10) | YES | | NULL | |
    | couple | tinyint(1) | YES | | 0 | |
    | couplePersonID | int(10) unsigned | YES | | NULL | |
    +----------------+------------------+------+-----+------------------------+----------------+

  2. #2
    Join Date
    Jan 2004
    Posts
    8
    I have been racking my brain and reading all the SQL docs that I can find. Still no answer.

    The only solution that I can come up with is to write another PHP script to process all the data and output it to a new table in the database. This script will then have to be run before any mail-merge is performed, or the data might be dirty.

    This is not ideal, and with the lack of stored procedures (which could do it internally??) in MySQL there is no way to trigger this without running this function as part of data entry and modification scripts, adding overhead and delays to the interface responce time.

    Oh well, at least I can be glad that only a maximum of 2 people will be entering data at a time, at the moment.

    Any other better ideas?

    Indego

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is a simple solution in plain sql but it depends on whether both people that are related have each other's couplePersonID

    let me describe two queries

    the first gets all people where couplePersonID is null (by the way, you don't need the flag)

    the second uses an inner join to link couples, such that only the person with the lower couplePersonID is returned, the other ignored

    simple, eh?

    and in mysql 4+ you can do both in one query with UNION ALL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2004
    Posts
    8
    OK, you might need to spell this out with a bit more SQL.

    Firest to answer your question.
    Yes, both of the person records have the others personID

    Second, I use the 'couple' flag as an entry option when entering a new student or later when, say 3 years later the partner does the course, to flag 'unresolved' couples so that they can then be matched with the partner. So I can search on "SELECT * FROM person where couple AND couplePersonID = NULL" - List of all unresolved couples. These are then partnered off, yes I could probably just write a '-1' into the field too...

    OK, could you give me some sample SQL to work with, I need to read more about JOIN type and the UNION operation. My SQL skills are a bit thin.

    Thanks

    Indego

    --EDIT--
    Just had a fiddle with the INNER JOIN and MySQL keeps complaining:-
    select forename from person inner join person ON personID = couplePersonID;
    ERROR 1066: Not unique table/alias: 'person'
    Last edited by indego; 01-28-04 at 09:29.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select concat_ws(' ',title,forename,surname) as name
         , address
      from person
     where couplepersonID is null
    union all   
    select case when p1.surname = p2.surname
                then concat_ws(' '
                      . concat_ws(' ',p1.title,p1.forename)
                      , 'and'
                      , concat_ws(' ',p2.title,p2.forename,p2.surname)
                              )
                else concat_ws(' '
                      . concat_ws(' ',p1.title,p1.forename,p1.surname)
                      , 'and'
                      , concat_ws(' ',p2.title,p2.forename,p2.surname)
                              )
                end as name 
         , p1.address
      from person p1
    inner
      join person p2
        on p1.couplePersonID = p2.personID
       and p1.personID < p2.personID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Posts
    8
    WOW!!!!!


    That is very cool.

    I will go in humble steps knowing that SQL was far more powerful than I knew.

    I have not read any tutorial or book on database developmet that has covered that level of SQL.

    Thank you very much!

    Indego

Posting Permissions

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