Search code examples
javasqldatabasederby

Java Derby SQL Join

I have 3 tables:

APPLICATION -

ID (PK)
DEVELOPER
GENRE
DESCRIPTION
POPULARITY
COST

CUSTOMER -

ID (PK)
FIRSTNAME
SURNAME
ADDRESS
TOWN
POSTCODE
PROFESSION

CUSTOMER_PURCHASES -

TRANSACTION_ID (PK)
CUSTOMER_ID (FK)
APPLICATION_ID (FK)

I have a method that allows a CUSTOMER to purchase an APPLICATION. The method populates the table CUSTOMER_PURCHASES with an auto genereated Transaction_ID + CUSTOMER_ID (Provided by the customer table) + APPLICATION_ID (Provided by the application table).

I need the SQL statement that gives me the output -

Transaction ID: 501
Customer ID: 301
Name: Beatrice May
Application ID: 302
Application Cost: £1.00

The SQL query I have that doesn't work is:

SELECT TRANSACTION_ID,
       CUSTOMER.ID,
       CUSTOMER_ID,
       FIRSTNAME, 
       SURNAME, 
       APPLICATION_ID, 
       APPLICATION.COST 
FROM CUSTOMER, CUSTOMER_PURCHASES,APPLICATION WHERE CUSTOMER.ID = CUSTOMER_PURCHASES.CUSTOMER_ID

Thank you

James

Solution

  • Use explicit join syntax

    SELECT TRANSACTION_ID,
       CUSTOMER.ID,
       CONCAT(FIRSTNAME, ' ',  SURNAME) AS Name, 
       APPLICATION.ID, 
       APPLICATION.COST 
FROM CUSTOMER
JOIN CUSTOMER_PURCHASES
ON CUSTOMER.ID = CUSTOMER_PURCHASES.CUSTOMER_ID
JOIN APPLICATION 
ON APPLICATION.ID = CUSTOMER_PURCHASES.APPLICATION_ID