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

From: Jacob Nordgren <jacob_nordgren_at_hotmail.com>
Date: 15 Oct 2001 00:11:41 -0700
Message-ID: <47c6b9be.0110142311.2ebe9451@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 - 02:11:41 CDT