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

Answer 1

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;