Page 1 of 1

SQL QUERY HELP

Posted: Tue Feb 24, 2004 8:00 am
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.

Posted: Tue Feb 24, 2004 9:05 am
by Illusionist
SELECT * FROM tbl_student ORDER BY stu_dob DESC

Posted: Tue Feb 24, 2004 9:14 am
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.

Posted: Tue Feb 24, 2004 10:00 am
by pickle
SELECT DISTINCT(*) FROM tbl_student LIMIT 2 ORDER BY stu_dob DESC;

Posted: Tue Feb 24, 2004 10:25 am
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.

Posted: Tue Feb 24, 2004 11:59 am
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?

Posted: Tue Feb 24, 2004 12:38 pm
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.

Posted: Wed Feb 25, 2004 4:47 am
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

Posted: Wed Feb 25, 2004 5:17 am
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

Posted: Wed Feb 25, 2004 6:49 am
by gurjit
tried it but it displays all the people.