CODEWITHSUNDEEP
sqlsql-serversql-server-2008joinsql-server-2008-r2
Urbley
Urbley

Reputation: 716

One-to-Many Join

I have three tables in my DB - APPLICATION, APPLICANT and ADDRESS

There is 1 row in APPLICATION.

APPLICANT can have 1 or 2 rows linked back to APPLICATION via APPLICATION_ID.

ADDRESS can have 1, 2 or 3 rows linked back to APPLICANT via APPLICANT_ID.

APPLICATION -> (1-to-many on APPLICATION_ID) -> APPLICANT -> (1-to-many on APPLICANT_ID) -> ADDRESS

I need to write a query which extracts specific fields from each table (changed from 'all information') into 1 result set. The result needs to contain all possible info for each application in one result row. Can someone please point me in the direction of the best solution for this?

I hope the question is clear. I searched through SO already but could only really find some case specific answers and nothing general regarding 1-to-many joins.

OK I thought I should elaborate a little to help anyone who actually takes the time to have a think about this. Here's some example dummy data from all three tables.

APPLICATION
-----------
APPLICATION_ID|APP1|APP2|OTHER_STUFF
1             |1   |1   |x

APPLICANT
---------
APPLICANT_ID|APPLICATION_ID|FORENAME|OTHER_STUFF
1           |1             |Homer   |x
2           |1             |Marge   |x

ADDRESS
-------
ADDRESS_ID|APPLICANT_ID|STREET           |OTHER_STUFF
1         |1           |Sesame Street    |x
2         |1           |Evergreen Terrace|x
3         |2           |Evergreen Terrace|x

The result from the SQL query would look something like this (hopefully);

APPLICATION_ID|APPLICANT_ID1|FORENAME1|ADDRESS_ID1|STREET1      |ADDRESS_ID2|STREET2          |APPLICANT_ID2|FORENAME_2|ADDRESS_ID3|STREET3
1             |1            |Homer    |1          |Sesame Street|2          |Evergreen Terrace|2            |Marge     |3          |Evergreen Terrace

Thanks

Upvotes: 0

Views: 101

Answers (3)

VikrantMore
VikrantMore

Reputation: 903

You will surely get it working with the following query. Hope this helps you.

SELECT * FROM APPLICATION as App

INNER JOIN APPLICANT as A1 on A1.APPLICATION_ID = App.APPLICATION_ID

INNER JOIN ADDRESS as A2 on A2.APPLICANT_ID = A1.APPLICANT_ID

Upvotes: 2

gvee
gvee

Reputation: 17161

; WITH applicants AS (
  SELECT applicant_id
       , application_id
       , forename
       , other_stuff
       , Row_Number() OVER (PARTITION BY application_id ORDER BY applicant_id) As sequence
  FROM   applicant
)
, addresses AS (
  SELECT address_id
       , applicant_id
       , street
       , other_stuff
       , Row_Number() OVER (PARTITION BY applicant_id ORDER BY address_id) As sequence
  FROM   address
)
SELECT application.application_id
     , first_applicants.applicant_id As applicant_id1
     , first_applicants.forename As forename1
     , first_applicants_first_addresses.address_id As address_id1
     , first_applicants_first_addresses.street As street1
     , first_applicants_second_addresses.address_id As address_id2
     , first_applicants_second_addresses.street As street2
     , second_applicants.applicant_id As applicant_id2
     , second_applicants.forename As forename2
     , second_applicants_first_addresses.address_id As address_id3
     , second_applicants_first_addresses.street As street3
     , second_applicants_second_addresses.address_id As address_id4
     , second_applicants_second_addresses.street As street4     
FROM   application
 LEFT
  JOIN applicants As first_applicants
    ON first_applicants.application_id = application.application_id
   AND first_applicants.sequence = 1
 LEFT
  JOIN addresses As first_applicants_first_addresses
    ON first_applicants_first_addresses.applicant_id = first_applicants.applicant_id
   AND first_applicants_first_addresses.sequence = 1
 LEFT
  JOIN addresses As first_applicants_second_addresses
    ON first_applicants_second_addresses.applicant_id = first_applicants.applicant_id
   AND first_applicants_second_addresses.sequence = 2
 LEFT
  JOIN applicants As second_applicants
    ON second_applicants.application_id = application.application_id
   AND second_applicants.sequence = 2
 LEFT
  JOIN addresses As second_applicants_first_addresses
    ON second_applicants_first_addresses.applicant_id = second_applicants.applicant_id
   AND second_applicants_first_addresses.sequence = 1
 LEFT
  JOIN addresses As second_applicants_second_addresses
    ON second_applicants_second_addresses.applicant_id = second_applicants.applicant_id
   AND second_applicants_second_addresses.sequence = 2
WHERE  application.application_id = 1
;

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

select APPLICATION.*, APPLICANT.*, ADDRESS.* 
  from APPLICATION
  join APPLICANT 
    on APPLICATION.APPLICATION_ID = APPLICANT.APPLICATION_ID
  join ADDRESS
    on APPLICANT.APPLICATION_ID = APPLICANT.APPLICATION_ID

Upvotes: 0

Related Questions