Need help with SQL query - need to get two distinct values 
Author Message
 Need help with SQL query - need to get two distinct values

Hello everyone and thanks for stopping by!

Here's my problem:

Using MS Siteserver Commerce 3.0 and SQL Server 7

I have the following table:

TABLE [cat] (
  [id_cat] [varchar] (4) NOT NULL ,
  [name] [varchar] (30) NULL ,
  [image] [varchar] (8) NULL ,
  [order] [tinyint] NULL
)

The table contains a list of categories and subcategories. The original
developer decided to store the values using the following method:

Main categories have an id_cat that looks like this: 1A00, 1B00, 1C00,
etc... (i.e., the last two character of the category id are '00').

On the other hand, subcategories are stored using this method: Subcategories
for main category 1A00 have id_cats that look like this: 1A01, 1A02, 1A03,
etc... (i.e., they share the first two characters with their parent category
and increment the last two characters).

Now, what I would like to do is this:

Two arguments, idCat and idSubcat are passed to the webpage as query string
values. I'd like to obtain the category name and the subcategory name from
the database as fast as possible. I can do that now with two separate SQL
statements. I'm wondering if it can be done in just one and whether it would
be faster. This is a high access page.

Thanks in advance for any help you can offer and for taking the time to read
this.

Best Regards,

EP.



Mon, 19 Apr 2004 02:10:29 GMT
 Need help with SQL query - need to get two distinct values

I'm not sure about the ins and outs of Commerce Server 3.0 but in SQL you'd do the following:

select 'Main Category'=catmain.name, 'Sub Category'=subcat.name
        from cat catmain, cat subcat

and if you wanted to get the main category from the sub category:

select 'Main Category'=catmain.name, 'Sub Category'=subcat.name
        from cat catmain, cat subcat

the performance of the first query should work out faster than two separate queries, but the second query may be slower

Cheers,
Kevin.

-----------------------------------------------
Visit  http://www.berryware.com/ for SQLMatcher
The ultimate database schema comparison utility

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 19 Apr 2004 03:35:32 GMT
 Need help with SQL query - need to get two distinct values
Wow... thanks a lot Kevin. That first query is really cool.

For the second one, wouldn't it be faster if I assign the value of

idCat = left(idSubcat,2) & "00"

Thanks and regards,

EP.



Mon, 19 Apr 2004 04:04:08 GMT
 Need help with SQL query - need to get two distinct values
Actually, I think the SQL is likely to be faster, but it doesn't really matter that much...  the I/O that SQL has to do to get the results is probably more than the cycles wasted for a few string manipulation operations. ;-)

-----------------------------------------------
Visit  http://www.berryware.com/ for SQLMatcher
The ultimate database schema comparison utility

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 19 Apr 2004 04:19:34 GMT
 Need help with SQL query - need to get two distinct values
Would it be easier to put the categories and sub-categories in the same table, since you are only going two levels down?

CREATE TABLE Categories
(cat_id CHAR(4) NOT NULL PRIMARY KEY
        CHECK (cat_id LIKE '[0-9][A-Z][0-9][0-9]'),
 cat_name VARCHAR(30) NOT NULL
       DEFAULT 'not available',
 sub_cat_name VARCHAR(30) NOT NULL
       DEFAULT ' ',
 image VARCHAR(8) NOT NULL
       DEFAULT 'not available',
 display_order INTEGER NOT NULL DEFAULT 0);

I am making some assumptions about how you have modeled the data.  But you can put data like this into the table:

('1A00', 'Main cat', ' ', 'foobar#1', 1)
('1A01', 'Main cat', 'first guy', 'foobar#2', 2)

Your query would assemble the full category id from two strings and go directly to it.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 19 Apr 2004 08:59:32 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Help need to split comma separated string into distinct values and count for each

2. need help getting a sql query embedde in java to work

3. Need help with an SQL-Statement (need the most current date-value)

4. query using distinct - need help

5. getting distinct values from one table ordered by value from another table

6. Need help with query that needs Dynamic SQL, variables, and Temporary tables

7. need help w/query on two tables

8. Need help with two Paradox 5.0 queries

9. NEED SQL HELP PLEASE, SELECT DISTINCT QUESTION

10. SQL sub query help; getting several MAX() values

11. Need DISTINCT query that returns all fields

12. Need help Access queries ===> SQL queries


 
Powered by phpBB® Forum Software