Selecting from one table or the other

[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";

:-/

[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

[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

[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!

[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