Hello,
This is an extension to the thread about authors.
To retrieve the buyer and seller for the book. Now, both buyer and seller are users. The table "books" has references to the users table in the fields "sellerid" and "buyerid".
With a query like this
SELECT u1.email , u2.email from users as u1, users as u2, books as b where u1.id=b.sellerid and u2.id=buyerid;
Problem is buyerid is NULL until some one places an order; in that case an empty row set is being returned.
To get around that, I stepped into the unfamiliar territory of JOINS and came up with this
SELECT u1.email , u2.email FROM users as u1, users as u2 inner join phones as p on p.sellerid=u1.id left outer join phones as p1 on p1.buyerid=u2.id
Result an error
unknown column 'u1.id' in 'on' clause.
There is a field id in the users table.
Going by my previous experience or lack of it,I believe there is a simpler way of doing it.
Thank you.