CODEWITHSUNDEEP
sqljoin
Jay
Jay

Reputation: 3082

joining tables in correct way

I am trying to return a list of all bill details on the one database row but the manner in which I am joining the tables does not seem to be having the desired effect, I have two classes bill and meter:

bill { billid, customername, town, postcode, dateofissue, totalamount } meter { meterid, billid, metertype, meterread, meterreaddate }

I am trying to do the join as follows:

SELECT bill as a
inner join meter as b on a.billId = b.billId
where dateOfIssue between '1 jul 2015' and '30 jun 2016'

If the bill has two different types of meters and one meter type is day and one is night I am then getting two rows for that bill. How can I select one row with setting a column so that if metertype is night it comes in the column header called night and if it is day it comes in a column header called day

What kind of join type would best serve this purpose?

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Using table aliases is good. Using abbreviations for the tables is even better. In addition, you should use standard date formats.

The answer to your question is to use conditional aggregation:

SELECT b.billId,
       SUM(CASE WHEN metertype = 'day' then metterread else 0 end) as reading_day,
       SUM(CASE WHEN metertype = 'night' then metterread else 0 end) as reading_night
FROM bill b JOIN
     meter m
     ON b.billId = m.billId
WHERE b.dateOfIssue between '2015-07-01' and '2016-06-30'
GROUP BY b.billId;

Upvotes: 4

Related Questions