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
– Sam DeHaan, Commented Apr 3, 2014 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
– Kanwar Singh, Commented Apr 3, 2014 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
– Sam DeHaan, Commented Apr 3, 2014 at 13:17
Can you post the table source data example rather than the table schema definitions please? — Mike
– Mike, Commented Apr 3, 2014 at 13:27

Sam DeHaan · Accepted Answer · 2014-04-03 13:34:59Z

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

Andrey Morozov · Accepted Answer · 2014-04-03 13:27:35Z

Try this:

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

Kanwar Singh · Accepted Answer · 2014-05-02 09:26:11Z

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

3 Answers 3