CODEWITHSUNDEEP
sqloracle-database
Swadeep Mohanty
Swadeep Mohanty

Reputation: 289

How to work in case in join condition

enter image description here

How to find city when ContactID is provided and condition is if ContactID is coming as 123 then it will look whether it is P or C, If P then it will go to Person table and returns City(USA) as output and If C then it will go to Company table and gives City(AUS) as output. NB: all tables contain thousands of record and City value comes from run time.

Upvotes: 0

Views: 63

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Ben's answer is almost right. You might want to check that the first join has no match before doing the second one:

select c.*, coalesce(p.name, c.name) as p.name
from contacts c left outer join
     person p
     on c.contactid = p.contactid and
        c.person_company = 'P' left join
     company co
     on c.contactid = co.contactid and
        c.person_company = 'C' and
        p.contactid is null;

This may not be important in your case. But in the event that the second join matches multiple rows and the first matches a single row, you might not want the additional rows in the output.

Upvotes: 1

Ben
Ben

Reputation: 52893

Unless you're dynamically generating the query (i.e. using some language other than SQL to execute it) then you need to join on both tables anyway. If you're joining on both tables then there's no need for a CASE statement:

select *
  from contacts co
  left outer join person p
    on co.contactid = p.contactid
   and co.person_company = 'P'
  left outer join company c
    on co.contactid = c.contactid
   and co.person_company = 'C'

You'll start noting an issue here, for every column from PERSON and COMPANY you're going to have to add some business logic to work out which table you want the information from. This can get very tiresome

select co.contactid
     , case when p.id is not null then p.name else c.name end as name 
  from contacts co
  left outer join person p
    on co.contactid = p.contactid
   and co.person_company = 'P'
  left outer join company c
    on co.contactid = c.contactid
   and co.person_company = 'C'

Your PERSON and COMPANY tables seem to have exactly the same information in them. If this is true in your actual data model then there's no need to split them up. You make the determination as to whether each entity is a person or a company in your CONTACTS table.

Creating additional tables to store data in this manner is only really helpful if you need to store additional data. Even then, I'd still put the data that means the same thing for a person or a companny (i.e. name or address) in a single table.

If there's a 1-2-1 relationship between CONTACTID and PID and CONTACTID and CID, which is what your sample data implies, then you have a number of additional IDs, which have no value.

Lastly, if you're not restricting that only companies can go in the COMPANY table and individuals in the PERSON table. You need the PERSON_COMPANY column to exist in both PERSON and COMPANY, though as a fixed string. It would be more normal to set up this data model as something like the following:

create table contacts (
    id integer not null
  , contact_type char(1) not null
  , name varchar2(4000) not null
  , city varchar2(3)
  , constraint pk_contacts primary key (id)
  , constraints uk_contacts unique (id, contact_type)
    ); 

create table people (
    id integer not null
  , contact_type char(1) not null
  , some_extra_info varchar2(4000)
  , constraint pk_people primary key (id)
  , constraint fk_people_contacts
      foreign key (id, contact_type)
      references contacts (id, contact_type)
  , constraint chk_people_type check (contact_type = 'P')
   );

etc.

Upvotes: 3

Z .
Z .

Reputation: 12837

you can LEFT JOIN all 3 tables and the using a CASE statement select the one that you need based on the P or C value

SELECT 
  CASE c.[Person/Company] 
    WHEN 'P' THEN p.NAME 
    WHEN 'C' THEN a.Name 
  END AS Name
FROM Contact c
LEFT JOIN Person p on p.ContactId = c.ContactId
LEFT JOIN Company a on a.ContachId = c.ContactId

Upvotes: 1

Related Questions