Hello, I'm trying to figure out what to do here. Look at these tables:
users
id, username, password
books
id, title
users_books
bookID, userID
How would one join these together? If I for example wanted to see user 47's books?
+1
A:
If you are using SQL Server, this query should work.
select books.title
from books, users_books
where books.id = users_books.bookID and users_books.id = 47
m-sharp
2009-07-05 03:21:51
Oh, well. I hope this wasn't homework, or someone will have to help him out tomorrow as well.
John Saunders
2009-07-05 03:23:44
My opinion is that people who ask homework questions without tagging them homework should just be given the answer. I have no qualms about giving them direct answers since this has the advantage of making it harder for them to put me out of work later (since they're invariably stupid). :-)
paxdiablo
2009-07-05 03:36:51
I'd suggest you might have waited long enough for him to decide to answer. BTW, IMHO there's just as much risk of him putting you out of work. The problem is, once he's taken someone else's job, he won't be able to do it, because we've enabled him to get by without learning anything. I see a lot of questions here (though more elsewhere) by people who really shouldn't be doing those jobs (this is the polite version).
John Saunders
2009-07-05 03:42:52
A:
select b.title from users_books ub
join books b on b.id = ub.bookID
where ub.userID = 47
Joel Meador
2009-07-05 03:24:25
It has lower precedence (in mysql, at least) than an actual join. For a problem this simple, it's a matter of taste. If more tables need to be added in, being explicit can help avoid weird errors, and (for me) make it more readable.Anyway, feel free to nuke my answer if you think it's the same. :)
Joel Meador
2009-07-05 04:21:06
+2
A:
Hi
Well, if it is a homework then instead of giving fish everyday, teaching how to fish would be more helpful.
I hope the following references would aid in understanding SQL joins-
http://www.sql-tutorial.net/SQL-JOIN.asp
http://www.tizag.com/sqlTutorial/sqljoin.php
As far as your join query is concerned, response given by 'm-sharp' should give you the correct answer.
cheers
Andriyev
2009-07-05 03:39:36
Andriyev: Thanks for the fishing lessons! Lack of lessons is what I meant.
John Saunders
2009-07-05 03:45:52
A:
Since none of the other answers are joining three tables (your question heading states three but your body only needs two), I'll give you a three table version.
To do that I assume you have the username of the user rather than the ID. In that case, you can do:
select
u.id as user_id,
u.username as user_name,
b.id as book_id,
b.title as book_title
from
users u,
books b.
user_books ub
where u.username = 'username-of-user-47'
and u.id = ub.userID
and b.id = ub.bookID
This sort of thing can also be done with explicit JOINs but I prefer the "old school" SQL ways especially when the DBMS is clever enough to optimize it in the same manner, such as my beloved DB2/z :-)
paxdiablo
2009-07-05 05:18:53