How to get value from two tables in sql server

Question

I have two tables First is tbcategory enter image description here

Second is tbcompany enter image description here

I want to retrieve from both tables in such a way that common image from tbcompany and all data from tbcategory according to the foreign key

here is an example that i want

enter image description here

here my query in sql server

   select cat.category, comp.imagename  from tb_category as cat
   inner join tb_company as comp
   on cat.companyid=comp.id

///////

  i am getting result like this

enter image description here

   and want result like this

enter image description here

The only thing that appears to be missing from your query, based on my understanding, is selecting cat.url in addition to the other fields selected. Can you explain what your query is returning, and what you want it to return? — Sam DeHaan, Apr 03 '14 at 13:09
i want single image of company and under that image there should be the category regarding that company right now it display all logo and category — Kanwar Singh, Apr 03 '14 at 13:12
Can you explain that by editing into your post a sample result set you are getting, and the result set you would like to get? I still do not understand what's missing. — Sam DeHaan, Apr 03 '14 at 13:17
Can you post the table source data example rather than the table schema definitions please? — Mike, Apr 03 '14 at 13:27

score 1 · Answer 1 · answered Apr 03 '14 at 13:27

1

Try this:

   select comp.imagename, cat.category
   from tb_company comp
   join tb_category cat on cat.companyid=comp.id

answered Apr 03 '14 at 13:27

Andrey Morozov

Sam DeHaan · Accepted Answer · 2014-04-03T13:34:59.773

Based on quite a few assumptions - it appears that your data schema is incorrect.

You want to have tbcategory have one record per category

tbcategory: id, category, url

and tbcompany have a reference to the company's category

tbcompany: id, categoryid, name, imagename

And then your query would be

select comp.imagename, cat.category, cat.url
from tb_company comp
inner join tb_category cat on comp.categoryid = cat.id

This would return data like the following, which appears to be what you want:

imagename    category   url
comp1logo    cat1       http://cat1url
comp2logo    cat1       http://cat1url
comp3logo    cat2       http://cat2url

score 0 · Answer 3 · answered May 02 '14 at 09:26

0

 select comp.imagename, cat.category
  from tb_company comp
 join tb_category cat on cat.companyid=comp.id

answered May 02 '14 at 09:26

Kanwar Singh

3 Answers3