Re: How do I order by some of the rows in a column. Not all.

On 15 Oct 2001 00:11:41 -0700, jacob_nordgren_at_hotmail.com (Jacob Nordgren) wrote:

>Hi again!
>
>Sorry about the missing data. I thought I was making it easier by not
>including all data.
>
>Before I give you my datamodel I would like to repeat my question.
>What I would like to do is to search for products in a certain
>productgroup (eg books). The result should only be ordered by title.
>It's the ordered by part that I need help with.
>
>Here is the data:
>
>CREATE TABLE PRODUCTGROUP (
> ID NUMBER (38) NOT NULL,
> NAME VARCHAR2 (50) NOT NULL,
> CONSTRAINT PRODUCTGROUP_PK
> PRIMARY KEY ( ID ) ) ;
>
>ID NAME
>
>1 Books
>2 Music
>
>CREATE TABLE PRODUCT (
> ID VARCHAR2 (50) NOT NULL,
> PRODUCTGROUP_ID NUMBER (38) NOT NULL,
> CONSTRAINT PRODUCT_PK
> PRIMARY KEY ( ID ) ) ;
>
>ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCTGROUPPRODUCT
> FOREIGN KEY (PRODUCTGROUP_ID)
> REFERENCES PRODUCTGROUP (ID) ;
>
>CREATE INDEX PRODUCTGROUPPRODUCT ON
> PRODUCT(PRODUCTGROUP_ID) ;
>
>ID PRODUCTGROUP_ID
>
>1840253053 1
>1476546765 2
>
>CREATE TABLE PRODUCT_PROPERTIES (
> PRODUCT_ID VARCHAR2 (50) NOT NULL,
> PROPERTY_ID NUMBER (38) NOT NULL,
> PROPERTY_VALUE VARCHAR2 (255) NOT NULL,
> CONSTRAINT PRODUCT_PROPERTIES_PK
> PRIMARY KEY ( PRODUCT_ID, PROPERTY_ID ) ) ;
>
>ALTER TABLE PRODUCT_PROPERTIES ADD CONSTRAINT
>PRODUCTPRODUCT_PROPERTIES
> FOREIGN KEY (PRODUCT_ID)
> REFERENCES PRODUCT (ID) ;
>
>ALTER TABLE PRODUCT_PROPERTIES ADD CONSTRAINT
>PROPERTYPRODUCT_PROPERTIES
> FOREIGN KEY (PROPERTY_ID)
> REFERENCES PROPERTIES (ID) ;
>
>CREATE INDEX PRODUCT_PROPERTIES_IDX1 ON
> PRODUCT_PROPERTIES(PRODUCT_ID)
>;
>
>CREATE INDEX PROPERTYPRODUCT_PROPERTIES ON
> PRODUCT_PROPERTIES(PROPERTY_ID)
>;
>
>PRODUCT_ID PROPERTY_ID PROPERTY_VALUE
>
>1840253053 1 Shops and Shopping on the Internet
>1840253053 2 Lambert, Kathy
>1840253053 3 1840253053
>1840253053 4 hardcover
>1476546765 5 Live At The Star Club In Hamburg 62
>1476546765 6 Beatles
>1476546765 8 cd
>1476546765 16 WLTS
>1476546765 17 3/26/01
>
>CREATE TABLE PROPERTIES (
> ID NUMBER (38) NOT NULL,
> NAME VARCHAR2 (50) NOT NULL,
> PRODUCTGROUP_ID NUMBER (38) NOT NULL,
> CONSTRAINT PROPERTIES_PK
> PRIMARY KEY ( ID ) ) ;
>
>ALTER TABLE PROPERTIES ADD CONSTRAINT PRODUCTGROUPPROPERTY
> FOREIGN KEY (PRODUCTGROUP_ID)
> REFERENCES PRODUCTGROUP (CATEGORYID) ;
>
>CREATE INDEX PRODUCTGROUPPROPERTY ON
> PROPERTIES(PRODUCTGROUP_ID)
>;
>
>ID NAME PRODUCTGROUP_ID
>
>1 booktitle 1
>2 author 1
>3 isbn 1
>4 format 1
>5 albumtitle 2
>6 artist 2
>8 format 2
>16 label 2
>17 release_date 2
>
>I hope this datamodel is correct. I've worked hard to make it very
>general so it is easy to include new products. Today a book can not
>have two authors but I can live with that.
>
>I use Java as a programminglanguage where I have many checks on
>correct data and so on. I've tried to move as much logic as possible
>to the programming code.
>
>Thank you for your help,
>
>Jacob
>
>
>
>
>
>
>
>
>marc_at_marcblum.de (Marc Blum) wrote in message news:<3bc9ddaf.2602341_at_news.online.de>...
>> On 12 Oct 2001 07:22:33 -0700, jacob_nordgren_at_hotmail.com (Jacob
>> Nordgren) wrote:
>>
>> >Hi,
>> >
>> >I have the following tables
>> >
>> >PRODUCT_GROUP
>> >ID (INT)* 'Example 1
>> >NAME (VARCHAR) 'Example "Books"
>> >
>> >PRODUCT
>> >ID (INT)* 'Example: 1
>> >PRODUCTGROUPID (INT) 'Example: 1
>> >
>> >PROPERTY
>> >ID (INT)* 'Example 1
>> >NAME (VARCHAR) 'Example "Title"
>> >
>> >PRODUCT_PROPERTY
>> >PRODUCTID (INT)* 'Example 1
>> >PROPERTYID (INT)* 'Example 1
>> >VALUE (VARCHAR)* 'Example "Learn SQL in 24 hours"
>> >
>> >If I want to find books with titles like "SQL" I use the following query:
>> >
>> >SELECT DISTINCT P.ID AS ID
>> >FROM PRODUCT P, PRODUCT_PROPERTY PP
>> >WHERE P.ID = PP.PRODUCTID
>> >AND P.PRODUCTGROUPID = 1
>> >AND PP.PROPERTYID = 1 AND UPPER(PP.VALUE) LIKE '%SQL%'
>> >
>> >My question is: How can I sort the result by the property "title".
>> >
>> >Thanks for your help,
>> >
>> >Jacob
>>
>> I'm nor sure why you are using distinct, I would think that each
>> "book" has only one property "title", therefore I comment out the
>> DISTINCT:
>>
>> SELECT /* DISTINCT */
>> P.ID AS ID,
>> PP.VALUE as TITLE
>> FROM PRODUCT P,
>> PRODUCT_PROPERTY PP
>> WHERE P.ID = PP.PRODUCTID
>> AND P.PRODUCTGROUPID = 1 --means Books
>> AND PP.PROPERTYID = 1 -- means Title
>> AND UPPER(PP.VALUE) LIKE '%SQL%'
>> ORDER BY 2;
>>
>>
>> Marc Blum
>> mailto:marc_at_marcblum.de
>> http://www.marcblum.de

So, does that one doesn't work for you?

regarding the property "Author" I would suggest to see "Author" as a stringfield with values like:

"Marc Blum"
"Marc Blum, Jacob Nordgren"
"My tiny little Organisation"

You get the idea? You don't have to normalize "author", because it's contents is arbitrary. I don't see any entity "Author", it's just a string.

(Hope not to get to much flames about that...;-)   

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon Oct 15 2001 - 05:32:56 CDT