Welcome!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

SignUp Now!
  • Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • You can also use markdown to share your code. When using markdown your code will be automatically converted to BBCode. For help with markdown check out the markdown guide.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

mysql confusion with creating VIEWs

MstfaBaban

New Coder
[CODE lang="sql" title="SQL"]select `invoice`.`date` AS `vdate`,`invoice_details`.`product_id` AS `product_id`,

sum(`invoice_details`.`quantity`) AS `sell`,

0 AS `Purchase` from (`invoice_details` join `invoice` on((`invoice_details`.`invoice_id` = `invoice`.`invoice_id`)))

group by `invoice_details`.`product_id`,`invoice`.`date` union select `product_purchase`.`purchase_date` AS `purchase_date`,`product_purchase_details`.`product_id` AS `product_id`,

0 AS `0`,sum(`product_purchase_details`.`quantity`) AS `purchase` from (`product_purchase_details` join `product_purchase` on((`product_purchase_details`.`purchase_id` = `product_purchase`.`purchase_id`))) group by `product_purchase_details`.`product_id`,

`product_purchase`.`purchase_date`[/CODE]

guys if i needed to add another col to this view

from product_purchase_details.expire_date

how do i do this

dont give me code

just tell me how i can implement this in mysql
 
Last edited by a moderator:
Welcome to Code Forum! I'm not all to familiar with this but I do know some members whom are pretty skilled at SQL, perhaps they can help!

I also inserted your code in a special BBCode tag so it's easier to read.
 
Found a fix

SQL:
SELECT 0 AS `expire_date` ,`invoice`.`date` AS `vdate`,`invoice_details`.`product_id` AS `product_id`,

sum(`invoice_details`.`quantity`) AS `sell`,

0 AS `Purchase` from (`invoice_details` join `invoice` on((`invoice_details`.`invoice_id` = `invoice`.`invoice_id`)))

group by `invoice_details`.`product_id`,`invoice`.`date` union SELECT `product_purchase_details`.`expire_date` AS `expire_date`,

  `product_purchase`.`purchase_date` AS `purchase_date`,`product_purchase_details`.`product_id` AS `product_id`,

0 AS `0`,sum(`product_purchase_details`.`quantity`) AS `purchase` from (`product_purchase_details` join `product_purchase` on((`product_purchase_details`.`purchase_id` = `product_purchase`.`purchase_id`))) group by `product_purchase_details`.`product_id`,

`product_purchase`.`purchase_date`
 
Last edited:
Found a fix

SQL:
SELECT 0 AS `expire_date` ,`invoice`.`date` AS `vdate`,`invoice_details`.`product_id` AS `product_id`,

sum(`invoice_details`.`quantity`) AS `sell`,

0 AS `Purchase` from (`invoice_details` join `invoice` on((`invoice_details`.`invoice_id` = `invoice`.`invoice_id`)))

group by `invoice_details`.`product_id`,`invoice`.`date` union SELECT `product_purchase_details`.`expire_date` AS `expire_date`,

  `product_purchase`.`purchase_date` AS `purchase_date`,`product_purchase_details`.`product_id` AS `product_id`,

0 AS `0`,sum(`product_purchase_details`.`quantity`) AS `purchase` from (`product_purchase_details` join `product_purchase` on((`product_purchase_details`.`purchase_id` = `product_purchase`.`purchase_id`))) group by `product_purchase_details`.`product_id`,

`product_purchase`.`purchase_date`
Awesome! Marked your answer as best answer!
 
Just curious - where is this SQL coming from? The query is combining a LOT of data and I can foresee some serious problems in your future if you ever have a large database. I know it might not be an issue now, but if you ever have thousands or millions of invoices you will see some serious lag time with your database query. Even a simple "SELECT column FROM table WHERE column2 = 'something' " can take a long time to load with millions of rows. Of course, that's not going to affect most websites, but it's something to keep in mind. We had one project that ran fine in development / testing, but when the client added 15 million rows of business data, the site was barely usable. We had to do something similar to Facebook's early days - splitting the data into separate tables and databases for each US State.
 
Yea this isn't my code, a client asked for features added to his existing application. its all good now <3

But the way the app was coded was really bad and made it horrible to make changes on it.
 

New Threads

Latest posts

Buy us a coffee!

Back
Top Bottom