Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

sql syntax does not give me the required output

Codes here !

Moderators: macek, egami, gesf

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]
Mazerman
New php-forum User
New php-forum User
 
Posts: 12
Joined: Sun Jan 26, 2003 2:57 pm
Location: Sweden

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...
liquedus
php-forum Active User
php-forum Active User
 
Posts: 266
Joined: Tue Apr 08, 2003 5:18 am
Location: Ottawa, Canada

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....
Mazerman
New php-forum User
New php-forum User
 
Posts: 12
Joined: Sun Jan 26, 2003 2:57 pm
Location: Sweden

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
Flood
New php-forum User
New php-forum User
 
Posts: 23
Joined: Tue Jun 24, 2003 4:49 am

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!!!
Mazerman
New php-forum User
New php-forum User
 
Posts: 12
Joined: Sun Jan 26, 2003 2:57 pm
Location: Sweden


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.