hard to understand how to use self join and it function

Question

This is my codding and the value insert to the table.

CREATE TABLE provendor (
  prcode varchar (10), 
  prvendor varchar (5),
  prpricing decimal (9,2)
);

INSERT INTO provendor(prcode, prvendor, prpricing)
VALUES  
    ('PW-1001', 'P10', '500.00'),
    ('PQ-4570', 'P88', '35.00'),
    ('MM-120E', 'P67', '245.00'),
    ('MM-F12WR', 'P10', '1210.00'),
    ('PW-QZR-09', 'P10', '79.00'),
    ('PQ-4570', 'P10', '32.50'),
    ('MM-120E', 'P88', '242.00'),
    ('PW-1001', 'P45', '550.00'),
    ('MM-F12WR', 'P73', '1200.00'),
    ('PQ-4570', 'P67', '33.00'),
    ('MM-F12WR', 'P35', '1189.00'),
    ('PW-1001', 'P23', '510.00'),
    ('PQ-4570', 'P35', '36.00'),
    ('MM-120E', 'P23', '250.00');

I need to perform a query to retrieve product code, the pricing and vendor code for product that supplied by more than one vendors and your record must be sorted by product code.

This is codding that I used for the self join

SELECT A.prvendor AS provendor1, B.prvendor AS provendor2, A.prcode
FROM  provendor A, provendor B
WHERE  prvendor1 > A.prcode
ORDER BY A.prcode;

When I run it the error will say that prvendor values is ambiguous. I dont know how to solve it

Hi. Read about table aliases in joins in the intro to joins that you should have read before you ran this code. Also google your error message with & without your particular strings next time. You are joining a & b, both have the value of provendor so both have a column prvendor1. Disambiguate by using an alias. This is a faq. — philipxy, Oct 22 '18 at 12:06
Possible duplicate of [Query error with ambiguous column name in SQL](https://stackoverflow.com/questions/12662954/query-error-with-ambiguous-column-name-in-sql) — philipxy, Oct 22 '18 at 12:07
Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Madhur Bhaiya, Oct 22 '18 at 12:20
What would be expected output, as per your given sample input table. — Madhur Bhaiya, Oct 22 '18 at 13:08
You should read up on sql order of execution. - the where clause executes before the select so cannot see aliases created in the select clause. — P.Salmon, Oct 22 '18 at 13:27

score 1 · Answer 1 · answered Oct 22 '18 at 13:20

You can't use the table alias in the where clause so you need

SELECT A.prvendor AS provendor1, B.prvendor AS provendor2, A.prcode
FROM  provendor A, provendor B
WHERE  A.prvendor > A.prcode
ORDER BY A.prcode;

However, you should use the ANSI join syntax as is clearer to understand as is generally seen as best practice:

SELECT A.prvendor AS provendor1,
       B.prvendor AS provendor2,
       A.prcode
FROM   provendor A
       JOIN provendor B
           ON A.prvendor > A.prcode
ORDER BY A.prcode;

hard to understand how to use self join and it function

1 Answers1