Need help designing an SQL statement
I'm no SQL guru, by any means, and I'm having a hard time wrapping my head around how to do a certain SQL query.
I have three Oracle database tables, MBRHISTDETL, LOCINFODETL, and METERCHGDETL. From MBRHISTDETL I'll get the following fields: MBRSEP, LOCATION, BILLTYPE, BILLMOYR, KWH. From LOCINFODETL (LOCATION is key) I'll get DIST, CYCLE, ADDR1. From METERCHGDETL (MBRSEP is key) I'll get METER.
So far, using my query below, I'm able to get all the information I need from MBRHISTDETL and LOCINFODETL. However, I don't quite now how to add the next table, METERCHRDETL, into the query statement. Can someone help me out with this?
Here's my query, so far:
SELECT LOCINFODETL.LOCATION AS L.LOCATION,
LOCINFODETL.DIST, LOCINFODETL开发者_StackOverflow社区.CYCLE,
LOCINFODETL.ADDR1, MBRHISTDETL.MBRSEP,
MBRHISTDETL.LOCATION AS M.LOCATION,
MBRHISTDETL.BILLTYPE, MBRHISTDETL.BILLMOYR,
MBRHISTDETL.KWH
FROM MBRHISTDETL
INNER JOIN L.LOCINFODETL ON H.MBRHISTDETL.LOCATION = L.LOCATION
WHERE MBRHISTDETL.BILLTYPE = '09'
AND CAV_MBRHISTDETL.BILLMOYR <> '9999'
AND SUBSTR(LOCINFODETL.CYCLE,0,2) = '04'
AND LOCINFODETL.DIST = '16'
This should get you started -- but you need to know which field links your M table.
Also -- just a style tip -- if you're trying to make sense out of sql for the first time especially -- you'll be able to proof your sql a lot easier if you format it a little more vertcially. My style might not be the best, but you can see if it makes things easier to read for you anyway.
SELECT
L.LOCATION AS L_LOCATION,
L.DIST,
L.CYCLE,
L.ADDR1,
H.MBRSEP,
H.LOCATION AS H_LOCATION,
H.BILLTYPE,
H.BILLMOYR,
H.KWH,
M.METER
FROM
MBRHISTDETL H
INNER JOIN
LOCINFODETL L
ON
H.LOCATION = L.LOCATION
INNER JOIN
METERCHRDETL M
ON M.___KEY___ = H.___KEY___ -- or perhaps L.___KEY___
WHERE
H.BILLTYPE = '09'
AND H.BILLMOYR <> '9999'
AND SUBSTR(L.CYCLE,0,2) = '04'
AND L.DIST = '16'
Something like this should work, although I'm not 100% of the prefixes that you are using.
SELECT LOCINFODETL.LOCATION AS L.LOCATION,
LOCINFODETL.DIST, LOCINFODETL.CYCLE,
LOCINFODETL.ADDR1, MBRHISTDETL.MBRSEP,
MBRHISTDETL.LOCATION AS M.LOCATION,
MBRHISTDETL.BILLTYPE, MBRHISTDETL.BILLMOYR,
MBRHISTDETL.KWH, METERCHGDETL.METER
FROM MBRHISTDETL
INNER JOIN L.LOCINFODETL ON H.MBRHISTDETL.LOCATION = L.LOCATION
INNER JOIN METERCHGDETL ON H.MBRHISTDETL.MBRSEP = METERCHGDETL.MBRSEP
WHERE MBRHISTDETL.BILLTYPE = '09'
AND CAV_MBRHISTDETL.BILLMOYR <> '9999'
AND SUBSTR(LOCINFODETL.CYCLE,0,2) = '04'
AND LOCINFODETL.DIST = '16'
精彩评论