Question using JOIN to display data from two tables
Posted: Thu Sep 18, 2003 5:19 pm
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.
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.