oracle select from multiple tables

Question

I have function which will return mi home or away if the p_search_string is same as those fields.

    FUNCTION SEARACH_FOR_GAMES  ( p_search_string in varchar2 )
                             return weak_cur
  IS
    SEARCH_FIXID WEAK_CUR;   
  BEGIN  
    OPEN   SEARCH_FIXID FOR
select  HOME,AWAY,COMP_NAME, M_TIME from SOCCER s
where s.HOME LIKE (:p_search_string) or s.AWAY LIKE (:p_search_string)
union all
select  HOME,AWAY,LISTS,M_TIME from BASKETBALL b
where b.HOME LIKE (:p_search_string) or b.AWAY LIKE (:p_search_string)
union all
select HOME,AWAY,COMP,M_TIME from HANDBALL h
where h.HOME LIKE (:p_search_string) or h.AWAY LIKE (:p_search_string)
union all
select  HOME,AWAY,LISTS,M_TIME from ICE_HOCKEY i
where i.HOME LIKE (:p_search_string) or i.AWAY LIKE (:p_search_string)
union all
select  HOME,AWAY,COMP,M_TIME from TENISt
where t.HOME LIKE (:p_search_string) or t.AWAY LIKE (:p_search_string)
union all
select  HOME,AWAY,LISTS,M_TIME from VOLLEYBALL v
where v.HOME LIKE (:p_search_string) or v.AWAY LIKE (:p_search_string);
    RETURN SEARCH_FIXID;
  END SEARACH_FOR_GAMES;

This works fine, but i m wondering is there a "nicer" way to write down these selects ?

Thanks

I would claim that your data model is maybe the problem. Since we don't see your table definitions, hard to say, but Having one table per sport type is not a good model. Sport type should be an attribute/column of a table, not individual tables. WHat happens when you need to add another sport? Create a new table? — OldProgrammer
– OldProgrammer, Commented Jun 1, 2018 at 15:15
Have a single table called game_schedules and another table sport for soccer, tennis etc with an id sport_id which is referenced by a foreign key in game_schedules. You may then join these 2 tables in a single select(without multiple union alls) for your cursor. — Kaushik Nayak
– Kaushik Nayak, Commented Jun 1, 2018 at 15:38

William Robertson · Accepted Answer · 2018-06-01 18:14:27Z

1

This is definitely an issue with the data model as far as this requirement is concerned. I can suggest two options:

Option# 1: Create a single table SPORTS by combining all tables and have a column sport_type. This will allow to use one select query in function.

select  HOME,AWAY,COMP_NAME, M_TIME from SPORTS s
where s.HOME LIKE (:p_search_string) or s.AWAY LIKE (:p_search_string)
  and sport_type in 
(
'SOCCER',
'BASKETBALL',
'HANDBALL',
'ICE_HOCKEY',
'TENIST',
'VOLLEYBALL'
)

Option# 2:

Create a view by combining all these tables and select from that view in this function.
CREATE OR REPLACE VIEW VW_SPORTS
AS
select HOME,AWAY,COMP_NAME, M_TIME, 'SOCCER' SPORT_TYPE from SOCCER
union all
select  HOME,AWAY,LISTS,M_TIME,'BASKETBALL' SPORT_TYPE from BASKETBALL 
union all
select HOME,AWAY,COMP,M_TIME,'HANDBALL' SPORT_TYPE from HANDBALL
union all
select  HOME,AWAY,LISTS,M_TIME,'ICE_HOCKEY' SPORT_TYPE from ICE_HOCKEY
union all
select  HOME,AWAY,COMP,M_TIME,'TENIST' SPORT_TYPE from TENISt
union all
select  HOME,AWAY,LISTS,M_TIME,'VOLLEYBALL' SPORT_TYPE  from VOLLEYBALL v;


 FUNCTION SEARACH_FOR_GAMES  ( p_search_string in varchar2 )
                             return weak_cur
  IS
    SEARCH_FIXID WEAK_CUR;   
  BEGIN  
    OPEN   SEARCH_FIXID FOR
       select  HOME,AWAY,COMP_NAME, M_TIME from VW_SPORTS s
        where s.HOME LIKE (:p_search_string) or s.AWAY LIKE (:p_search_string)
         and s.SPOR_TYPE IN 
    (
    'SOCCER',
    'BASKETBALL',
    'HANDBALL',
    'ICE_HOCKEY',
    'TENIST',
    'VOLLEYBALL'
    );
    RETURN SEARCH_FIXID;
  END SEARACH_FOR_GAMES;

edited Jun 1, 2018 at 18:14

William Robertson

16.1k4 gold badges41 silver badges49 bronze badges

answered Jun 1, 2018 at 17:38

SumitSoni

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

civesuas_sine Over a year ago

All these sport tables are external tables. Would that be problem if I create one table to hold all data ..

Collectives™ on Stack Overflow

oracle select from multiple tables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related