Can't you say:
SELECT vhs.*, p.price, s.studio, s.ID as sID, s.studioID
FROM vhs
JOIN pricing p on p.priceID=vhs.price
JOIN studios s on s.id=vhs.studio
JOIN performersRel pr on pr.videoID=vhs.id
WHERE pr.performerID = 1627
I could be wrong, I don't have a dataset to test against, this is just off
the top of my head.
Jason Powers
Fig Leaf Software
202-797-5440
-----Original Message-----
From: Stuart Duncan [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 17, 2000 4:17 PM
To: CF-Talk
Subject: Speeding up a multi table query
I was wondering if anyone knows of a way to speed up this query? If "Joins"
would be better, and how? That sorta thing.
The actors, studios and prices are all in separate tables. "vhs" "pricing"
"studios" and "performersRel"
There has to be a faster way to get 22 records back.
resultsbyperformer (Records=22, Time=328ms)
SQL =
select vhs.*, p.price, s.studio, s.ID as sID, s.studioID
from vhs,pricing p,studios s
where p.priceID = vhs.price
and s.ID = vhs.studio
and vhs.ID in (select videoID from performersRel where performerID = 1627)
Any help would be greatly appreciated.
Stuart Duncan
MaracasMedia Inc.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
