[RESOLVED] problem with INNER JOIN

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

Quote:

---

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

Quote:

---

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

Quote:

---

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.

If you have a query with multiple tables, you are using joins - you are just either not specifying the join (thus get a Cartesian product), or are specifying the join conditions in the Where clause.

The syntax for the FROM clause (including joins) should be like this:

Code:

---

FROM <table1>
[INNER/LEFT OUTER/RIGHT OUTER] JOIN <table2> ON (conditions to join table2 to table1)
[INNER/LEFT OUTER/RIGHT OUTER] JOIN <table3> ON (conditions to join table3 to table1 or table2)
...

---

So based on that syntax, you should have something like this:

Code:

---

SELECT u1.email , u2.email
FROM phones as p
INNER JOIN users as u1 ON (p.sellerid=u1.id)
LEFT OUTER JOIN users as u2 ON (p1.buyerid=u2.id)

---

('phones'? wasn't this 'books'? ;) )

Quote:

---

Originally Posted by si_the_geek

Code:

---

SELECT u1.email , u2.email
FROM phones as p
INNER JOIN users as u1 ON (p.sellerid=u1.id)
LEFT OUTER JOIN users as u2 ON (p1.buyerid=u2.id)

---

---

Thank you. That worked.

Quote:

---

('phones'? wasn't this 'books'? ;) )

---

Yes, it is books, but there are phones too. Mixed both of them :o
I don't think I can understand the cartesian product etc , now. But from what I see, we cannot have two tables in the from clause when we are joining tables.
Or, can we have two tables? Leaving out the verbosity of the statement I have, that is the only difference I can see.
Thanks again.

A Cartesian Product (also a 'FULL JOIN') is simply a join without conditions - so you get every row from one table against every row of the other (eg: every user listed with every book). So in most cases, it is definitely not what you want.


You can put any amount of tables you like in the FROM clause.. note that the Join's are all part of the FROM clause, so my example had 3 tables in there.

The only difference is that it is more specific (and thus accurate) about how you want the Joins to take place, so you are less likely to accidentally get the wrong data.

It also tends to run a bit faster too, and of course also simplifies the WHERE clause (in cases like this, completely removing it), as well as making the join conditions for each table easier to find/read.

Thanks for your time and patience. I don't know if you ever considered teaching as a career option;if you did, you would have been good at it.
I am taking the liberty of asking one more favour from you. Can you please point out where I went wrong with this

Code:

---

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)
where p.id=1;

---

Thanks in advance.

Quote:

---

Originally Posted by srisa

Thanks for your time and patience. I don't know if you ever considered teaching as a career option;if you did, you would have been good at it.

---

It's not really my thing.. I suppose I could cope with it at college level, as then the people actually want to learn rather than being forced into it! :lol:

(posting on the forums is very different, as it means I can pick what questions I answer or what tutorials/FAQs I write, and when I do it)

Quote:

---

Can you please point out where I went wrong with this

---

Well to start with, make it easier to read by putting one table on each line, ie:

Code:

---

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)

where p.id=1;

---

Now it's like this, it is easy to see that there is no link information whatsoever between u1/p and u2/p1 (you have specified u1 to p, and u2 to p1, but not u1 to u2 or p to p1), so you'll be getting a cartesian product (due to the where clause, the rows from u1/p will be restricted, but you'll get a copy of each of those for every row in u2/p1).

Note that how you originally had things (no JOIN syntax) was effectively an INNER JOIN, so that is what you should be using instead of having a comma separated list (for the reasons I listed before).


I'm not sure what you were trying to achieve with this query, so I'm not sure how it should be.. assuming you only want one copy of the phones table (so you get the buyer & seller of a particular phone), presumably as I posted before.

I wanted the seller name and buyer name from the phones table, which your query gave me.
Regarding teaching, your attitude is like a teacher on a mountain who teaches for free. If someone wants to learn , he has to approach you. Even then you will teach only if you feel like :D.
Thank you very much for the help.

:lol: That sounds about right I suppose - except the mountain is covered with people like me, and any/all of us can answer! (it would be scary if all of us answered - the thread would be huge! :eek: )