SQL QUERY HELP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

SQL QUERY HELP

Post by gurjit »

I have 2 tables.

1. student
2. student_siblings

tbl_student(stuid,stu_name,stu_dob)
tbl_student_sibling(ssid, frn_stuid, frn_ss_stuid)

In tbl_student_sibling i keep all sibling realtionships. so for example

in tbl_student i have the following data:


1 fred 15/12/1977
2 john 02/02/1980
3 alex 05/05/1970


in tbl_student_sibling i have the realtionships
1 1
1 2
2 1
2 2
3 3

the above shows that fred and john are related.

how can i pull all oldest students (stuid) from tbl_student and not have any of the siblings showing?

In the above data i want stuid 1 and 3 pulled from the tbl_student table.
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

SELECT * FROM tbl_student ORDER BY stu_dob DESC
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

this will show all the students, i want to show unique oldest students even if they have older brothers and sisters in the tbl_student_sibling table.

look at my example and what i want to pull out.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

SELECT DISTINCT(*) FROM tbl_student LIMIT 2 ORDER BY stu_dob DESC;
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

i want to produce a list of students, i'm sure you have to make a realtionship with the two tables. this will only give me a result of two siblings. what if i had 100 records and i did'nt know which person had brothers or sisters and i wanted to display all the oldest of the 100 people leaving out the younger brothers and sisters.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Well, if you have 100 records, then from what I can see, all people will be in those records - siblings included. I'm not sure what you mean by leaving out younger siblings. Do you want the oldest people who aren't related to anyone? Or do you just want the oldest people period?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
eletrium
Forum Commoner
Posts: 34
Joined: Tue Feb 10, 2004 3:38 pm

Post by eletrium »

He wants all students who have siblings, but are the eldest sibling...

You need a column in your sibling or student Table to denote a relationship or family ID.

tbl_student_sibling(ssid, frn_stuid, frn_ss_stuid, family_id)

Data: (man, try to be somewhat descriptive, not make us use the force... clarity is a good thing..)

column1 = whatever, column 2 = whatever, column 3 = family_ID
1 1 1
1 2 1
2 1 2
2 2 2
3 3 0

Select * from tbl_student_sibling where family_id > 0

will get you a result set of all students who have siblings.

Query that result set to get unique siblings... I am not caffeinated yet, so I'll post back with a full out solution maybe... but that is the basic concept of how you do it.

<span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>, was previewing this when I realized a massive flaw in the data model. You are posting data to the "sibling" data that has absolutely nothing to do with siblings. Your sibling table should only be a storage space for data about those with siblings. Delete that last record. It is extraneous.
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

i have already created the tables and done alot of scripting, so changing the table structure would be a big,big job. plus why use family ID. i can pull any student and find out which family they belong to with this structure.

i want to list all the oldest people including people who do not have siblings related to them thats why you need a relationship in the tbl_student_sibling table for a person to be related to them selves, its like a matrix.

is there any possible way with out doing if statements in php to pull the oldest person from a sibling realtionship (who have brothers and sisters) or an individual person who has no sibling realtion?

e.g.
john is the oldest and has one brother called peter.
frank has no brothers or sisters.
so my list at the end should have

john
frank

my table structures will have the following realtionships:

tbl_student:

1 john 15/12/1977
2 peter 02/02/1980
3 frank 12/08/1977

tbl_sibling_student

1 1 1
2 2 2
3 1 2
4 2 1
5 3 3
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

UNTESTED! ... but something like this should work on a mysql database, and I assume on most others, I think its pretty standard SQL

Code: Select all

SELECT COUNT(*) AS siblings, main.stu_name, main.stu_dob, sibling.stu_name, sibling.stu_dob FROM tbl_student AS main
LEFT OUTER JOIN tbl_student_sibling ON frn_stuid=main.stuid
LEFT OUTER JOIN tbl_student AS sibling ON frn_ss_stuid=sibling.stuid
WHERE main.stu_dob > sibling.stu_dob
OR siblings = 1
GROUP BY main.stuid
ORDER BY sibling.stu_dob DESC
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

tried it but it displays all the people.
Post Reply