CODEWITHSUNDEEP
sqloracle-databasejoin
Saliwarrior Al-Sahli
Saliwarrior Al-Sahli

Reputation: 11

Support with sql join statement

I am a student new to sql and Oracle Apex. Initially, I was given this working snippet:

SELECT Contract_no, customer_code, Start_Date, End_Date
FROM Contract

I attempted to add this code to it:

INNER JOIN Customer
ON Contract.customercode = Customer.Customername

Upon adding my code, I received an Oracle / PLSQL: ORA-06550 Error Message, which stated:

Invalid user.table.column, or column specification.

I am trying to add Customer Name, from the customer table, to the contract table by using the JOIN statement.

I basically have 2 tables: Contract and Customer.

Customer contains the following columns

Contract contains the following columns

Upvotes: 0

Views: 89

Answers (3)

Krazy Dev
Krazy Dev

Reputation: 182

Try

SELECT Contract_no, customer_code, Start_Date, End_Date
FROM Contract
INNER JOIN Customer
ON Contract.customer_code = Customer.customer_code

Make sure you're joining your primary key on the foreign key.

Upvotes: 0

Roger Cornejo
Roger Cornejo

Reputation: 1547

Your second sample's field names are wrong.

Contract.customercode should be Contract.customer_code, and if the naming conventions in your database are consistent then Customer.Customername should be Customer.customer_name. However, you probably don't want to use customer_name. Given that both tables contain customer_code fields, you probably meant to join based on those. Joining records where someone's name is the same as their code is unlikely to produce good results.

So your last line would be

ON Contract.customer_code = Customer.customer_code

Upvotes: 1

Prashant Sharma
Prashant Sharma

Reputation: 26

I think there is a space in one of the the column names. Please post the column definition using the DESC command. If there were spaces, you'd need to refer such column with quotes throughout your code, which is not really ideal to use. Oracle does not recommend quoted identifiers, so please do not allow space in column names. Please use an alias to identify the column name in the select statement and also use consistent column names throughout the query. This will help in code maintenance and better readability of code.

Below is a small piece of code i tried for your reference.

CREATE TABLE customer(
customer_code VARCHAR2(10),
customer_name  VARCHAR2(10),
address        VARCHAR2(100),
postcode       VARCHAR2(10),
telephone      VARCHAR2(10));


CREATE TABLE contract(
contract_no     NUMBER, 
customer_code   VARCHAR2(10), 
start_date      DATE,
end_date        DATE
);


INSERT INTO customer
values ('1','Person 1','Address 1','12345A','0000000000');

INSERT INTO contract
VALUES(1,'1','01-JAN-2010','31-DEC-2019');

SELECT con.contract_no, cust.customer_code, con.start_date, con.end_date
FROM contract con
INNER JOIN customer cust
ON con.customer_code = cust.customer_code;

Output:
CONTRACT_NO CUSTOMER_C START_DAT END_DATE 
----------- ---------- --------- ---------
          1 1          01-JAN-10 31-DEC-19

HTH

Upvotes: 0

Related Questions