Oracle Text Search on Multiple Tables and Joins

Oracle text search on multiple tables and joins

I usually solve fulltext searches on multiple columns on different tables by materializing a structured XML view of them and then creating the index on the whole XML.

This solution is generic and also give you freedom on search: the whole view or only a subpath. The drawback is managing the refresh of a MV who usually cannot be refreshed fast; but update of fulltext index usually isn't in real-time, too, so you can just schedulate it.

-- Crating the view
CREATE MATERIALIZED VIEW fulltext_helper
NOLOGGING
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT 
   a.dob, -- we don't need to fulltext on him
   XMLELEMENT(helper,
     XMLFOREST(a.emp_no AS emp_no, 
              a.dept_no AS dept_no, 
              b.emp_name AS emp_name)
   ) AS indexme
FROM v_depts a 
LEFT OUTER JOIN employee_details b
ON (a.emp_no = b.emp_no);

-- Creating the index
BEGIN
    ctx_ddl.create_preference('fulltext_helper_lexer', 'BASIC_LEXER');
    ctx_ddl.create_preference('fulltext_helper_filter', 'NULL_FILTER');
END;
/
CREATE INDEX fulltext_helper_index ON fulltext_helper (indexme)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (
    'DATASTORE CTXSYS.DIRECT_DATASTORE
     LEXER fulltext_helper_lexer
     FILTER fulltext_helper_filter');

-- Searching the whole data
SELECT * FROM fulltext_helper
WHERE contains(indexme, '{abc} INPATH (/helper)') > 0;

-- Searching only on "empno"
SELECT * FROM fulltext_helper
WHERE contains(indexme, '{abc} INPATH (/helper/emp_no)') > 0;

Joining multiple tables ORACLE

This would be equivalent (if we also add the additional criteria you mentioned):

FROM 
    SD 
    
    INNER JOIN STORE as S
    on S.STOREID = SD.STOREID

    INNER JOIN MATERIAL as M
    ON M.MATERIALID = SD.MATERIALID

    LEFT JOIN STOREBIN as SB
    ON SB.STOREBINID = SD.STOREBINID

    LEFT JOIN VENDOR as V
    ON V.VENDORID = SD.VENDORID

    LEFT JOIN MATERIALPRICE as MP
    ON MP.MATERIALID = M.MATERIALID

Oracle Text Search on certain columns

Giving the path "/HELPER/EMP_NAME/SUPERVISOR" tells him to search on leaf like "...", which doesn't exists; furthermore you shoud specify the INPATH operator.

The right syntax is:

CONTAINS(indexme,'jack INPATH(/HELPER/EMP_NAME) and jill INPATH(/HELPER/SUPERVISOR)') > 0;

Oracle SQL Query to join multiple tables

The query should look like this:

select e.ENCNTR_ID as e_id, p.PERSON_ID as p_id, e.REG_DT_TM as admitted, ce.EVENT_ID as event_id
from ENCOUNTER e join
     person p 
     on e.person_id = p.person_id left join
     CLINICAL_EVENT ce
     on ce.ENCNTR_ID = e.ENCNTR_ID and   
        ce.EVENT_CD in (1235764, 22161234) and                
        ce.valid_until_dt_tm > sysdate left join
     CE_BLOB cb
     on ce.EVENT_ID = cb.EVENT_ID and
        cb.valid_until_dt_tm > sysdate
where e.reg_dt_tm > date '2018-01-01' and
      e.reg_dt_tm < date '2018-01-02' and
      e.active_ind = 1 and
      e.encntr_type_cd = 7113.00 and
      e.ACTIVE_STATUS_CD = 22223 
order by e.REG_DT_TM, ce.PERFORMED_DT_TM, ce.CLINICAL_EVENT_ID;

Notes:

• Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.
• The WHERE clause goes after the FROM clause.
• JOIN is an operator in the FROM clause, so all JOINs need to be before the WHERE.
• Use the keyword DATE for date constants.

Join Multiple tables in oracle sql

This is the query using join:

select p.p_name,c.c_name,s.s_name,s.s_contact,b.b_name,b.b_contact 
from product p 
join buyer b  on p.p_id = b.p_id and <second condition>
join category c on  p.p_id = c.p_id
join seller s on c.p_id  = s.p_id
where p.p_id="123" ;

Combining values from multiple tables using join clause with multiple ON

This approach uses UNION ALL to combine the letter named tables (tbla, tblb, tblc, tbld) into a CTE, common table expression. The combined table is then summarized by id, [desc] and crosstabulated (or pivoted) across the login columns. The pivoted result is then LEFT JOIN'ed to the master_tbl. Something like this.

with
tbl_cte(tbl, id, [login], [desc]) as (
    select 'A', * from tbla
    union all
    select 'B', * from tblb
    union all
    select 'C', * from tblc
    union all
    select 'D', * from tblc),
pvt_cte(id, tbla_login, tblb_login, tblc_login, tbld_login, [desc]) as (
    select id, 
           max(case when tbl='A' then [login] else null end) as tbla_login,
           max(case when tbl='B' then [login] else null end) as tblb_login,
           max(case when tbl='C' then [login] else null end) as tblc_login,
           max(case when tbl='D' then [login] else null end) as tbld_login,
           [desc] 
    from tbl_cte
    group by id, [desc])
select mt.id, [name], country, [status], pc.tbla_login, 
       pc.tblb_login, pc.tblc_login, pc.tbld_login, pc.[desc]
from master_tbl mt
     left join pvt_cte pc on mt.id=pc.id;

Search across multiple tables and also display table name in resulting rows

You are looking for dynamic SQL. Assemble your query from the system catalog automatically:

SELECT string_agg('SELECT student_name, '''
                   || c.oid::regclass || ''' AS tbl, pid FROM '
                   || c.oid::regclass
                   || $$ WHERE student_name = 'John Doe'$$
                 , E'\nUNION ALL\n')
FROM   pg_namespace n
JOIN   pg_class     c ON c.relnamespace = n.oid
WHERE  n.nspname = 'public'         -- schema name where your tables lie
AND    c.relname LIKE 't%'          -- and / or filter table names
AND    EXISTS (
   SELECT 1 FROM pg_attribute 
   WHERE  attrelid = c.oid
   AND    attname = 'student_name'  -- make sure column exists
   AND    NOT attisdropped          -- and is alive
   );

Produces the query string:

SELECT student_name, 'tbl1' AS tbl, pid FROM tbl1 WHERE student_name = 'John Doe'
UNION ALL
SELECT student_name, 'tbl2' AS tbl, pid FROM tbl2 WHERE student_name = 'John Doe'
UNION ALL
SELECT student_name, 'tbl3' AS tbl, pid FROM tbl3 WHERE student_name = 'John Doe'
...

Then run it in a second call or completely automate it with a PL/pgSQL function using EXECUTE. Example:

Select a dynamic set of columns from a table and get the sum for each

This query produces safe code with sanitized identifiers preventing SQL injection. (Explanation for oid::regclass here.)

There are more related answers. Use a search.

BTW, LIKE in student_name LIKE 'John Doe' is pointless. Without wildcards, just use =.

How to query specific column name from multiple table from SQL

Because user_tab_columns has table_name and column_name in each rows, so your select only omit the row with that columns, not other rows with other columns of that table.

One query you could use is:

SELECT DISTINCT table_name
FROM user_tab_columns c
WHERE NOT EXISTS (
    SELECT 1
    FROM user_tab_columns
    WHERE column_name LIKE '%OBJECT_NO%'
        AND table_name = c.table_name
) 
ORDER BY table_name;

Other way could work is:

SELECT DISTINCT table_name
FROM user_tab_columns 
WHERE table_name NOT IN (
    SELECT table_name
    FROM user_tab_columns
    WHERE column_name LIKE '%OBJECT_NO%'
) 
ORDER BY table_name;

---

Related Topics