CODEWITHSUNDEEP
sql
Will Bachrach
Will Bachrach

Reputation: 119

Selecting Columns after joining two tables

I am trying to select 'contractkey' after joining the two tables. All I can run now is "select *" and then all columns are returned. Below is the query. It runs perfectly as long as I do not try to select a column. How can I select a specific column? do I need to to label the table after the INNER JOIN?

Select * 
FROM 
(SELECT
    attachmentdata.contractkey,
    requirementinfo.reqcode,
    requirementinfo.parentidkey as 'ReqInfoParentID',
    max(attachment.datecreated) as datecreated,
    applicationinfo.hocompletiondate

FROM  location1.attachment

LEFT JOIN location1.attachmentdata
    on  attachment.contractkey = attachmentdata.contractkey
    and attachment.id = attachmentdata.parentidkey --Links Attachment Type

LEFT JOIN location1.requirementinfo
    on  attachment.contractkey = requirementinfo.contractkey
    and attachment.parentidkey = requirementinfo.id --Links Attachment Type

INNER JOIN location1.applicationinfo
    on  attachment.contractkey = applicationinfo.contractkey
    and requirementinfo.parentidkey = applicationinfo.parentidkey --Links Policy Type

where
    (requirementinfo.reqcode = 2) 

group by attachmentdata.contractkey,requirementinfo.reqcode, 
requirementinfo.parentidkey, applicationinfo.hocompletiondate) as table1

INNER JOIN

(SELECT
    attachmentdata.contractkey,
    requirementinfo.reqcode,
    requirementinfo.parentidkey as 'ReqInfoParentID',
    attachment.datecreated,
    applicationinfo.hocompletiondate,
    attachmentdata.pcdata

FROM  location1.attachment

LEFT JOIN location1.attachmentdata
    on  attachment.contractkey = attachmentdata.contractkey
    and attachment.id = attachmentdata.parentidkey --Links Attachment Type

LEFT JOIN location1.requirementinfo
    on  attachment.contractkey = requirementinfo.contractkey
    and attachment.parentidkey = requirementinfo.id --Links Attachment Type

INNER JOIN location1.applicationinfo
    on  attachment.contractkey = applicationinfo.contractkey
    and requirementinfo.parentidkey = applicationinfo.parentidkey)table2

on table1.contractkey = table2.contractkey
and table1.reqcode = table2.reqcode
and table1.ReqInfoParentID = table2.ReqInfoParentID
and table1.datecreated = table2.datecreated
and table1.hocompletiondate = table2.hocompletiondate

Upvotes: 0

Views: 3648

Answers (3)

Ehsan
Ehsan

Reputation: 785

It is not required to "Label the table"(Alias). Use Case to make sure it won't put null for records that are not in the AttachmentData table as follow:

Select *  FROM 
(SELECT case 
   WHEN attachmentdata.contractkey IS NOT NULL THEN attachmentdata.contractkey 
   else attachment.contractkey END AS contractkey,

    requirementinfo.reqcode,
    requirementinfo.parentidkey as 'ReqInfoParentID',
    max(attachment.datecreated) as datecreated,
    applicationinfo.hocompletiondat
    ...

Upvotes: 0

Eric
Eric

Reputation: 3257

There are columns contractkey in both tables, so the system doesn't know which ones you want. So you will have to put tablename in front of it.

SELECT table1.contractkey, ...

Upvotes: 1

Stavr00
Stavr00

Reputation: 3314

Add the following to the end of your outermost inner table selects:

SELECT contractkey FROM
(SELECT
    attachmentdata.contractkey,

          etc...

) AS t(contractkey,reqcode,ReqInfoParentID,datecreated,hocompletiondate)

Upvotes: 0

Related Questions