display data from multiple tables using php

Question

I have two tables

tblData
ID Name
1  ABC
2  XYZ

tblDetails  
ID DataID PayDate     Amount ApprovedDate
1  1      15-08-2015  200    20-18-2015 
2  1      16-08-2015  300    20-18-2015
3  1      17-08-2015  50     20-18-2015 
4  2      18-08-2015  100    21-18-2015   
5  2      19-08-2015  500    21-18-2015

I need to get the result like the following

ID          Duration                TotalAmount ApprovedDate 
1   15-08-2015 - 17-08-2015         550         20-18-2015  
2   18-08-2015 - 19-08-2015         600         21-18-2015

How can I accomplish this?

SELECT somecolumn, AGGREGATEVALUEOF(someothercolumn), ANOTHER_AGGREGATE_VALUE_OF(another_column), AND_YET_ANOTHER_AGGREGATEVALUE_OF(yet_another_column) FROM some_table GROUP BY somecolumn; — Strawberry
– Strawberry, Commented Aug 20, 2015 at 15:32
Hi Stuart, the DataID comes from tblData.ID - somestimes we need to show the respective records from tblDetails, related to ID from tblData. — chuplu
– chuplu, Commented Aug 20, 2015 at 15:45

Giorgos Betsos · Accepted Answer · 2015-08-20 15:34:05Z

6

It seems like a simple GROUP BY together with some aggregate functions can do the job:

SELECT DataID, CONCAT(MIN(PayDate), ' - ', MAX(PayDAte)) AS  Duration,
       SUM(Amount) AS TotalAmount, MAX(ApprovedDate) AS ApprovedDate 
FROM tblDetails
GROUP BY DataID

Demo here

Note: Table tblData does not seem to play any role in producing the required result set.

answered Aug 20, 2015 at 15:34

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

1 Comment

Giorgos Betsos Over a year ago

@Strawberry Excuse me?

John · Accepted Answer · 2015-08-20 15:28:23Z

0

You can use joins in your query which will join data from different tables into one. Simple example

answered Aug 20, 2015 at 15:28

John

1,6654 gold badges24 silver badges50 bronze badges

2 Comments

Strawberry Over a year ago

There are no joins required here. All the info is in the second table.

Funk Doc Over a year ago

a simple join will not produce the desired result set as stated in the question.

Collectives™ on Stack Overflow

display data from multiple tables using php

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related