select result from three tables using sql

Question

I want to get a result from three tables. i dont how to write the sql query. Please help me.

"I want to display Name,Username and Product_Name where Id=007"

table "register"

Name  Username  Id
Arj   arjun     007
xyz   abcd      008
abcd  asdf      007

table "products"

Product_Id   Product_Name   Price
101           Clothes        200
102           Games          100

table "purchase"     //products.Product_Id=purchase.Item

Username   item    Id
arjun      102     007
abcd       101     008
asdf       102     007

If you are learning SQL, you should really show what you have tried. This is a fairly basic statement. — Gordon Linoff, Feb 05 '13 at 16:36
Look up INNER JOIN and WHERE. If you still have trouble, post back here. — PinnyM, Feb 05 '13 at 16:39
With those duplicate IDs and the duplication of names across the tables, I'm not even sure what you'd consider a correct result. Could you add the desired result to the question? — Joachim Isaksson, Feb 05 '13 at 16:40
You should also always state your DBMS (Oracle, Postgres, ...) as some answers might depend on the specific SQL dialect. — , Feb 05 '13 at 17:39

score 0 · Answer 1 · answered Feb 05 '13 at 16:39

Try this query :

SELECT a.Name,a.Username,c.Product_Name
FROM  register as a
JOIN purchase as b on a.Username=b.Username
JOIN products as c on b.item=c.Product_Id

It should work like this. To read more on SQL Joins try : http://www.w3schools.com/sql/sql_join.asp

Enjoy!

Patrick Kostjens · Answer 2 · 2013-02-05T18:37:21.767

0

SELECT Username, Id, Product_Name 
FROM register 
INNER JOIN purchase ON purchase.Id = register.Id 
INNER JOIN products ON purchase.Item = products.Product_Id
WHERE Id = '007'

As a side note: you shouldn't save the Username in the purchase table too.

edited Feb 05 '13 at 18:37

answered Feb 05 '13 at 16:40

Patrick Kostjens

5,065
6
29
46

score 0 · Answer 3 · answered Feb 05 '13 at 16:43

0

That should be like this

SELECT * FROM
register r
JOIN purchase p on p.username = r.username
JOIN products pr on pr.product_id = p.item

you can revise this basic code to include specific columns and add where and order clause.

answered Feb 05 '13 at 16:43

jamesTheProgrammer

1,747
4
22
34

select result from three tables using sql

3 Answers3