MySQL - nested select statement for JOIN?

Question

I have 3 tables that are currently being joined using inner joins. The tables are:

invoice, contract and meter.

Some simplified sample data:

//invoice

id | contract_id

1  | 123

//contract

id  | meter_id | supplier     | end_date

123 | 100      | British Gas  | 2013-12-20

456 | 100      | nPower       | 2014-03-03

//meter

id  | meter-id
1   | 100

My aim is to join the tables but retrieve only the latest (MAX) end_date and get the supplier. Normally this wouldn't be a problem, but I only have contract 123 to join on, not contract 456. As shown, they both share the same meter_id.

//Current query
SELECT
contract.supplier AS supplierName
FROM invoice
INNER JOIN contract ON contract.id=invoice.contract_id
INNER JOIN meter ON meter.id=contract.meter_id

How do I do this? Is it via a nested select or something?? Thanks

In this case do you want to bring back the supplier details for 123 (which are not the latest, but which do match on invoice) or 456 (which is the latest but doesn't have a matching invoice)? Could you add the expected output? — Kickstart
– Kickstart, Commented Dec 17, 2013 at 13:34
@anewvision: my comment is not upto your problem but Did you try LEFT JOIN and saw the difference in result also? — Sohail
– Sohail, Commented Dec 17, 2013 at 13:48
@Sohail - why would a LEFT JOIN work? The invoice table does not contain the meter_id. — anewvision
– anewvision, Commented Dec 17, 2013 at 14:13

Kevin Holditch · Accepted Answer · 2013-12-17 13:22:49Z

5

SELECT *
FROM
(
    SELECT meter_id, supplier, MAX(end_date) end_date
    FROM contract 
    GROUP BY meter_id, supplier
) a
JOIN contract c ON c.meter_id = a.meter_id AND a.end_date = c.end_date
JOIN meter m ON m.meter-id = c.meter_id
JOIN invoice i ON i.contract_id = c.id

answered Dec 17, 2013 at 13:22

Kevin Holditch

5,3534 gold badges22 silver badges37 bronze badges

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

3 Comments

Kickstart Over a year ago

Also (assuming a GROUP BY on meter_id) you probably want to remove supplier from the sub query (it might not refer to the one for the max end_date), and just get it from the join to the contract table

Kevin Holditch Over a year ago

Sorry my bad missed the group by... Should be fine now

anewvision Over a year ago

All working, thanks to kevin for the code and @paparazzokid for the fiddle.

Twinkalkumar Savani · Accepted Answer · 2013-12-17 13:31:21Z

3

It should be like :

    SELECT contract.supplier AS supplierName
    FROM invoice
    INNER JOIN contract ON contract.id=invoice.contract_id
    INNER JOIN meter ON meter.meter_id=contract.meter_id
    order by end_date DESC 
    limit 1

answered Dec 17, 2013 at 13:31

Twinkalkumar Savani

2781 gold badge2 silver badges11 bronze badges

1 Comment

anewvision Over a year ago

That would work for the example, but not in a database full of thousands of records. I simplified the dataset for the question.

Aditya · Accepted Answer · 2013-12-17 13:31:16Z

0

Hope this is what you want & this should work.

SELECT
contract.supplier AS 'supplierName'
end_date AS 'end_date'
FROM contract
INNER JOIN invoice ON contract.id=invoice.contract_id
INNER JOIN meter ON meter.id=contract.meter_id
WHERE end_date in (select MAX(end_date) from contract)

If you want it to be latest (MAX) end_date for each supplier, simply change the sub-query & add group by supplier.

answered Dec 17, 2013 at 13:31

Aditya

2,3198 gold badges40 silver badges58 bronze badges

1 Comment

Aditya Over a year ago

you can also alias, end_date as start_date, it doesn't matter. It is not compulsory to alias every column, but I did, anyways, thanks for vote down.

Soner Gönül · Accepted Answer · 2015-10-21 11:32:45Z

0

select i.contract_id,m.meter_id,c.supplier,c.end_date
from invoice i
inner join contract c on i.contract_id=c.id
inner join meter m c.meter_id=m.meter_id

edited Oct 21, 2015 at 11:32

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

answered Oct 21, 2015 at 11:28

Kavya Mogili

1

Collectives™ on Stack Overflow

MySQL - nested select statement for JOIN?

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related