Join tables query in SQL Server 2012

Question

I have two tables like below:

TableA --> categoryId(pk), categoryParentId, catName
TableB --> empId(pk), categoryId, empName, empDesignation

I want to get all catName with respective categoryId from TableA where categoryId=2 of TableB is equal to categoryParentId=2 of TableA. Please help.

Result:

1002   SE
1003   MD

Answer 1

Something like this?

SELECT DISTINCT catName FROM TABLEA WHERE categoryParentId IN (SELECT DISTINCT categoryID from TABLEB);

Answer 2

Solution with INNER JOIN:

select TableA.catName
from TableA
inner join TableB on TableA.categoryParentId = TableB.categoryId

Using INNER JOIN is the recommended way to use for joining tables (as opposed to SELECT ... FROM TableA, TableB ...)

See:

• INNER JOIN ON vs WHERE clause
  (this question was actually about MySql, but the answers say that it's the same for SQL Server)
• Difference in INNER join and cartesian join in SQL Server

---

Answering your comment (I assume that you wanted the categoryId from TableA):

select TableA.catName, TableA.categoryId
from TableA
inner join TableB on TableA.categoryParentId = TableB.categoryId
where TableB.categoryId = 2

Answer 3

I hope this query will fulfill your need

    SELECT TABLEA.CATNAME AS CATEGORY
    from TABLEB INNER JOIN TABLEA 
    ON TABLEA.CATEGORYPARENTID = TABLEB.CATEGORYID

Answer 4

another method...

SELECT 
  catname
from
  tablea a INNER JOIN
  tableb b ON 
    a.categoryParentID = b.categoryID
  group by
  catname

Answer 5

This should do the trick

SELECT tableA.catName
 FROM tableA, tableB
WHERE tableB.categoryId = table1.categoryParentId
  AND tableB.categoryId = 2;

If you want the unique catNames, you can use

SELECT distinct tableA.catName
 FROM tableA, tableB
WHERE tableB.categoryId = table1.categoryParentId
  AND tableB.categoryId = 2;