Selecting data from two tables using Mysql stored procedures

Question

I have to select data from two tables with following criteria,

lets say there are two tables as,,

Table one

id | itemName | Quantity | companyName

1    bread      25         the Baker pvt ltd
2    butter     30         green famers

Table two

id | itemName | itemPrice

1    bread      30.50      
6    jam        80.25

what I need is,

select items out of two tables which their ids are matching and the quantities of them should be multiplied by the unit price if ids are matching. The rows which don't have matching ids should be selected but their quantities should not multiplied.

Where is the unit price?

ajreal
– ajreal

2011-09-07 10:32:53 +00:00
Commented Sep 7, 2011 at 10:32 — ajreal
– ajreal, Commented Sep 7, 2011 at 10:32

Shef · Accepted Answer · 2011-09-07 10:41:36Z

1

   SELECT o.id, o.itemName, o.companyName, o.Quantity * IFNULL(t.itemPrice, 1) total
     FROM one o
LEFT JOIN two t 
       ON o.id = t.id

answered Sep 7, 2011 at 10:41

Shef

45.7k15 gold badges82 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

judda · Accepted Answer · 2011-09-07 10:56:56Z

0

Something like this should work ...

Select a.id, a.itemName, a.companyName, a.Quantity * IFNULL(b.itemPrice,1) As total
From table1 as a 
Left Join table2 as b on a.id = b.id

edited Sep 7, 2011 at 10:56

answered Sep 7, 2011 at 10:39

judda

3,9721 gold badge27 silver badges27 bronze badges

2 Comments

Harsha Over a year ago

thanx a lot for your answer, with this query i can get all the rows that the id is match, when there is no matching ids of each tables how can I select them also?

judda Over a year ago

I think I get what you mean. I think you want a FULL OUTER JOIN instead then. This will return data from both tables even if there is no match. You'll want to add a few more columns in if this is the case because if the LHS table doesn't have a match, you will have NULL for all values currently being selected.

Collectives™ on Stack Overflow

Selecting data from two tables using Mysql stored procedures

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related