sql syntax does not give me the required output

Codes here !

Moderators: macek, egami, gesf

Mazerman
New php-forum User
New php-forum User
Posts: 12
Joined: Sun Jan 26, 2003 2:57 pm
Location: Sweden

sql syntax does not give me the required output

Postby Mazerman » Wed Jun 18, 2003 11:41 pm

Hi!

I have a many to many relationship between 2 entities; Product and Property. So that a product may have one or many properties and a property may concern one or many products. This make up three tables:
---------------
Product
---------------
prod_id *
title
price
---------------

---------------
Product_Property
---------------
prod_id *
property *
---------------
---------------
Property
---------------
property *
description
---------------

One of the products is a monitor, a monitor has got a couple of properties such as CRT, TFT, 15", 17", 19" etc
My question is how can I make a sql-query that show only the monitors that is of CRT and 17"?

Have tried this statement but it wont work:

Code: Select all

SELECT title, price FROM Product P INNER  JOIN Product_Property PP ON P.prod_id = PP.prod_id INNER  JOIN Property PY ON PP.property = PY.property WHERE PY.property =  'CRT' AND PY.property =  '17"'


Please help me anyone![/img]

liquedus
php-forum Active User
php-forum Active User
Posts: 266
Joined: Tue Apr 08, 2003 5:18 am
Location: Ottawa, Canada
Contact:

Postby liquedus » Thu Jun 19, 2003 4:11 am

PY.property WHERE PY.property = 'CRT' AND PY.property = '17"'

im pretty sure this is what is giving you problems...

i would redesign your tables to include a field such as screen_size and other_property

and you could do with 2 tables instead of 3...

Mazerman
New php-forum User
New php-forum User
Posts: 12
Joined: Sun Jan 26, 2003 2:57 pm
Location: Sweden

Re:liquedus

Postby Mazerman » Thu Jun 19, 2003 6:49 am

I thank you very much for your contribution, but making this redesign is not an option since the Product table stores data for about 20 different product, where each product has its own properties.
Would be thankful if there are anyone else who can help me out....

Flood
New php-forum User
New php-forum User
Posts: 23
Joined: Tue Jun 24, 2003 4:49 am

Postby Flood » Wed Jun 25, 2003 9:02 am

Hello!

First of all, as Liquedus said, I think your database should be designed again from scratch. And if not, you should at least add an identifier for the properties in order to avoid redundancies in fields between product_property and property...

But anyway, here is a solution that works. Perhaps it is not the best one, nor the quickest one during the execution, but it gives good results...


SELECT title, price
FROM Product P, Product_Property PP1, Product_Property PP2
WHERE P.prod_id=PP1.prod_id
AND P.prod_id=PP2.prod_id
AND PP1.property = 'CRT'
AND PP2.property = '17"'

Hope it helps.

/Flood

Mazerman
New php-forum User
New php-forum User
Posts: 12
Joined: Sun Jan 26, 2003 2:57 pm
Location: Sweden

Postby Mazerman » Thu Jul 03, 2003 4:51 pm

I thank you for your advises, I have redesigned the whole database like you said and know it works fine...THNX!!!


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 2 guests