Member Avatar for Virangya

Hello,
I have two tables product table and accessory table. also i have a order table.
order table have either product id or an accessory id. those are two nullable fields. now i want to choose from product or from accessory table, either product name or accessory name. can i use a join and use OR like bellow?
anyway it doesn't work...
please can someone give me a solution?

$sql="SELECT product.product_name,accessory.accessory_name FROM order,product,accessory WHERE order.product_id=product.product_id OR order.accessory_id=accessory.accessory_id";

:-/

  • 2 Contributors
  • 4 Replies
  • 92 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by tesuji
Member Avatar for tesuji

hi Virangya

glad to meeting you again. Do you already solve your other problem?

Well, what you want to do with boolean OR does not function for it combines the wrong rows from the three tables, which is kind of cross product, what you might have already noticed.

Such an unfavorable sql construct originates from poor table design. If you have the chance to make a redesign of your tables, you will eliminate this and for sure further problems for the future.

On the other hand, php is nice programming language which allow almost every patch of poor-designed tables/ data models. Possibly the problem may also be solve by means of case function in sql statement what I will have to check.

-- tesu

Edited by tesuji because: n/a
Member Avatar for tesuji

Well, possibly is this idea too simple:

SELECT product.product_name FROM orders, product WHERE orders.product_id=product.product_id;
UNION
SELECT accessory.accessory_name FROM orders, accessory WHERE orders.accessory_id=accessory.accessory_id;

I have got the feeling that there could be a relationship between products and accessory since the wording "accessory" suggest such an assumption.

So the senario could be: A customer purchase a certain product and some optional accessories which are associated with this product. Other Products have other associated accessories. A buyer of a certain product is free in whether he purchase zero, some or all accessories.

If so, you should urgently revise your data model for table order can't achieve the requirements of the outlined senario.

Hint: table name ORDER is already a SQL system word. Such a collision should be avoided, so rename your table order to orders for example.

-- tesu

Edited by tesuji because: n/a
Member Avatar for Virangya

thanx a lot!
scenario is correct. for ease of use only i typed the name of the table as "order". Thanx a lot for your help!

Edited by Virangya because: n/a
Member Avatar for tesuji

Well, if the scenario I sketched out is correct, then there are two eventualities:

1. Your first posted query is wrong (then one could assume your data model might be correct)

or

2. Your first posted query is correct, then your data model is wrong.

Your data model should have at least one one-to-many (---<) and two many-to-many (---< >---) relationships:

1. customer ----< orders ----< ordered_items >---- product

2. ordered_items ----< ordered_accessories >---- accessory

Entity product and accessory can be put together in one entity only (yet, i would not prefer)

But ordered_items and ordered_accessories cannot by in one and the same linking entity (relationship that forms the many-to-many).

So redesign of damo seems to be necessary.

Finally, your posted problem cannot be solve by OR (I already told) nor by UNION you had been asking in a message to me.

I wish you success in your upcoming exams! (term is ending now?)

-- tesu

Edited by tesuji because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.