mysql selects for guru.

mouloud2001

Example : I have a Person table(id, name, normal_clothe_id, sunday_clothe_id) and Clothe table (id, name)
So, my person has two link to the Clothe table. Is it possible to make a one-row-result select which give me the person name, and the name of normal & sunday clothes ?

If mysql can't, is there a database which allows this ?

Thx.

PS : more difficult : a unique Person table (id, name, mother_id, father_id)
A one-row-result select which give me the person name, and his mother and father name, knowing there is one record for each in the db ?

mouloud2001

Just an error in my email. This one is good.

Anon

The way you can obtain this is (using person table)

select p1.name,p2.name,p3.name
from persons p1,persons p2, persons p3
where p1.id=1
and p2.id=p1.father_id
and p3.id=p1.mother_id
;

I don´t know if this is correct in Mysql but in Postgres runs ok.

Bye.

yurko

select p.name, nc.name, sc.name from person p, clothe nc, clothe sc wher
e p.normal_clothe_id = nc.id and p.sunday_clothe_id = sc.id order by p.name;

here what is in Person table:

here what is in Clothe:

+----+-----------+
| id | name |
+----+-----------+
| 1 | clothe #1 |
| 2 | clothe #2 |
| 3 | clothe #3 |
+----+-----------+

Next time do your homework yourself 🙂))
For father mother is kinda same stuf.

yurko

yeps this will work in MySQL too.

Anon

select
id
,name
,b.normal
,c.sunday
from
Person
,(select name as normal from Clothe where Person.normal_clothe_id = id) b
,(select name as normal from Clothe where Person.sunday_clothe_id = id) c
where
person_id = Person.id

This should give you a 1 single row result back.

You could even make it more simple still:
select
id
,name
,b.name
,c.name
from
Person
,Clothes b
,Clothes c
where
Person_id = Person.id
and Person.normal_clothe_id = b.id
and Person.sunday_clothe_id = c.id

MySQL is perfectly capable of parsing this kind of statements 🙂

HTH,
Steven