bvk48 bvk48 - 3 months ago 11
SQL Question

Join only selected tables

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
7
th 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

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...

Comments