how to format a query

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
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

how to format a query

Post 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
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

Let see now, try this - read comments

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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`
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

with some fiddling, you could get my posted code to do such a thing.
Post Reply