Two int columns in a join need litertal value from the same table

Question

User-297332451 posted

Select PERDATA.PERMIT_NO as [Permit No], TITLE as [Title], FORNAM1 as [Forename] ,SURNAME as [Surname] ,S_ID as [Staff Id],ADDR1 as [Address 1],ADDR2 as [Address 2],
ADDR3 as [Address 3],ADDR4 as [Address 4],PHONE as [Phone],MOBILE as [Mobile],EMAIL as [Email],COST_CEN as [Cost],COMPANY as [Company], Zone.LITERAL as [Zone] , DATE_ISSUED as [Date Issued] ,
DATE_EXPIRES as [Expiry Date], ISSUED_BY as [Issued by],
PERMIT_TYPE.LITERAL as [Permit Type] , PERMIT_STATUS.LITERAL as [Permit Status] ,CAR1_REG as [Car Registeration 1], CAR1_MAKE as [Car Make 1],
CAR1_MODEL as [Car Model 1] , CAR1_COLOUR as [Car Colour 1],
CAR2_REG as [Car Registeration 2], CAR2_MAKE as [Car Make 2] , CAR2_MODEL as [Car Model 2], CAR2_COLOUR as [Car Colour 2],
[START_DATE] as [Start Date] ,FEE_PAID as [Fee Paid], ORGANIZATION.LITERAL as [Organization Name] , NOTES as [Notes] from PERDATA Join PERMIT_HOLDER
on PERDATA.PERMIT_NO = PERMIT_HOLDER.PERMIT_NO
Join ORGANIZATION on ORGANIZATION.ORG_NO = PERMIT_HOLDER.ORG_NO
Join Zone on Zone.ZONE_NO =PERDATA.ZONE_NO
Join PERMIT_STATUS on PERMIT_STATUS.PERMIT_STATUS = PERDATA.PERMIT_STATUS
Join PERMIT_TYPE on PERMIT_TYPE.PERMIT_TYPE = PERDATA.PERMIT_TYPE

In the above query there are two columns Org_No and Company both are int and get there Literal value from a table Organization which has primary key Org_No and Literal value,

Org_No and Company can have different values How can I make a join and get both Org_No and Company to get the literal value from Organisation table

Answer 1

User-219423983 posted

Hi rohitjgc,

How can I make a join and get both Org_No and Company to get the literal value from Organisation table.

As the table “Organization” has column “Org_No” and “LITERAL” and it’s not clear that the column “Company” in which table. So, you’d better first clarify where the “Company” comes from.

Besides, could you clarify what the relationship between the “Org_No”,” Company”, “literal value” and table “Organisation”? It’s not easy to know what you want to achieve. It’s better for you to provide an image to show the Tables columns and the result that you want.

Best Regards,

Weibo Zhang

Answer 2

User-297332451 posted

There is join between Perdata and Permit_Holder tables
ORG_NO and Company are numeric(3,0) columns in Permit Holder table
They are Foreign key in the Permit Holder table and reference the
ORG_NO numeric(3,0) column in the ORGANIZATION table which is primary key in this
table.

I need to the Organisation Name from the Organisation table . ORG_NO and Company
can have different values .

Answer 3

User-219423983 posted

Hi rohitjgc,

Thank you for your response.

The “Organisation Name” is column “LITERAL” of table ORGANIZATION, right?

They are Foreign key in the Permit Holder table and reference the

 ORG_NO numeric(3,0) column in the ORGANIZATION table which is primary key in this  table.

The following is my conjecture according to the above descriptions.

Table [Permit_Holder]:

                PERMIT_NO,      ORG_NO [ForeignKey], Company

Table [ORGANIZATION]

                ORG_NO [Key],                LITERAL( The “Organisation Name”)

Table [PERDATA]

                PERMIT_NO,      ZONE_NO,         PERMIT_STATUS,                        PERMIT_TYPE

I need to the Organisation Name from the Organisation table . ORG_NO and Company can have different values .

Based on my assumption, I’m very sorry that I still couldn’t to know where the “Company” comes from and don’t why you say “ORG_NO and Company can have different values .”. If you want to get the “ORGANIZATION. LITERAL”, do you have some conditions?

If my assumption of your table structures are wrong, you could refer to the following link to share an image to explain what you want.

http://forums.asp.net/t/1660762.aspx?how+to+insert+image+in+asp+net+forum+post

Best Regards,

Weibo Zhang