Page 1 of 1

how to format a query

Posted: Wed Jun 09, 2004 12:36 am
by davidklonski
Hello

I was hoping someone could help me with the format of the following query?
I have the following table:

Code: Select all

CREATE TABLE people (
  ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
  First_Name VARCHAR(255) NOT NULL,
  Last_Name VARCHAR(255) NOT NULL,
  # more attributes go here
  Father_ID INT UNSIGNED NOT NULL,
  Mother_ID INT UNSIGNED NOT NULL,
  PRIMARY KEY  (ID)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;
since each person can only have a single father and a single mother, I put their IDs in the same table. The Father_ID attribute behaves like a foreign key to the ID key in the same table (the same goes for the mother).
A missing father is indicated with Father_ID = 0
A missing mother is indicated with Mother_ID = 0

I need to extract the following info:
select all the people who match a certain criteria (for example First_Name is 'John') and for each matching person, also select his father.
For both the people who matched the search and for their fathers I need to get all the attributes (probably using *)

An added bonus would be to return the people sorted with each person followed by his father.

thanks for advance for any help

Let see now, try this - read comments

Posted: Wed Jun 09, 2004 1:02 am
by Calimero
SQL STATEMENT:

SELECT First_Name, Last_Name, Father_first_name(you didn't named this column, and in here list all the columns from these two tables in which peoples and fathers data are stored)
FROM people pe, parents pa (you also didn't named this table, my suggestion)
WHERE First_Name='John' AND pe.Father_ID=pa.ID ( in here I would use First_Name like '%John%' )
ORDER BY First_Name; (this sorts each person, but for father use variable in php and like search engine results page just tell the PHP to join the person and father together when displaying results.


COMMENTS ARE IN (BRACKETS) SO JUST DELETE THEM WHEN YOU COPY/PASTE THE CODE
+ be advised that if you have MySQL inside a PHP use this as qoutation marks ' ' , not these " "

SELECT ( all fileds involved)
FROM (all tables involved - you pull data from)
WHERE (here is your criteria)[big_search][/big_search]

Posted: Wed Jun 09, 2004 1:56 am
by feyd
something I cooked up:

Code: Select all

(
SELECT CONCAT( p.`Last_Name` , ', ', p.`First_Name` ) child, CONCAT( f.`Last_Name` , ', ', f.`First_Name` ) father, CONCAT( m.`Last_Name` , ', ', m.`First_Name` ) mother
FROM `people` p
INNER JOIN `people` f ON p.`Father_ID` = f.`ID` 
INNER JOIN `people` m ON p.`Mother_ID` = m.`ID` 
ORDER BY p.`Father_ID` 
)
UNION (

SELECT CONCAT( p.`Last_Name` , ', ', p.`First_Name` ) child, ''father, CONCAT( m.`Last_Name` , ', ', m.`First_Name` ) mother
FROM `people` p
INNER JOIN `people` m ON p.`Mother_ID` = m.`ID` 
WHERE p.`Father_ID` =0
ORDER BY p.`Last_Name` 
)
UNION (

SELECT CONCAT( p.`Last_Name` , ', ', p.`First_Name` ) child, CONCAT( f.`Last_Name` , ', ', f.`First_Name` ) father, ''mother
FROM `people` p
INNER JOIN `people` f ON p.`Father_ID` = f.`ID` 
WHERE p.`Mother_ID` =0
ORDER BY p.`Father_ID` 
)
UNION (

SELECT CONCAT( p.`Last_Name` , ', ', p.`First_Name` ) child, ''father, ''mother
FROM `people` p
WHERE p.`Father_ID` =0 AND p.`Mother_ID` =0
ORDER BY p.`Last_Name` 
)
ORDER BY `father`,`child`

Posted: Wed Jun 09, 2004 5:52 pm
by davidklonski
Here is an example of data and the results the query should return:

data:

Code: Select all

ID   First_Name     Last_Name    Father_ID    Mother_ID
1     'John'        'Thomas'        2            0
2     'Mark'        'Thomas'        0            80
3     'Rachel'      'Steeler'       4            0
4     'Jeff'        'Steeler'       0            5
Now assume that I want to get all the people who'se first name begins with 'j' and for every person found, also return his father (if one exists (father_id <> 0). Make sure that each person is only returned once (a certain person can be ther father of two distinct people, but he should be returned only once)

So the result of the query will be:

Code: Select all

ID   First_Name     Last_Name    Father_ID    Mother_ID
1     'John'        'Thomas'        2            0
2     'Mark'        'Thomas'        0            80
4     'Jeff'        'Steeler'       0            5
1 and 4 because they match they condition (first name begins with 'j') and 2 because he is the father of 1 (who matched the condition).

The father should appear right after his son in the results (if this is possible).

can anyone show me how this can be acheived in SQL?
I am using MySQL 5.0

thanks in advance

Posted: Wed Jun 09, 2004 6:41 pm
by feyd
with some fiddling, you could get my posted code to do such a thing.