Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1074573953.988336_at_yasure>...
> Prince Of Thieves wrote:
> > I have the following SQL:
> >
> > SELECT DISTINCT STYLE.STYLEIDX, STYLE.STYLECODE, STYLE.NAME, STYLE.DESCRIP
> > FROM STYLE
> > WHERE STYLE.REF5 = 2054
> > AND STYLE.ACTIVE = 1
> > ORDER BY 2;
> >
> > The 2054 value comes from another table, called REFCODE, which looks like
> > this:
> >
> > RGIDX RCIDX CODE NAME RPCIDX ACTIVE
> > 1046 1975 NHL Nhl 348 1903 1
> > 1046 1936 NON LICENSED Non Licensed 1914 1
> > 1046 2054 NON-LICENSED Non-Licensed 1914 1
> >
> >
> > The SQL to get the above is:
> >
> > SELECT DISTINCT *
> > FROM REFCODE
> > WHERE RGIDX = 1046 AND ACTIVE = 1
> > ORDER BY 1;
> >
> > Instead of using "WHERE STYLE.REF5 = 2054" in the top-most SQL, I want to
> > search by the CODE, ie. "NON-LICENSED". So I changed the SQL to:
> >
> > SELECT DISTINCT STYLE.STYLEIDX, STYLE.STYLECODE, STYLE.NAME, STYLE.DESCRIP
> > FROM STYLE, STYLES, STYLEC, REFCODE
> > WHERE STYLE.REF5 = (SELECT RCIDX FROM REFCODE WHERE RGIDX = 1046 AND
> > REFCODE.CODE = 'NON-LICENSED')
> > AND STYLE.ACTIVE = 1
> > ORDER BY 2;
> >
> > Note that "(SELECT RCIDX FROM REFCODE WHERE RGIDX = 1046 AND REFCODE.CODE =
> > 'NON-LICENSED')
> > " returns 2054 only.
> >
> > The problem is that the query now seems to "hang". The original (top-most)
> > query runs instantly). Have I done this correctly?
>
> Even if it returns only one record it is still bad programming. What
> you want is:
>
> SELECT DISTINCT ...
> FROM
> WHERE STYKE,REF5 IN (
> SELECT ...
> FROM ...
> WHERE ...)
> AND ...
> ORDER BY ...
>
> You should get rid of every instance of your hard coding and use
> variables. What you are writing is not unscalable.
>
> But the biggest problem you have is that you seem to be trying to
> master the Cartesian Join: It is not an art form.
>
> You have no joins defining the relationships between STYLE, STYLES,
> STYLEC, and REFCODE.
>
> It isn't hanging. It is just going to take forever to return the
> Cartesian product of this ghastly affair.
And the first clue that something is amiss is in your very first query:
SELECT DISTINCT ...
Fix that and you might make progress.
Received on Thu Jan 22 2004 - 12:47:24 CST
