I SPY Eddie Murphy, Kelly Robinson, Owen Wilson
OR
I SPY Eddie Murphy
Kelly Robinson
Owen Wilson"
-- Not sure if you can return the column value of title only once, but when you are displaying the field you can hide it then.
"Here is the select statement I tried but doesn't work.
SELECT TitleName, ActorName
FROM Titles
left JOIN ActorTitle ON Titles.TitleID = ActorTitle.TitleID
left JOIN Actors ON Titles.ActorID =Actors. ActorID
WHERE TitleName LIKE '%colname%'
ORDER BY TitleName ASC"
Try:
SELECT A.TitleName, C.ActorName
FROM Titles A, ActorTitle B, Actors C
Where
A.TitleID=B.TitleID
AND B.ActorID=C.ActorID
AND A.TitleName LIKE '%I Spy%'
ORDER BY A.TitleName ASC
I believe all those left joins will just slow you down. Hope this helps.
Jason
---------------------------------
Bond: "That gun, looks more fitting for a woman."
Largo: "Do you know much about guns Mr. Bond?"
Bond: "No, I know a little about women."
-- Thunderball
----Original Message Follows----
From: John Chang <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: Select statement
Date: Fri, 01 Nov 2002 12:02:36 -0500
1) What is the correct select statement to use in MYSQL so that it only shows the TitleName once and all the stars in a video. I have a search button for Titles and when it gets submitted it shows it not the way I want it.
So if there is a Video with TitleName=I Spy and the stars are Eddie Murphy, Kelly Robinson, Owen Wilson
In the output table I see:
I Spy Eddie Murphy
I Spy Kelly Robinson
I Spy Owen Wilson
I want to see something like this:
I SPY Eddie Murphy, Kelly Robinson, Owen Wilson
OR
I SPY Eddie Murphy
Kelly Robinson
Owen Wilson
Here is the select statement I tried but doesn't work.
SELECT TitleName, ActorName
FROM Titles
left JOIN ActorTitle ON Titles.TitleID = ActorTitle.TitleID
left JOIN Actors ON Titles.ActorID =Actors. ActorID
WHERE TitleName LIKE '%colname%'
ORDER BY TitleName ASC
Studios (StudioName, StudioID) Genres (GenreName, GenreID) Titles (TitleName, Details, StudioID, GenreID, BitRateID, TitleID) Actors (ActorName, ActorID) ActorTitle (TitleID, ActorID) TitleGenres (TitleID, GenreID)
2) Is there a way to optimize my tables for this kind of searches?
Thank you.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
_________________________________________________________________
Unlimited Internet access for only $21.95/month. Try MSN! http://resourcecenter.msn.com/access/plans/2monthsfree.asp
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
