GROUPING RESULTS

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

GROUPING RESULTS

Post by kendall »

Hello,

I have 2 tables individuals and families...
im trying to create a query that lists all the families and extract the names of the members of the families associated with the family

thus i have created the following query
SELECT family.FAMID, family.Married, individual.FName, individual.MName, individual.LName FROM quesnel_marriages AS family, quesnel_individuals AS individual WHERE individual.INDID = family.Husband OR individual.INDID = family.Wife ORDER BY family.Married, individual.Born ASC
The results of this query come out like this....
1 209 1960-06-24 Vincent Edmund Quesnel
2 209 1960-06-24 Ulusia Joan Henderson De Lyon
Is there any way to group the results into a class field
1 209 1960-06-24 Vincent Edmund Quesnel Ulusia Joan Henderson De Lyon
IDeas are greatly appreciated

Kendall
[/quote]
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

try adding, after WHERE, before ORDER

Code: Select all

GROUP BY family.FAMID, family.Married, individual.FName, individual.MName, individual.LName
(change the order of your preference)

http://www.mysql.com/doc/en/GROUP-BY-Functions.html
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

GROUPING RESULTS

Post by kendall »

JAM,

I have tried your suggestion and i got the same results as before...However look through mysql documentation i found a CONCAT_WS() function so im trying the following query

Code: Select all

SELECT family.FAMID, family.Married, CONCAT_WS(" ",individual.INDID, individual.FName, individual.MName, individual.LName) AS Spouse1, CONCAT_WS(" ",individual.INDID, individual.FName, individual.MName, individual.LName) AS Spouse2 FROM quesnel_marriages AS family, quesnel_individuals AS individual GROUP BY family.FAMID
the result i get is
241 1992-03-16 9 Vincent Edmund Quesnel 9 Vincent Edmund Quesnel
which is more leaning to the result 'format' that i want but its duplicateing the entry.....hmmmmm

what i need to do is..
group by family ID
class the husband and wife

any ideas....im thinking....im thinking....

Kendall
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Hard when you cant test... =/
SELECT DISTINCT ... ?
Tho, if you have two Edmund (thats really are two Edmund's) You might have a problem...
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

GROUPING RESULTS CONCAT_WS()

Post by kendall »

JAM,

No there isnt 2 edmunds....at least not yet!! I don't see it being a problem as im identifying them by FAMID groupings....

Now note in my squery string i have CONCAT_WS() AS Spouse1 twice...I was using this to try to group the results by FAMID Spouse1 (husband) Spouse2 (wife) MArried date....Can I have expression in the CONCAT_WS? Cause i just realise i have to detect the males from females....
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

GROUPING RESULTS CONCAT_WS()

Post by kendall »

JAM,

ok i got it here is the following query i used

Code: Select all

SELECT DISTINCT family.FAMID, family.Married, CONCAT_WS(" ",husband.INDID, husband.FName, husband.MName, husband.LName) AS Spouse1, CONCAT_WS(" ",wife.INDID, wife.FName, wife.MName, wife.LName) AS Spouse2 FROM quesnel_marriages AS family, quesnel_individuals AS husband, quesnel_individuals AS wife WHERE husband.INDID = family.Husband AND wife.INDID = family.Wife GROUP BY family.FAMID
which give me the desired results
235 1990-04-23 2248 Valentine Marc Arneaud 2249 Sandra Mary Alexander
which is the family id, the year they were married, the husbands id and name, the wifes id and name

Was there an easier way to do this....just asking

Kendall
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Re: GROUPING RESULTS CONCAT_WS()

Post by JAM »

kendall wrote:Was there an easier way to do this....just asking
I'm sitting here with a rather stupid "uhmmmm..." look on my face. Im wondering if this cant be done by rearranging the join's abit.

But as you stated, the desired result is achieved, so...

If you really want to continue, by all means dump the table structures and a couple of lines of data for me. If you bother that is...
If not, glad you sorted it out ;)
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

GROUPING RESULTS CONCAT_WS()

Post by kendall »

JAM,

sure man...just wanna get query optimised....

heres the structure

---------quesnel_individuals---------
INDID
FName
MName
LName
SEX
BORN
DIED
-------------------------------------------
-------------quesnel_marriages--------
FAMID
Husband
Wife
Married
Divorced
-------------------------------------------

not much to the code but

Code: Select all

if($index == 'family'){
	 $query = 'SELECT DISTINCT family.FAMID, family.Married, CONCAT_WS(" ",husband.FName, husband.MName, husband.LName) AS Spouse1, CONCAT_WS(" ",wife.FName, wife.MName, wife.LName) AS Spouse2 FROM quesnel_marriages AS family, quesnel_individuals AS husband, quesnel_individuals AS wife WHERE husband.INDID = family.Husband AND wife.INDID = family.Wife GROUP BY family.FAMID '; 
	$FAMILIES = QueryDatabase(&$database,$query,$Connection);
	$families = mysql_num_rows($FAMILIES);
	$Family = mysql_fetch_assoc($FAMILIES);
	// get individuals of the family
	echo 'total of - '.$families.'<br>';
	do&#123;
		// print family
		echo '<a href="display_family_tree.php?ID='.$Family&#1111;'FAMID'].'" target="main">';
		echo $Family&#1111;'Spouse1'].' & '.$Family&#1111;'Spouse2'].' on '.$Family&#1111;'Married'];
		echo '</a><br>';
	&#125;while($Family = mysql_fetch_assoc($FAMILIES));
the URL is http://www.quesnels.com/draft/

is just a mock up really im just trying to develop the components i may need before i really put in into workings.....this is hard...and im an ametuer

rite now im working on getting it to display a graphical family tree using DIVS...going good so far but not keen


Kendall
Post Reply