List:General Discussion« Previous MessageNext Message »
From:Jason Thiesse Date:November 1 2002 5:15pm
Subject:Re: Select statement
View as plain text  
" want to see something like this:
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 <johnec@stripped>
To: mysql@stripped
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 <mysql-thread123795@stripped>
To unsubscribe, e-mail 
<mysql-unsubscribe-jasonthiesse=hotmail.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


_________________________________________________________________
http://resourcecenter.msn.com/access/plans/2monthsfree.asp

Thread
Select statementJohn Chang1 Nov
  • Re: Select statementMichael T. Babcock1 Nov
Re: Select statementJason Thiesse1 Nov