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

  • 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

    Thursday, January 21, 2016 2:47 PM

All replies

  • 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

    Friday, January 22, 2016 3:01 AM
  • 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 .

    Friday, January 22, 2016 11:10 AM
  • 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

    Saturday, January 23, 2016 7:09 AM