Linked Questions

Popular Questions

Making alias that have similar attributes and conditions

Asked by At

I'm making query that needs to output new alias with different, but similar, conditions.

There are two tables; one for person, product, and sale. They should have something like following:

person = (personID, personName, isValidPerson)
product = (productID, productName)
sale = (productID, tradeTime, customerID, employeeID)

My goal is to output productID, productName, customerName, and employeeName for specific time. It also has to be distinct.

As you can see from above table, we don't have attributes called customerName and employeeName. I understand that I have to make alias for those two attributes, but I can't get them to work for some reason and gives me an error.

So far, I tried to use AS statement by linking person and sale table with two conditions.

To elaborate on this, I did the following:

SELECT DISTINCT s.productID, prod.productName,
                psn.personName AND psn.personID = s.customerID AS customerName,
                psn.personName AND psn.personID = s.employeeID AS employeeName,
FROM person psn, product prod, sale s
WHERE s.tradeTime < '2017-01-01 11:00:00';

However, I am keep getting an error for assigning alias to customerName and employeeName column. I attempted to do psn.personID AS customerName and same for employeeName and declare from WHERE. However, I had issue with this as well since both customerName and employeeName has issue recognizing which psn.personName to choose.

If I can get this right, I am hoping that all four attributes mentioned above should display its results for certain time range that's before '2017-01-01 11:00:00'.

Related Questions