Home » SQL & PL/SQL » SQL & PL/SQL » Conditional Join Help
Conditional Join Help [message #254476] Thu, 26 July 2007 19:50 Go to next message
vsao
Messages: 7
Registered: July 2007
Location: Connecticut
Junior Member
Hi all I am trying to get a conditional join to work in Oracle and can not come up with the statement. I have 2 tables, both contains fields: client, acc, cc. Table 2 contains a desc field and is a lookup table for categories based on acc, cc but is different per client. The joins will be on acc and cc as well as client BUT if the client is not in the look up table (Table 2) then I want the join to be on client 0000 in the lookup table. Please contact me with any questions and I sorry for any posting issues. This is my first post. Thanks for any help in advance.

Table 1:

client acc cc
1000 01 01
1000 01 02
1001 01 01
1001 01 02
1002 01 01
1002 01 02

Table 2:

client acc cc desc
1000 01 01 supplies
1000 01 02 food
1001 01 01 tents
1001 01 02 camping equipment
0000 01 01 misc1
0000 01 02 misc2

Report for client 1002 should be:

client acc cc desc
1002 01 01 misc1
1002 01 02 misc2

[Updated on: Thu, 26 July 2007 21:58]

Report message to a moderator

Re: Conditional Join Help [message #254479 is a reply to message #254476] Thu, 26 July 2007 20:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow the posting guidelines as stated in the STICKY posts at the top of this forum.
Re: Conditional Join Help [message #254481 is a reply to message #254479] Thu, 26 July 2007 21:09 Go to previous messageGo to next message
vsao
Messages: 7
Registered: July 2007
Location: Connecticut
Junior Member
thanks for the info ana, i read it and wasnt sure if i needed to put table examples into the code. If needed I will do that. Thanks again for the info.
Re: Conditional Join Help [message #254484 is a reply to message #254476] Thu, 26 July 2007 21:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What about "Show us what you did (if you tried it yourself), including errors and/or why the result is not what you want."?
Re: Conditional Join Help [message #254489 is a reply to message #254484] Thu, 26 July 2007 21:50 Go to previous messageGo to next message
vsao
Messages: 7
Registered: July 2007
Location: Connecticut
Junior Member
Here here the sql I am using.

select t1.client, t1.acc, t1.cc, t2.desc
from table1 t1, table2 t2
where t1.acc = t2.acc and t1.cc = t2.cc and nvl(t1.client, 0000) = t2.client


someone at my job suggested to use NVL to return 0000 if the client number is not in the lookup table. When I use the above and add the following where statement it returns:

client acc cc desc
1000 01 01 supplies
1000 01 02 food

select t1.client, t1.acc, t1.cc, t2.desc
from table1 t1, table2 t2
where t1.acc = t2.acc and t1.cc = t2.cc and nvl(t1.client, 0000) = t2.client and t1.client=1000


but when I try to get client 1002 it doesnt return any records but i want it to return client 0000 records from the lookup table (misc1, misc2)

select t1.client, t1.acc, t1.cc, t2.desc
from table1 t1, table2 t2
where t1.acc = t2.acc and t1.cc = t2.cc and nvl(t1.client, 0000)=t2.client and t1.client=1002


Maybe what I am trying to accomplish is not possible but any insight is appreciated. Thanks.
Re: Conditional Join Help [message #254491 is a reply to message #254476] Thu, 26 July 2007 23:14 Go to previous messageGo to next message
ravik_mca03
Messages: 3
Registered: July 2007
Junior Member
hi check this it would work properly

select t1.client,t1.acc,t1.cc,t2,desc from table1 t1,table2 t2 where t1.cc=t2.cc and t2.acc=t1.acc and nvl(t1.client,'0000')=t2.client;

[Updated on: Fri, 27 July 2007 04:49]

Report message to a moderator

Re: Conditional Join Help [message #254519 is a reply to message #254491] Fri, 27 July 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think a SELECT without any FROM clause will work.

Regards
Michel
Re: Conditional Join Help [message #254556 is a reply to message #254491] Fri, 27 July 2007 02:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
0000 is a number and will be returned as 0
If you actually want "0000", either convert the outcome to a string and lpad it with leading zeroes, or use the string '0000'
Re: Conditional Join Help [message #254649 is a reply to message #254556] Fri, 27 July 2007 06:31 Go to previous messageGo to next message
vsao
Messages: 7
Registered: July 2007
Location: Connecticut
Junior Member
client contains a number (decimal format) and is stored in the lookup table as 0. I updated the statement to:

select t1.client, t1.acc, t1.cc, t2.desc
from table1 t1, table2 t2
where t1.acc = t2.acc and t1.cc = t2.cc and nvl(t1.client, 0)=t2.client and t1.client=1002


but it still returns no records. My guess is that the condition nvl(t1.client,0) never returns a NULL value therefore the join is only valid when t1.client = t2.client.
Re: Conditional Join Help [message #254652 is a reply to message #254649] Fri, 27 July 2007 06:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
where (t1.client = t2.client
      or (   t2.client = 0
         and not exists (select 1
                         from   table2 t2a
                         where  t2a.client = t1.client
                        )
         )
      )



But what if you have more than 1 client in table1 that has no matching client in t2?

[Updated on: Fri, 27 July 2007 06:37]

Report message to a moderator

Re: Conditional Join Help [message #254663 is a reply to message #254476] Fri, 27 July 2007 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select t1.client, t1.acc, t1.cc, t2.descr,
  4             row_number() 
  5               over (partition by t1.client, t1.acc, t1.cc 
  6                     order by t2.client desc) rn
  7      from t1, t2
  8      where t2.client in (t1.client, '0000') 
  9        and t2.acc = t1.acc
 10        and t2.cc = t1.cc
 11    )
 12  select client, acc, cc, descr
 13  from data
 14  where rn = 1
 15  order by client, acc, cc
 16  /
CLIE AC CC DESCR
---- -- -- ------------------------------
1000 01 01 supplies
1000 01 02 food
1001 01 01 tents
1001 01 02 camping equipment
1002 01 01 misc1
1002 01 02 misc2

6 rows selected.

Regards
Michel
Re: Conditional Join Help [message #254704 is a reply to message #254652] Fri, 27 July 2007 10:38 Go to previous messageGo to next message
vsao
Messages: 7
Registered: July 2007
Location: Connecticut
Junior Member
Thanks for the help Frank, that is the case. I will have possibly 50 clients in table 1 and only 10 will match and have their own description. The rest should pull the description from client 0 since the client is not in table 2. Does that make sense? Thanks again for the help all.

Michel - I tried the IN statement in the WHERE clause both ways (see below) and this is what happened.

Added WHERE clause t1.client = 1002 and it gave me the right results.
SQL> with 
  2    data as (
  3      select t1.client, t1.acc, t1.cc, t2.descr,
  4             row_number() 
  5               over (partition by t1.client, t1.acc, t1.cc 
  6                     order by t2.client desc) rn
  7      from t1, t2
  8      where t2.client in (t1.client, 0) 
  9        and t2.acc = t1.acc
 10        and t2.cc = t1.cc and t1.client = 1002
 11    )
 12  select client, acc, cc, descr
 13  from data
 14  where rn = 1
 15  order by client, acc, cc
 16  /
CLIE AC CC DESCR
---- -- -- ------------------------------
1002 01 01 misc1
1002 01 02 misc2


But when I tried to get client 1000 it gave me 4 desc total. The 1000 ones and the 0 ones.
SQL> with 
  2    data as (
  3      select t1.client, t1.acc, t1.cc, t2.descr,
  4             row_number() 
  5               over (partition by t1.client, t1.acc, t1.cc 
  6                     order by t2.client desc) rn
  7      from t1, t2
  8      where t2.client in (t1.client, 0) 
  9        and t2.acc = t1.acc
 10        and t2.cc = t1.cc and t1.client = 1000
 11    )
 12  select client, acc, cc, descr
 13  from data
 14  where rn = 1
 15  order by client, acc, cc
 16  /
CLIE AC CC DESCR
---- -- -- ------------------------------
1000 01 01 supplies
1000 01 02 food
1000 01 01 misc1
1000 01 02 misc2


When I switched the IN statement [t1.client in (t2.client, 0)] around it gave me the right results for client 1002 (returned the 0 record desc)
SQL> with 
  2    data as (
  3      select t1.client, t1.acc, t1.cc, t2.descr,
  4             row_number() 
  5               over (partition by t1.client, t1.acc, t1.cc 
  6                     order by t2.client desc) rn
  7      from t1, t2
  8      where t1.client in (t2.client, 0) 
  9        and t2.acc = t1.acc
 10        and t2.cc = t1.cc and t1.client = 1002
 11    )
 12  select client, acc, cc, descr
 13  from data
 14  where rn = 1
 15  order by client, acc, cc
 16  /
CLIE AC CC DESCR
---- -- -- ------------------------------
1002 01 01 misc1
1002 01 02 misc2


but when I tried to pull client 1000 it returned zero results.
SQL> with 
  2    data as (
  3      select t1.client, t1.acc, t1.cc, t2.descr,
  4             row_number() 
  5               over (partition by t1.client, t1.acc, t1.cc 
  6                     order by t2.client desc) rn
  7      from t1, t2
  8      where t1.client in (t2.client, 0) 
  9        and t2.acc = t1.acc
 10        and t2.cc = t1.cc and t1.client = 1000
 11    )
 12  select client, acc, cc, descr
 13  from data
 14  where rn = 1
 15  order by client, acc, cc
 16  /
CLIE AC CC DESCR
---- -- -- ------------------------------



Let me know if this is unclear and I will try and explain it better. Thanks.
Re: Conditional Join Help [message #254707 is a reply to message #254704] Fri, 27 July 2007 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I get the correct result:
SQL> l
  1  with
  2    data as (
  3      select t1.client, t1.acc, t1.cc, t2.descr,
  4             row_number()
  5               over (partition by t1.client, t1.acc, t1.cc
  6                     order by t2.client desc) rn
  7      from t1, t2
  8      where t2.client in (t1.client, '0000')
  9        and t2.acc = t1.acc
 10        and t2.cc = t1.cc
 11        and t1.client = '1000'
 12    )
 13  select client, acc, cc, descr
 14  from data
 15  where rn = 1
 16* order by client, acc, cc
SQL> /

CLIE AC CC DESCR
---- -- -- ------------------------------
1000 01 01 supplies
1000 01 02 food

2 rows selected.

SQL> l
  1  with
  2    data as (
  3      select t1.client, t1.acc, t1.cc, t2.descr,
  4             row_number()
  5               over (partition by t1.client, t1.acc, t1.cc
  6                     order by t2.client desc) rn
  7      from t1, t2
  8      where t2.client in (t1.client, '0000')
  9        and t2.acc = t1.acc
 10        and t2.cc = t1.cc
 11        and t1.client = '1002'
 12    )
 13  select client, acc, cc, descr
 14  from data
 15  where rn = 1
 16* order by client, acc, cc
SQL> /

CLIE AC CC DESCR
---- -- -- ------------------------------
1002 01 01 misc1
1002 01 02 misc2

2 rows selected.

Of course, I used the data you posted and assumed all fields are varchar2.
I don't know the data you have and your table description.

Regards
Michel
Re: Conditional Join Help [message #254721 is a reply to message #254707] Fri, 27 July 2007 13:02 Go to previous messageGo to next message
vsao
Messages: 7
Registered: July 2007
Location: Connecticut
Junior Member
My appoligies Michel for the unclear example. Here is hopefully a clearer picture.

table 1 should pull the desc from table 2 based on 2 main joins t1.acc = t2.acc / t1.cc = t2.cc and a third join by client if its in table 2 if it's not then pull t2.client 0

Table 1:

client = decimal
acc = char
cc = char

client acc cc
1000 01 01
1000 01 02
1001 01 01
1001 01 02
1002 01 01
1002 01 02
1003 01 02
1003 01 02

Table 2:

client = decimal
acc = char
cc = char

client acc cc desc
1000 01 01 supplies
1000 01 02 food
1001 01 01 tents
1001 01 02 camping equipment
0 01 01 misc1
0 01 02 misc2

Report for client 1002 should be:

client acc cc desc
1002 01 01 misc1
1002 01 02 misc2
1003 01 01 misc1
1003 01 02 misc2


since table 2 does not contain client 1002, I want it to return or make the join to client 0 from table 2. If it is better to define the misc1, mics2 records with a NULL client, that can be done as well. Thanks again Michel for the value feedback. I know I am closer to what I want.
Re: Conditional Join Help [message #254725 is a reply to message #254476] Fri, 27 July 2007 13:18 Go to previous messageGo to next message
vsao
Messages: 7
Registered: July 2007
Location: Connecticut
Junior Member
Thanks all for your help. I was able to finally figure it out. It looks like using the IN was part of the solution.

Here is what I have.

where t2.client = CASE WHEN t1.client in (t2.client) THEN t1.client ELSE 0 END
  and t2.acc = t1.acc
  and t2.cc = t1.cc
  and t1.client = 1000

Thinking about it now, using a NVL wouldn't work due to the fact that the client was never NULL and was always a number in t1. The IN state by itself gave me a TRUE statement all the time because t2.client had to either equal t1.client or 0 but was not joined to anything. By adding the CASE statement, it allowed me to meet the criteria of 1)if t1.client was in t2.client then use the t1.client else 2)if not then use 0 to join to t2.client.

Thanks again all for your comments and suggestions. Take care.
Re: Conditional Join Help [message #254838 is a reply to message #254725] Sun, 29 July 2007 02:06 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I don't think your where clause works. You get 12 rows. You should only have 8.
SQL> SELECT *
  2  FROM table_1
  3  ORDER BY 1,2,3;

              CLIENT ACC                  CC
-------------------- -------------------- --------------------
                1000 01                   01
                1000 01                   02
                1001 01                   01
                1001 01                   02
                1002 01                   01
                1002 01                   02
                1003 01                   02
                1003 01                   02

8 rows selected

SQL> SELECT *
  2  FROM table_2
  3  ORDER BY 1,2,3;

              CLIENT ACC                  CC                   DESCRIPTION
-------------------- -------------------- -------------------- --------------------
                   0 01                   01                   misc1
                   0 01                   02                   misc2
                1000 01                   01                   supplies
                1000 01                   02                   food
                1001 01                   01                   tents
                1001 01                   02                   camping equipment

6 rows selected

SQL> SELECT t1.client
  2        ,t1.acc
  3        ,t1.cc
  4        ,t2.description
  5  FROM table_1 t1
  6      ,table_2 t2
  7  WHERE t2.client = CASE WHEN t1.client in (t2.client) THEN t1.client ELSE 0 END
  8  AND   t2.acc = t1.acc
  9  AND   t2.cc = t1.cc
 10  ORDER BY 1,2,3;

              CLIENT ACC                  CC                   DESCRIPTION
-------------------- -------------------- -------------------- --------------------
                1000 01                   01                   supplies
                1000 01                   01                   misc1
                1000 01                   02                   food
                1000 01                   02                   misc2
                1001 01                   01                   tents
                1001 01                   01                   misc1
                1001 01                   02                   camping equipment
                1001 01                   02                   misc2
                1002 01                   01                   misc1
                1002 01                   02                   misc2
                1003 01                   02                   misc2
                1003 01                   02                   misc2

12 rows selected
You might want to use this instead.
SQL> SELECT t1.client
  2        ,t1.acc
  3        ,t1.cc
  4        ,nvl(t2.description, t3.description) description
  5  FROM table_1 t1
  6      ,table_2 t2
  7      ,table_2 t3
  8  WHERE t2.client(+) = t1.client
  9  AND   t2.acc(+) = t1.acc
 10  AND   t2.cc(+) = t1.cc
 11  AND   t3.client = 0
 12  AND   t3.acc = t1.acc
 13  AND   t3.cc = t1.cc
 14  ORDER BY 1,2,3
 15  ;

              CLIENT ACC                  CC                   DESCRIPTION
-------------------- -------------------- -------------------- --------------------
                1000 01                   01                   supplies
                1000 01                   02                   food
                1001 01                   01                   tents
                1001 01                   02                   camping equipment
                1002 01                   01                   misc1
                1002 01                   02                   misc2
                1003 01                   02                   misc2
                1003 01                   02                   misc2

8 rows selected

SQL> 
If your running on v9 or above, I would use this
SELECT t1.client
      ,t1.acc
      ,t1.cc
      ,nvl(t2.description, t3.description) description
FROM table_1 t1
LEFT JOIN table_2 t2 ON t2.client = t1.client
                    AND t2.acc = t1.acc
                    AND t2.cc = t1.cc
LEFT JOIN table_2 t3 ON t2.description IS NULL
                    AND t3.client = 0
                    AND t3.acc = t1.acc
                    AND t3.cc = t1.cc
ORDER BY 1,2,3;
Re: Conditional Join Help [message #254992 is a reply to message #254476] Mon, 30 July 2007 06:23 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

Hi,

Find the following solution. It will help u sure.

SELECT a1.client,a1.acc,a1.cc,a2.des FROM table1 a1, table2 a2
WHERE a1.acc=a2.acc AND a1.cc=a2.cc AND a1.CLIENT= DECODE(a2.CLIENT,0,1002)
Re: Conditional Join Help [message #254994 is a reply to message #254992] Mon, 30 July 2007 06:29 Go to previous message
ravik_mca03
Messages: 3
Registered: July 2007
Junior Member


your answer is appreciable
Previous Topic: reg: Integrity constraints
Next Topic: spawning multiple processes
Goto Forum:
  


Current Time: Sat Dec 10 22:26:12 CST 2016

Total time taken to generate the page: 0.24324 seconds