How do I write a mysql subquery where one table returns multiple rows based from a table that returns one row?

Question

Supposed that I have a table like this:

**tbl_purchase**
PurchaseID
Date
Supplier

**tbl_purchaseitem**
PurchaseItemID
PurchaseID

I want to create a query where it will return something like:

**Header**
PurchaseID = 2
Date: 4/26/12
Supplier:stackoverflow

*Items*
PurchaseItemID:3
PurchaseID:2

PurchaseItemID:4
PurchaseID:2

PurchaseItemID:5
PurchaseID:2

Sir/Ma'am your answers would be of great help and be very much appreciated.

Stevo · Accepted Answer · 2012-03-07 06:54:43Z

1

Select * from tbl_purchase 
inner join 
tbl_purchaseitem
on 
tbl_purchase.PurchaseID = tbl_purchaseitem.PurchaseID
where 
PurchaseID = 2 
and 
Date = '4/26/12' 
and 
Supplier = 'stackoverflow'

answered Mar 7, 2012 at 6:54

Stevo

2,6393 gold badges26 silver badges32 bronze badges

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

Comments

Code Prank · Accepted Answer · 2012-03-07 07:57:15Z

1

Like this:

SELECT pi.*
FROM tbl_purchase p
INNER JOIN tbl_purchaseitem pi
     ON p.PurchaseID = pi.PurchaseID
WHERE p.PurchaseID = 2 AND
      p.Date       = '2012-04-26' AND
      p.Supplier   = 'stackoverflow'

edited Mar 7, 2012 at 7:57

Code Prank

4,2706 gold badges35 silver badges48 bronze badges

answered Mar 7, 2012 at 6:55

Mahmoud Gamal

80.3k18 gold badges143 silver badges168 bronze badges

Collectives™ on Stack Overflow

How do I write a mysql subquery where one table returns multiple rows based from a table that returns one row?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related