none
Using conditions in joins

    Question

  • I've below 3 tables from which need to fetch address for the records.

    1. Address AD
     ---> It has all the address values(addrline1,addrline2,state,etc..) alongwith AddressID column

    2. Address_Employee AE
     ---> It has EmpID,AddressID,AddressTypeID columns
    There can be multiple instances of same EmpID with different AddressID and AddressTypeID value as either 3 or 1

    3. Address_Type AT
     ---> There are 2 rows, with AddressTypeID and AddressType columns. AddressTypeID value with 1 will be Home and 3 will be Postal.

    Need to fetch records in such a way that first need to look for Postal address, if not available then Home.

    I'm using the query:

    SELECT (AD.sAddressLine1+' '+AD.sAddressLine2+' '+AD.sSuburb+' '+AD.sPostCode) AS Address
    from Address AD
    INNER JOIN Address_Employee AE ON AD.AddressID = AE.AddressID
    INNER JOIN Address_Type AT ON AE.AddressTypeID= AT.AddressTypeID


    But how can I apply the condition: "first need to look for Postal address, if not available then Home."

    Please advise.

    Thanks, Krishna

    Wednesday, September 18, 2013 9:14 AM

Answers

  • Pseudo Code:

    ;With cte
    AS
    (Select *,ROW_NUMBER()Over(PARTITION by EmpID Order by AddressTypeID desc) Rn  From
    Address_Employee Where AddressTypeID in (3,1)
    )
    Select A.*,B.* From cte A
    Inner Join AddressTable B On A.AddressID=B.AddressID
    Where Rn=1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 18, 2013 9:40 AM
  • You'll actually want to sort the rows in ascending order to get the Home address if it exists.

     ;WITH tbl1 AS
    (
       SELECT *,
             ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY AddressTypeID ASC) AS rn
       FROM Address_Employee Where AddressTypeID in (3,1)

    )
    SELECT (AD.sAddressLine1+' '+AD.sAddressLine2+' '+AD.sSuburb+' '+AD.sPostCode) AS Address
     from Address AD
     INNER JOIN tbl1 ON AD.AddressID = tbl1.AddressID
    WHERE tbl1.rn = 1


    Martina White

    Monday, September 23, 2013 1:43 AM

All replies

  • SELECT (AD.sAddressLine1+' '+AD.sAddressLine2+' '+AD.sSuburb+' '+ISNULL(AD.sPostCode,AD.Home)) AS Address
    from Address AD
    INNER JOIN Address_Employee AE ON AD.AddressID = AE.AddressID 
    INNER JOIN Address_Type AT ON AE.AddressTypeID= AT.AddressTypeID
    Are you looking for this ?

    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Wednesday, September 18, 2013 9:21 AM
  • Hi Krishna,

    You can use CASE statement in select.

    SELECT  CASE WHEN AD.sAddressLine1 IS NULL THEN AD.sAddressLine2
    WHEN AD.sAddressLine2 IS NULL THEN AD.sPostCode
    FROM TABLE

    We cant put this condition in JOIN.

    - Pls mark as answer / vote, if this post is helpful.

    Sugumar Pannerselvam.

    Wednesday, September 18, 2013 9:21 AM
  • Please post DDL along with sample data

    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Wednesday, September 18, 2013 9:28 AM
  • Pseudo Code:

    ;With cte
    AS
    (Select *,ROW_NUMBER()Over(PARTITION by EmpID Order by AddressTypeID desc) Rn  From
    Address_Employee Where AddressTypeID in (3,1)
    )
    Select A.*,B.* From cte A
    Inner Join AddressTable B On A.AddressID=B.AddressID
    Where Rn=1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 18, 2013 9:40 AM
  • You'll actually want to sort the rows in ascending order to get the Home address if it exists.

     ;WITH tbl1 AS
    (
       SELECT *,
             ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY AddressTypeID ASC) AS rn
       FROM Address_Employee Where AddressTypeID in (3,1)

    )
    SELECT (AD.sAddressLine1+' '+AD.sAddressLine2+' '+AD.sSuburb+' '+AD.sPostCode) AS Address
     from Address AD
     INNER JOIN tbl1 ON AD.AddressID = tbl1.AddressID
    WHERE tbl1.rn = 1


    Martina White

    Monday, September 23, 2013 1:43 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. You have not learned the huge difference between records and rows. You do not know that "type_id" is a violation of ISO-11179 and silly. You format data in the Data Base. Bad manner and  ignorance of basics is not going to help your career. Want to b e polite and try again?? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, September 23, 2013 2:59 AM