Stephanie Iruthayanathan Stephanie Iruthayanathan - 7 months ago 20
SQL Question

column ambiguously defined sql plus

I am trying to run this in sql plus but it is giving me this error

SELECT reviewer_address, reviewer_name, review_date
FROM reviewers, reviews
INNER JOIN reviews ON reviewers.reviewer_address = reviews.review_date
INNER JOIN reviewers ON reviews.review_date = reviewers_address;

ERROR at line 1:
ORA-00918: column ambiguously defined


Does anyone know what I am doing wrong?

These are the tables of reviews and reviewers

CREATE TABLE reviewers (
reviewer_id NUMBER (6),
reviewer_name VARCHAR2 (30),
review_name VARCHAR2 (15),
reviewer_address REF address_type SCOPE IS addresses
);

CREATE TABLE reviews (
review_id NUMBER (6),
review_name VARCHAR2 (30),
review_date DATE,
reviewer_id NUMBER (6) NOT NULL,
venue_id NUMBER (6) NOT NULL
);


And these are the inserts for them two tables

--reviews
INSERT INTO reviews (review_id, review_name, review_date, reviewer_id, venue_id)
VALUES (1, 'MIKE POWELL', '03-DEC-2015', 1, 1);

INSERT INTO reviews (review_id, review_name, review_date, reviewer_id, venue_id)
VALUES (2, 'JOHN MAYER', '15-MAY-2004', 2, 2);

INSERT INTO reviews (review_id, review_name, review_date, reviewer_id, venue_id)
VALUES (3, 'SETH CONNOR', '25-JAN-2011', 3, 3);

INSERT INTO reviews (review_id, review_name, review_date, reviewer_id, venue_id)
VALUES (4, 'JULIA SMITH', '08-AUG-2013', 4, 4);

INSERT INTO reviews (review_id, review_name, review_date, reviewer_id, venue_id)
VALUES (5, 'MADONNA MARLEY', '30-NOV-2001', 5, 5);


--reviewers
INSERT INTO reviewers (reviewer_id, reviewer_name, review_name, reviewer_address)
SELECT 1, 'MIKE POWELL', 'TEST REVIEW', REF(a)
FROM addresses a
WHERE street = '13 JAMES ROAD';

INSERT INTO reviewers (reviewer_id, reviewer_name, review_name, reviewer_address)
SELECT 2, 'JOHN MAYER', 'MAIN REVIEW', REF(a)
FROM addresses a
WHERE street = '54 BRIDGE STREET';

INSERT INTO reviewers (reviewer_id, reviewer_name, review_name, reviewer_address)
SELECT 3, 'SETH CONNOR', 'CAR REVIEW', REF(a)
FROM addresses a
WHERE street = '10 LIME STREET';

INSERT INTO reviewers (reviewer_id, reviewer_name, review_name, reviewer_address)
SELECT 4, 'JULIA SMITH', 'BEAUTY REVIEW', REF(a)
FROM addresses a
WHERE street = '1 PALL MALL STREET';

INSERT INTO reviewers (reviewer_id, reviewer_name, review_name, reviewer_address)
SELECT 5, 'MADONNA MARLEY', 'CLOTHES REVIEW', REF(a)
FROM addresses a
WHERE street = '28 MAIN ROAD';


I hope this makes it easier for you guys

Answer

Never mix old, implicit join syntax with modern, explicit JOIN syntax. And don't duplicate the tables in the select list.

SELECT reviewer_address, reviewer_name, review_date
FROM reviewers
INNER JOIN reviews ON reviewers.reviewer_id = reviews.reviewer_id
Comments