Re: How do I order by some of the rows in a column. Not all.
Date: 15 Oct 2001 00:11:41 -0700
Message-ID: <47c6b9be.0110142311.2ebe9451_at_posting.google.com>
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
Received on Mon Oct 15 2001 - 09:11:41 CEST