Question using JOIN to display data from two tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Question using JOIN to display data from two tables

Post by RobertGonzalez »

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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

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
Post Reply