I have two tables as follows:
pep_saleslist (Table 1)
VehicleID
ISDate
Make
Model
Color
OOSDate
Invoice
Miles
pep_invoice_options (Table 2)
Make
Model
Invoice
P1
P2
P3
P4
P5
Options
What I am trying to do is display all the fields from Table 1 and the Options field from Table 2 when the Invoice field from both tables matches. Along with that I want to display one of the P# fields from Table 2 (Identified in the output as Price) based on criteria found in the Miles field in Table 1 (if Miles < 1000, show P1, if Miles >=1000 AND Miles <2499, show P2 etc).
Example output:
Vehicle -> ID -> ISDate -> Make -> Model -> Color -> OOSDate -> Invoice -> Miles -> Price
Is this possible? I am not sure if I am supposed to use JOIN, INNER JOIN, LEFT JOIN, etc. And I am not totally sure on how to display the right P#. Any help would be most appreciated.
Question using JOIN to display data from two tables
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
This is very experimental as I have no test data to check against, to verify it for myself... But perhaps a good start to rearrange yourself:
Code: Select all
select
pep_saleslist.*,
pep_invoice_options.Options,
CASE
WHEN (pep_saleslist.Miles < 1000) THEN pep_invoice_options.P1 as Price
WHEN (pep_saleslist.Miles > 1000 and pep_saleslist.Miles < 2499) THEN pep_invoice_options.P2 as Price
WHEN (pep_saleslist.Miles > 2500 and pep_saleslist.Miles < 5000) THEN pep_invoice_options.P3 as Price
ELSE pep_invoice_options.P4 as Price
from
pep_saleslist
inner join pep_invoice_options on pep_saleslist.Invoice = pep_invoice_options.Invoice