Join only selected tables

Question

I have A table, B table 10 different tables(1,2,3,..10). I have to select the data by joining any one/more of those 10 , A, and B tables based on the input.

Ex: If input is only 2,5,8,10 I have to join A, B, 2,5,8, and 10 tables and retrieve the data. If input is only 1 and 7th table , I have to join A , B, 1, and 7 tables and retrieve the data.

---- sample-----

suppose A is a person table with p_id, p_name, order_id,.......
B is an order table with Order_id,.........
each 1-10 tables are shopping items like clothes, shoes, electronics,...etc

now I want to pull person details who orders only clothes and shoes with some other constraints like within these dates or age should be 20 like that then I have to join only person table, order table, clothes table, and shoe table and retrived the details about the persons who ordered atlease one of both the tables. person having only shoe or only colthes are not required. result will be like how many clothes he orderd and how many shoes he ordered.

Can anyone please give me some idea how to do it. I am working on oracle db and using SQL.

Answer 1

I don't think you can handle this with plain SQL. It looks like you need to use dynamic SQL. Have a stored procedure with parameters for the tables you need to join and then build your query based on how you call that procedure. Run the query with execute immediate... Read more here: https://docs.oracle.com/cloud/latest/db112/LNPLS/dynamic.htm#LNPLS011

Update: Here's a sample with pseudo plsql as I don't have your actual data. This is just to give you an idea on how to proceed - I can't write you actual code with the given information and your setup:

DECLARE
  table1  VARCHAR2 (100) := 'Table1';
  table2  VARCHAR2 (100) := '';
  table3  VARCHAR2 (100) := 'Table2';
  ...
  table10 VARCHAR2 (100) := 'Table10';
  query VARCHAR2 (4000) := '';
BEGIN
--create the core join between A and B
  query := query || 'select A.p_id, A.p_name, B.order_id from A, B where A.order_id = B.order_id ';
  IF (table1  != '') THEN query := query || ' and a.id = table1.id '; 
  --if statements for the rest of your tables and add the join condition for each table to the query string  
...

EXECUTE IMMEDIATE query;  --run the query... depends on what you want to do with the returned result you can use RETURN INTO or BULK COLLECT INTO...

EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;

Hope this helps...

Answer 2

This is sql-server syntax as far as variables but hopefully you can translate that to oracle variables. Anyway the trick is to use your input variables as conditions on a LEFT JOIN. That way if the condition for your variable is false it just wont return rows from that table. If true it will return matches. If you really want it to be an INNER JOIN rather than a LEFT JOIN then just put a case statement in your WHERE condition to make it true if your variable condition is met.

DECLARE @IncludeT1 BIT, @IncludeT2 BIT

SELECT *
FROM
    TableA a
    INNER JOIN TableB B
    ON a.somecol = b.somecol
    LEFT JOIN Table1 t1
    ON a.somecol = t1.somecol
    AND @IncludeT1 = 1
    LEFT JOIN Table2 t2
    ON b.somecol = t2.solmcol
    AND @IncludeT1 = 1
WHERE
    (CASE WHEN (@IncludeT1 = 1 and t1.someid IS NOT NULL) OR @IncludeT1 = 0 THEN 1 ELSE 0 END) = 1
    AND (CASE WHEN (@IncludeT2 = 1 and t2.someid IS NOT NULL) OR @IncludeT2 = 0 THEN 1 ELSE 0 END) = 1

You can also do it via dynamic SQL as the other answer suggests there are more considerations if you go that route.

Answer 3

It appears your 10 different tables are actually just 10 different categories of Products.

Is it possible to homogenise them all into one big Products table with an additional field for ProductCategory (clothes, shoes, electronics, etc)? Your assignment would then reduce to a simple WHERE on this ProductCategory field.

An equivalent (albeit less efficient) view can be achieved without modifying your schema by UNION-ing all 10 tables together, upon which you can draw your assignment.

I realise this does not directly answer your question, but this would drastically simplify your problem and normalise your database, thereby eliminating the need for a complicated solution (which is always the preferred way to go).

Answer 4

I think you should go for pipe lined function with dynamic sql.

e.g. you will create sql on the fly, execute it with execute immediate, return resuls row by row. Take a look here.

Note that there is no accepted answer, however I think

Vincent Malgrat's

approach will do just fine.