Re: Help with SQL

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 19 Jan 2004 20:47:01 -0800
Message-ID: <1074573953.988336@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.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Jan 19 2004 - 22:47:01 CST