Query problem with related data in the same table

indego · #1 (**permalink**) 01-22-04, 14:16

indego · #2 (**permalink**) 01-27-04, 05:30

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

r937 · #3 (**permalink**) 01-27-04, 06:20

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

indego · #4 (**permalink**) 01-28-04, 08:11

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'

r937 · #5 (**permalink**) 01-28-04, 08:33

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

indego · #6 (**permalink**) 01-28-04, 11:27

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

Internet Services

Web Development

Digital Marketing

Consumer Tech