Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Question Unanswered: SQL Nested Query based on value of Dependent Column

    Here, in the attached Table structure, TB_Vehicles is the Master table and TB_Cars, TB_Bikes, TB_Cars are Satellite tables which hold more info about respective vehicles.

    I want users to search using the Name. So, when users enter Name as 'Access', my query should give all information about that vehicle, including that from Satellite Tables (using both TB_Vehicles and TB_Scooters).

    Code:
    SELECT * 
    FROM TB_Vehicles
    WHERE V_NAME = 'Access';
    Similarly, if user enters 'Linea', it should give info from TB_Vehicles and TB_Cars tables.

    So, with V_NAME as input, I'll find V_TYPE from TB_Vehicles table, and using that, I need to identify which satellite table, I need to join to retrieve more info.

    How do I do this? Can you please help me with the query? Do I need to use CASE or DECODE to achieve this?
    Attached Thumbnails Attached Thumbnails Table_Structure.jpg  

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    One option is to create a view on Scooters, Bikes and Cars tables and perform search on a view (instead of separate tables); something like
    Code:
    create view v_vehicles 
      (v_type, v_name, v_id, v_year, v_mileage, v_engine, v_car_type)
       as
    select v_type, v_name, v_id, v_year, v_mileage, null, null
      from tb_scooters
    union all
    select v_type, v_name, v_id, v_year, null, v_engine, null
      from tb_bikes
    union all
    select v_type, v_name, v_id, v_year, null, null, v_car_type
      from tb_cars

  3. #3
    Join Date
    Sep 2013
    Posts
    6
    @Littlefoot: Thanks for your response.

    I don't have the option of creating any extra table or view for this. It's a single dynamic SQL query, which should be able to fulfill the requirement.

    The requirement here is to display only those columns which are applicable for that particular vehicle type. So, when user gives 'Access' as input, it should display all columns from TB_Vehicles table, and v_mileage from TB_Scooters table.

    I was thinking whether this can be done using CASE or DECODE, where I first identify the type (V_TYPE) of the vehicle from TB_Vehicles table using the input name (V_NAME), and then based on this V_TYPE, I'll search the relevant table and display only relevant columns.

    When Input Name is 'Access', then result should be:
    Code:
    V_NAME	V_ID	V_YEAR	V_TYPE	V_COLOR	V_AVAILABLE	V_MILEAGE
    Access	20	2004	Scooter	Black	No	        50
    When Input Name is 'i20', then result should be:
    Code:
    V_NAME	V_ID	V_YEAR	V_TYPE	V_COLOR	V_AVAILABLE	V_CAR_TYPE
    i20	60	2012	Car	Red	Yes	        Sedan

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't have the option of creating any extra table or view
    Why not?

    A view seems to be reasonably easy & fast solution to the problem.

  5. #5
    Join Date
    Sep 2013
    Posts
    6
    Thanks for your help Littlefoot! Actually the requirement was like that.

    Finally I had to manage with the reply in the below link using CASE: ('manage', 'cuz what I wanted to do is seemingly not possible using basic SQL queries)

    OraFAQ Forum: SQL & PL/SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •