CODEWITHSUNDEEP
sql-serverselectsubquery
Mashhoor Gulati
Mashhoor Gulati

Reputation: 127

Display corresponding name based on ID SQL select query

I have 2 tables

tbl_jobs

CREATE TABLE [dbo].[tbl_jobs]
(
    [JobID] [int] IDENTITY(1,1) NOT NULL,
    [JobType] [nvarchar](50) NOT NULL,
    [RequestID] [int] NOT NULL,
    [AssignTo] [int] NOT NULL,
    [FromOrgID] [int] NOT NULL,
    [ToOrgID] [int] NOT NULL,
    [Ammount] [nvarchar](50) NOT NULL,
    [JobStatus] [nvarchar](50) NOT NULL,
    [Remark] [nvarchar](50) NULL,
    [strOwner] [nvarchar](50) NOT NULL,
    [dbTstamp] [datetime2](7) NOT NULL,

    CONSTRAINT [PK_tbl_jobs] 
        PRIMARY KEY CLUSTERED ([JobID] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_jobs] WITH CHECK 
    ADD CONSTRAINT [FK_tbl_jobs_tbl_orgs] 
    FOREIGN KEY([FromOrgID]) REFERENCES [dbo].[tbl_orgs] ([OrgID])
GO

ALTER TABLE [dbo].[tbl_jobs] CHECK CONSTRAINT [FK_tbl_jobs_tbl_orgs]
GO

ALTER TABLE [dbo].[tbl_jobs]  WITH CHECK 
    ADD CONSTRAINT [FK_tbl_jobs_tbl_orgs1] 
    FOREIGN KEY([ToOrgID]) REFERENCES [dbo].[tbl_orgs] ([OrgID])
GO

tbl_orgs

CREATE TABLE [dbo].[tbl_orgs]
(
    [OrgID] [int] IDENTITY(1,1) NOT NULL,
    [OrgName] [nvarchar](50) NOT NULL,
    [OrgTele] [nvarchar](50) NULL,
    [OrgEmail] [nvarchar](50) NULL,
    [OrgArea] [nvarchar](50) NOT NULL,
    [OrgCity] [nvarchar](50) NOT NULL,
    [OrgLocation] [nvarchar](50) NOT NULL,
    [OrgType] [nvarchar](50) NOT NULL,
    [OrgStatus] [nvarchar](50) NOT NULL,
    [strOwner] [nvarchar](50) NOT NULL,
    [dbTStamp] [datetime2](7) NOT NULL,

    CONSTRAINT [PK_tbl_orgs] 
        PRIMARY KEY CLUSTERED ([OrgID] ASC)
) ON [PRIMARY]
GO

I need to get most of the tbl_jobs columns and corresponding tbl_orgs.OrgName for tbl_jobs.FromOrgID & tbl_jobs.ToOrgID.

If I choose tbl_orgs.orgname, I don't get the proper results.

I am stuck here. What type of query should I use to get the result.?

enter image description here

Upvotes: 1

Views: 370

Answers (1)

Kromas
Kromas

Reputation: 26

You're joining two times to the same table instance. You should click on "add table" and add tbl_orgs one more time and join "FromOrgID" to one instance of it and "ToOrgID" to the other one. Otherwise the join doesn't make sense unless "FromOrgID" and "ToOrgID" are equal.

Upvotes: 1

Related Questions