0

I have 3 tables which contains related information about Users,Products and Categories 

Users
--------------
id (PK) |   user
--------------
1       Jessy
2       Emily
3       John



Products
--------------------------------------
id | user_id (FK) |  product   
--------------------------------------
1     1           iPhone
2     1           Galaxy S
3     1           xbox
4     2           PS5
5     2           MPhone
6     1           XPhone
7     3           PS3


Cateogories
---------------------------------------
id  | product_id(FK)| cateogy
---------------------------------------
1       1               Phone
2       7               Gaming Console
3       4               Gaming Console
4       5               Phone
5       2               Phone
6       3               Gaming Console
7       6               Phone

Guys,How do I get the products with categories which belongs to that user as shown below using sql?

Product List of Jessy
------
Phone
------
iPhone
Galaxy S
X Phone

------------
Gaming Console
------------
xbox
mixfuel
  • 27
  • 1
  • You've got some duplication in your Categories table. I'd suggest just having distinct values in there (two records in your case; Phone and Gaming Console), and then have a foreign key relationship to those in your products table. – Mr Moose Apr 21 '13 at 04:44
  • it'll be really helpful if you can give me any link to question or tutorial about such data relationships ? – mixfuel Apr 21 '13 at 04:50
  • Basically, just look for some info on "database normalisation". Don't over analyse it or read too much beyond 3NF initially, but at least get an understanding of the benefits given your current structure. Look at some introductory articles such as [this](http://databases.about.com/od/specificproducts/a/normalization.htm) or [this](http://en.wikipedia.org/wiki/Database_normalization). – Mr Moose Apr 21 '13 at 04:58

2 Answers2

0

You can join these tables to be able to get the required result.

select u.user, p.product, c.category
from Users u, Products p, Category c
where u.id = p.user_id
and p.id = c.product_id
and u.user = 'Jessy'
Santosh Kewat
  • 522
  • 6
  • 19
  • 2
    I'd also suggest using ANSI standard join syntax. See [this answer](http://stackoverflow.com/a/1599201/685760) for more info. – Mr Moose Apr 21 '13 at 04:47
  • 1
    @MrMoose Yeah, although the optimizer will reduce it to literally the same execution plan, I HATE reading queries that don't explicitly join – Scotch Apr 21 '13 at 06:24
0

You can do it with joining three table. Following inner join explanation example may help you.

http://www.codecandle.com/Articles/399/SQL/JOINS/INNER-JOIN/codedetail.aspx

You will need to add one more inner join to the above example for your category table.

josefh75
  • 1