CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle11g
ziggy
ziggy

Reputation: 15876

Oracle SQL - Is it possible to join two tables only when a certain condition is true?

I have a table that i have to work with which unfortunately is not very well designed. The table lists existing products that have matched to other products. There are two types of matches: a match to a main category product and a match to an alternative category product.

Whenever there is a match, the ID of that match is populated in the relevant column in the table as shown below in PRODUCT_MATCH. So in each product row, there will be a PRODUCT_ID specified in PRODUCT_MAIN_CATEGORY_ID or PRODUCT_ALTERNATIVE_CATEGORY_ID but never both.

PRODUCT

PRODUCT_ID  PRODUCT_CATEGORY PRODUCT_SERIAL_NO
1001        Book             XSDAD132   
1002        MP3              X348023948
1003        DVD              5234234023948
2003        CD               SDRX83489
2002        Video            23423879JJ0

PRODUCT_MATCH

PRODUCT_MATCH_ID, PRODUCT_ID, MAIN_CATEGORY_ID, ALTERNATIVE_CATEGORY_ID, PRODUCT_CATEGORY
1         1001      1002                        Book
2         1001                  1003            Book
3         1002      1004                        Book
4         2002                  1002            Video
5         2002      1003                        Video
6         2002      1001                        Video       
7         2002      1002                        Video       
8         1003                  2003            DVD

I have to read the PRODUCT_MATCH table so that if there is a match on MAIN_CATEGORY_ID I display the ID, or if there is an ALTERNATIVE_CATEGORY_ID i have to display the product_id and the serial number of the product id.

I have written the following query to display the results

select product_id,
cast(collect(coalesce(MAIN_CATEGORY_ID,
  decode(ALTERNATIVE_CATEGORY_ID,null,null, ALTERNATIVE_CATEGORY_ID || '-' || PRODUCT_CATEGORY))) as myType) as product_matches
from product_match
group by product_id

Note: MyType is defined as a table of varchar(2000)

The query will either use MAIN_CATEGORY_ID if it is not null otherwise it will use the value in ALTERNATIVE_CATEGORY_ID to build the output. I want to change this so that it displays the product_id concatenated with the product_serial_no from the PRODUCT number whenever the coalesce function uses the ALTENATIVE_CATEGORY_ID value. This will require a join with the PRODUCT table.

Basically the output I would like to achieve would look like this:

PRODUCT_ID  PRODUCT_MATCHES
-----------------------------------
1001        1002,DVD-5234234023948
1002        1004
2002        MP3-X348023948, 1003, 1001, 1002
1003        CD-SDRX83489

I know that i can simply join the tables but it is not as simple as that. The problem is that i only want to use the join if ALTERNATIVE_CATEGORY_ID is used. This means that a simple join will affect the results for when MAIN_CATEGORY_ID is used.

Upvotes: 2

Views: 1058

Answers (2)

Art
Art

Reputation: 5792

I think LISTAGG() or WM_CONCAT() functions is what you are looking for as you wrote that you have table not nested table or collection. Collect will return a collection and mostly used with PL/SQL datatypes not with simple tables. The LISTAGG() or WM_CONCAT() will concatenate the values into a single string exactly as in your example. Sorry, I will not add any queries as your output is very confusing to me. I see only Book matches to product_id = 1001, not DVD and other things. It would be nice if you created sample data using SQL Fiddle or simply create tables and insert. Please do this in the future.

This worked for me - the emp_test is exact copy of scott.emp table:

ALTERE TABLE emp_test MODIFY ename VARCHAR2(3000);

Update emp_test set ename = rpad(ename, 3000, ' '); Commit;

SELECT deptno
     , LISTAGG(TRIM(SUBSTR(ename, 1, 3000)), ',') WITHIN GROUP (ORDER BY ename) AS employees
    , count(*) total_emps
  FROM emp_test
GROUP BY deptno
/

SELECT deptno
     , WM_CONCAT(TRIM(SUBSTR(ename, 1, 3000))) AS employees
     , count(*) total_emps
 FROM emp_test
GROUP BY deptno
/

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

select 
   pm.product_id, 
   cast(
      collect(
         coalesce(
            pm.MAIN_CATEGORY_ID, 
            pm.ALTERNATIVE_CATEGORY_ID || '-' || pr.PRODUCT_SERIAL_NO
         )
      ) as myType
   ) as product_matches
from 
   product_match pm
   left join PRODUCT pr
      on pr.PRODUCT_ID = pm.ALTERNATIVE_CATEGORY_ID
group by 
   pm.product_id

Upvotes: 4

Related Questions