0

i hope you understand my problem, on my db i have ord_detail & custom_ord_detail tables

ord_detail 

-------------------------------------------------
ordID | custID | productID |quantity | discount |
-------------------------------------------------
002   |  1     |     5     |  2      |     0    |
-------------------------------------------------

custom_ord_detail 

--------------------------------------------------------
ordID | custID | custom_productID |quantity | discount |
--------------------------------------------------------
002   |  1     |     2            |  1      |     0    |
-------------------------------------------------

ordID -> primary key auto_increment (in second table ordID is primary key auto_increment , foreign key refere to table called custom_ord_reply)

custID -> foreign key refer to customer table , productID -> foreign key refer to products table , custom_productID foreign key refer to custom_ord_reply table that the customer request the product in custom_ord_request table.

After we bring the specific product we store it into custom_ord_reply, now we use ord_detail & custom_ord_detail when user added the product into cart and we use union all to show the all products in cart , if the customer want to update the quantity or delete specific product how can do this becouse they will be Duplicate values for the ordID column , how could fix this problem ? any idea union all with join

EzLo
  • 13,780
  • 10
  • 33
  • 38
Omar
  • 13
  • 3
  • Can you post you business logic/requirement, your schema doesn't look good may there is more better way to go, Also if you aren't sure to update schema can you include the union query in your post? – M Khalid Junaid Jun 19 '18 at 05:01
  • Please check the picture called union with each join, this is the shopping cart, and as you can see the ordID is similar –  Omar Jun 19 '18 at 09:09
  • first result is available product in store and the second is product which we customize it as request –  Omar Jun 19 '18 at 09:14
  • i will show the result as list view in the app my question when the customer want to remove the first one or update the quantity how can i make sure he was select the first one not the second because both has same ordID ? or i have to change my schema ? –  Omar Jun 19 '18 at 09:24

1 Answers1

0

Viewing your query what I have in mind is add a new attribute/column in both queries as product_type if ordID in both table is unique ord_detail,custom_ord_detail

'product' as product_type  // 1st query
'custom' as product_type  // 2nd query

If not then 2 new attributes/columns in your query, First product_type as above second add some unique identifier for both queries like for first query include product id and for second query include request id (I assume request id from table custom_ord_request is set to auto increment)

SELECT p.id as uid, 'product' as product_type, od.ordID , p.prod_name , p.prod_descrip , p.price , od.quantity , od.discount
FROM ord_detail od 
JOIN product p ON od.productID = p.productID
WHERE od.custlD = 1 
UNION ALL 
SELECT rq.id as uid, 'custom' as product_type, cod.ordID , rq.ord_title , rp.description , rp.price , cod.quantity cod.discount
FROM custom_ord_detail cod 
JOIN custom_ord_reply rp ON cod.custom_prod_servID = rp.custom_prod_servID 
JOIN custom_ord_request rq ON rp.ordID = rq.ordID 
WHERE cod.custlD = 1

This way you could differentiate between your products and custom products for your cart.

Another way would be defining supertype/subtype relationship for your tables.

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • 1
    by adding new column named ord_type and your query with union all the problem was solved i used your query to showing the cart and the ord_type column job is to determind which table the user want to modefiing / delete row + the values such as uid , custID and ordID ,for condition so , thanks a lot –  Omar Jun 20 '18 at 15:40