Need help on a select statement using MYSQL ^.^

[Puffer Fish via SQLMonster.com]

Hi,

I want to create a select statement showing -->

John -- happy -- Alice -- Mad -- Sunday
John -- Mad -- Alice -- happy -- Monday

Here is my table structure:

main (table)
main_id int int primary key
person_id_1 int references person(person_id)
mood_id int references mood(mood_id)
person_id_2 int references person(person_id)
mood_id int references mood(mood_id)
day char(10)

person (table)
person_id int primary key
person_name char(15)

mood (table)
mood_id int primary key
mood_type char(15)

I think my table structure setup is bad....thanks if anyone can help!

[Erland Sommarskog]

Puffer Fish via SQLMonster.com (fo***@SQLMonster.com) writes:

I want to create a select statement showing -->

John -- happy -- Alice -- Mad -- Sunday
John -- Mad -- Alice -- happy -- Monday

Here is my table structure:

main (table)
main_id int int primary key
person_id_1 int references person(person_id)
mood_id int references mood(mood_id)
person_id_2 int references person(person_id)
mood_id int references mood(mood_id)
day char(10)

person (table)
person_id int primary key
person_name char(15)

mood (table)
mood_id int primary key
mood_type char(15)

I think my table structure setup is bad....thanks if anyone can help!

First of all, beware that if you stay in this newsgroup, you will
get solutions that will run on Microsoft SQL Server, and that may or
may not run on MySQL.

I can't reall tell whether your table definition is good or not,
because I don't know the business problem you are trying to solve.
Well "business problem" may not be the right world, because this does
not seem like business...

Anyway, this could do:

SELECT p1.person_name, m1.mood_type, p2.person_name, m2.mood_type,
m.day
FROM main m
JOIN person p1 ON m.person_id_1 = p1.person_id
JOIN person p2 ON m.person_id_2 = p2.person_id
JOIN mood m1 ON m.mood_id_1 = m1.mood
JOIN mood m2 ON m.mood_id_2 = m2.mood
ORDER BY CASE m.day
WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
...
END
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Puffer Fish via SQLMonster.com]

Thanks Erland! It works


Erland Sommarskog wrote:

I want to create a select statement showing -->

[quoted text clipped - 20 lines]

I think my table structure setup is bad....thanks if anyone can help!

First of all, beware that if you stay in this newsgroup, you will
get solutions that will run on Microsoft SQL Server, and that may or
may not run on MySQL.

I can't reall tell whether your table definition is good or not,
because I don't know the business problem you are trying to solve.
Well "business problem" may not be the right world, because this does
not seem like business...

Anyway, this could do:

SELECT p1.person_name, m1.mood_type, p2.person_name, m2.mood_type,
m.day
FROM main m
JOIN person p1 ON m.person_id_1 = p1.person_id
JOIN person p2 ON m.person_id_2 = p2.person_id
JOIN mood m1 ON m.mood_id_1 = m1.mood
JOIN mood m2 ON m.mood_id_2 = m2.mood
ORDER BY CASE m.day
WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
...
END

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200508/1