Joining with different table stored procedure

Question

I have stored procedure like this

ALTER Procedure [dbo].[spCarCallFetch] @Carid nvarchar(20)
as
begin
  Select 
      k.tid, k.HBarcode, m.make, t.plateno ,v.vtype, l.locname,
      mdl.model, c.Colname 
  from 
      transaction_tbl t, KHanger_tbl k, make_tbl m, vtype_tbl v, Location_tbl l, Model_tbl mdl, Color_tbl C  
  where 
      t.tbarcode = @carid and t.mkid = m.mkid and v.vtid = t.vtid 
      and t.locid = l.locid and mdl.mdlid = t.mdlid and t.colid = c.colid 
      and t.transactID = k.transactID
end

While executing this

exec spCarCallFetch'51891044554'

Output:

tid         HBarcode   make plateno   vtype         locname        model       Colname--------------------------------------------
6           564       BMW   44554    Normal       Fashion Avenue  520          Red

here tid is taking from KeyHanger table, but correspond name of tid is saved in Terminal table, actually I want to fetch corresponding name of tid.

My tables look like this:

KHanger_tbl

transactid                              HBarcode                tid
--------------------------------------- ----------------------------------
19                                      34                      7
22                                      002                     5
21                                      1                       7
23                                      200005                   6

Terminals_tbl

tid         UniqueName
----------- --------------------------------------------------
5           Key Room-1
6           Podium -1
7           Key Room - 2

I want to take uniqueName of corresponding tid. How can I write a stored procedure for this? If anyone knows, please help me

Answer 1

Maybe something like this is what you want?

As suggested in a comment explicit joins are what should be used, and then the query should look like this:

ALTER PROCEDURE [dbo].[spCarCallFetch] @Carid NVARCHAR(20)
AS

BEGIN

  SELECT
    k.tid, k.HBarcode, m.make,
    t.plateno, v.vtype, l.locname,
    mdl.model, c.Colname, te.UniqueName

  FROM transaction_tbl t

  INNER JOIN KHanger_tbl   AS k   ON t.transactID = k.transactID
  INNER JOIN make_tbl      AS m   ON t.mkid = m.mkid 
  INNER JOIN vtype_tbl     AS v   ON v.vtid = t.vtid 
  INNER JOIN Location_tbl  AS l   ON t.locid = l.locid 
  INNER JOIN Model_tbl     AS mdl ON mdl.mdlid = t.mdlid 
  INNER JOIN Color_tbl     AS c   ON t.colid = c.colid 
  INNER JOIN Terminals_tbl AS te  ON k.tid = te.tid

  WHERE t.tbarcode = @carid 

END