Home » SQL & PL/SQL » SQL & PL/SQL » SQL Nested Query based on value of Dependent Column (SQL)
 SQL Nested Query based on value of Dependent Column [message #595400] |
Tue, 10 September 2013 22:32  |
 |
nickz
Messages: 39
Registered: September 2013
Location: US
|
Member |
|
|
In the below 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).
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?
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595401 is a reply to message #595400] |
Tue, 10 September 2013 22:58  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Use a single SQL with all your tables, and with proper join conditions. Make sure you have proper constraints on the parent and child tables for data integrity. Your join condition should be on V_ID, V_YEAR, V_TYPE. You might need OUTER JOIN for all the join conditions of child tables.
You can do something like this(I used outer joins blindly since I just have your test data) -
SQL> CREATE TABLE tb_vehicles AS SELECT * FROM(
2 WITH DATA AS (
3 SELECT 'access' v_name, 10 v_id, 2004 v_year, 'scooter' v_type, 'black' v_colour from dual union ALL
4 SELECT 'linea' v_name, 20 v_id, 2005 v_year, 'car' v_type, 'black' v_colour from dual)
5 SELECT * from DATA);
Table created
SQL> CREATE TABLE tb_scooters AS SELECT * FROM(
2 WITH DATA AS (
3 SELECT 'access' v_name, 10 v_id, 2004 v_year, 'scooter' v_type from dual
4 )
5 SELECT * from DATA);
Table created
SQL> CREATE TABLE tb_cars AS SELECT * FROM(
2 WITH DATA AS (
3 SELECT 'linea' v_name, 20 v_id, 2005 v_year, 'car' v_type from dual
4 )
5 SELECT * from DATA);
Table created
SQL> CREATE TABLE tb_bike AS SELECT * FROM(
2 WITH DATA AS (
3 SELECT 'pulsar' v_name, 30 v_id, 2006 v_year, 'bike' v_type from dual
4 )
5 SELECT * from DATA);
Table created
SQL> SELECT TV.*
2 FROM TB_VEHICLES TV, TB_SCOOTERS TS, TB_CARS TC, TB_BIKE TB
3 WHERE TV.V_NAME = TS.V_NAME(+)
4 AND TV.V_NAME = TC.V_NAME(+)
5 AND TV.V_NAME = TB.V_NAME(+)
6 AND TV.V_ID = TS.V_ID(+)
7 AND TV.V_ID = TC.V_ID(+)
8 AND TV.V_ID = TB.V_ID(+)
9 AND TV.V_YEAR = TS.V_YEAR(+)
10 AND TV.V_YEAR = TC.V_YEAR(+)
11 AND TV.V_YEAR = TB.V_YEAR(+)
12 AND TV.V_TYPE = TS.V_TYPE(+)
13 AND TV.V_TYPE = TC.V_TYPE(+)
14 AND TV.V_TYPE = TB.V_TYPE(+)
15 AND TV.V_NAME = 'access';
V_NAME V_ID V_YEAR V_TYPE V_COLOUR
------ ---------- ---------- ------- --------
access 10 2004 scooter black
SQL>
[Updated on: Wed, 11 September 2013 00:45] Report message to a moderator |
|
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595430 is a reply to message #595401] |
Wed, 11 September 2013 04:32 |
|
nickz
Messages: 39
Registered: September 2013
Location: US
|
Member |
|
|
Thanks Lalit!
Sorry I didn't mention earlier but 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:
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:
V_NAME V_ID V_YEAR V_TYPE V_COLOR V_AVAILABLE V_CAR_TYPE
i20 60 2012 Car Red Yes Sedan
|
|
|
|
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595451 is a reply to message #595446] |
Wed, 11 September 2013 06:05 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
You didn't test your script, now did you?
Here's a corrected version:
CREATE TABLE TB_SCOOTERS
( V_NAME VARCHAR2(20),
V_ID NUMBER(4) NOT NULL ENABLE,
V_YEAR NUMBER(4),
V_TYPE VARCHAR2(20),
V_MILEAGE NUMBER(4),
CONSTRAINT TB_SCOOTERS_PK PRIMARY KEY (V_ID)
);
CREATE TABLE TB_BIKES
( V_NAME VARCHAR2(20),
V_ID NUMBER(4) NOT NULL ENABLE,
V_YEAR NUMBER(4),
V_TYPE VARCHAR2(20),
V_ENGINE NUMBER(4),
CONSTRAINT TB_BIKES_PK PRIMARY KEY (V_ID)
);
CREATE TABLE TB_CARS
( V_NAME VARCHAR2(20),
V_ID NUMBER(4) NOT NULL ENABLE,
V_YEAR NUMBER(4),
V_TYPE VARCHAR2(20),
V_CAR_TYPE VARCHAR2(20),
CONSTRAINT TB_CARS_PK PRIMARY KEY (V_ID)
);
CREATE TABLE TB_VEHICLES
( V_NAME VARCHAR2(20),
V_ID NUMBER(4) NOT NULL ENABLE,
V_YEAR NUMBER(4),
V_TYPE VARCHAR2(20),
V_COLOR VARCHAR2(20),
V_AVAILABLE VARCHAR2(20),
CONSTRAINT TB_VECHILES_PK PRIMARY KEY (V_ID)
);
INSERT INTO TB_SCOOTERS (V_NAME, V_ID, V_YEAR, V_TYPE, V_MILEAGE) VALUES ('Activa', '10', '2003', 'Scooter', '45');
INSERT INTO TB_SCOOTERS (V_NAME, V_ID, V_YEAR, V_TYPE, V_MILEAGE) VALUES ('Access', '20', '2004', 'Scooter', '50');
INSERT INTO TB_BIKES (V_NAME, V_ID, V_YEAR, V_TYPE, V_ENGINE) VALUES ('Karizma', '30', '2001', 'Bike', '180');
INSERT INTO TB_BIKES (V_NAME, V_ID, V_YEAR, V_TYPE, V_ENGINE) VALUES ('Pulsar', '40', '2008', 'Bike', '150');
INSERT INTO TB_CARS (V_NAME, V_ID, V_YEAR, V_TYPE, V_CAR_TYPE) VALUES ('Linea', '50', '2011', 'Car', 'Sedan');
INSERT INTO TB_CARS (V_NAME, V_ID, V_YEAR, V_TYPE, V_CAR_TYPE) VALUES ('i20', '60', '2012', 'Car', 'Sedan');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Activa', '10', '2003', 'Scooter', 'Black', 'Yes');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Access', '20', '2004', 'Scooter', 'Black', 'No');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Karizma', '30', '2001', 'Bike', 'Red', 'Yes');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Pulsar', '40', '2008', 'Bike', 'Red', 'Yes');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Linea', '50', '2011', 'Car', 'White', 'No');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('i20', '60', '2012', 'Car', 'Red', 'Yes');
COMMIT;
MHE
[Updated on: Wed, 11 September 2013 06:06] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595513 is a reply to message #595446] |
Wed, 11 September 2013 08:05 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
It would help to state in which tool you want to use that query and how the "result" shall be "displayed". As you want to "display" (what that means? it depends on the client tool) dynamic number/types of columns (if I understanding it correctly), you have to use dynamic SQL or use some dynamic feature of your client tool.
E.g. in sqlplus and simple output to terminal/file, I would use cursor variable, something like:
var out_cur refcursor
declare in_val integer := 2;
begin
if in_val=1 then
open :out_cur for select * from emp;
elsif in_val=2 then
open :out_cur for select * from dept;
elsif in_val=3 then
open :out_cur for select * from bonus;
elsif in_val=4 then
open :out_cur for select * from salgrade;
end if;
end;
/
print out_cur
(note that I do not have your table, so I used standard scott user; I am also lazy to create procedure and run it afterwards, so I simply used the anonymous block; IN_VAL and OUT_CUR would be IN/OUT parameters of that procedure)
You may create the similar code in PL/SQL procedure e.g. here: http://www.orafaq.com/scripts/plsql/refcurs.txt
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595520 is a reply to message #595464] |
Wed, 11 September 2013 08:42 |
|
nickz
Messages: 39
Registered: September 2013
Location: US
|
Member |
|
|
@Maaher: Sorry I just typed the script in a notepad, didn't test it.. I should have! 
@Michel: I am new to this forum. Will certainly keep that in mind when posting next time.
Quote: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.
Can this not be done?? This might be a dumb question to ask again, but just to clarify all my doubts. Suppose, I identify the V_TYPE from TB_Vehicles table, using the V_NAME, supplied as input by the user. Then for each CASE for each V_TYPE (Car/Bike/Scooter), I put a query inside it which will have a sub-query on the relevant table returning the relevant columns. And all this in a single query.
CASE V_TYPE
WHEN 'Car' THEN <Query on TB_Cars>
WHEN 'Bike' THEN <Query on TB_Bikes>
...
Another approach: Let's say I create a View with all columns from TB_Vehicles, TB_Cars, TB_Scooters, TB_Bikes. Now, what should be the query in this case if I just want to display only the NOT NULL columns, if V_NAME column matches the user given input?
[Updated on: Wed, 11 September 2013 08:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595528 is a reply to message #595526] |
Wed, 11 September 2013 09:12 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
The root cause of this challenge is that the table "design" is flawed.
Table name or column name should NEVER be application data; like JANUARY, FEBRUARY, or MARCH.
Table name or column name should NEVER be application data; like 2009, 2010, 2011, 2012, 2013.
Table name or column name should NEVER be application data; like FRANCE, GERMANY, SWITZERLAND
It does NOT matter what the actual names are. Data values should NEVER be part of table name or column name.
If the tables were normalized to Third Normal Form, then the coding solution would be trivial.
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595529 is a reply to message #595520] |
Wed, 11 September 2013 09:17 |
|
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
nickz wrote on Wed, 11 September 2013 15:42Can this not be done?? It can be done, but not with a dynamic column header because a select statement can't have a dynamic layout.
Here's an example of what can be done. I've added a "V_SPECIFIC" column, containing type specific information. I agree with BlackSwan that the design is not optimal but sometimes we have to work with what we've got.
Note: I've created the tables using the script mentioned above.
SQL> -- Using Lalit Kumar B's query:
SQL> Column v_name format a10
SQL> Column v_id format 9999
SQL> Column v_year format 99999
SQL> Column v_type format a10
SQL> Column v_color format a10
SQL> Column v_specific format a10
SQL> Select tv.v_name
2 , tv.v_id
3 , tv.v_year
4 , tv.v_type
5 , tv.v_color
6 , tv.v_available
7 , decode( tv.v_type
8 , 'Scooter', To_Char(ts.v_mileage)
9 , 'Bike' , To_Char(tb.v_engine)
10 , 'Car' , tc.v_car_type
11 ) v_specific
12 From tb_vehicles tv
13 , tb_scooters ts
14 , tb_cars tc
15 , tb_bikes tb
16 Where tv.v_name = ts.v_name(+)
17 And tv.v_name = tc.v_name(+)
18 And tv.v_name = tb.v_name(+)
19 And tv.v_id = ts.v_id(+)
20 And tv.v_id = tc.v_id(+)
21 And tv.v_id = tb.v_id(+)
22 And tv.v_year = ts.v_year(+)
23 And tv.v_year = tc.v_year(+)
24 And tv.v_year = tb.v_year(+)
25 And tv.v_type = ts.v_type(+)
26 And tv.v_type = tc.v_type(+)
27 And tv.v_type = tb.v_type(+)
28 And lower(tv.v_name) = 'access'
29 /
V_NAME V_ID V_YEAR V_TYPE V_COLOR V_AVAILABLE V_SPECIFIC
---------- ----- ------ ---------- ---------- -------------------- ----------
Access 20 2004 Scooter Black No 50
MHE
|
|
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595537 is a reply to message #595531] |
Wed, 11 September 2013 09:56 |
 |
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
|
Junior Member |
|
|
I know this is ugly. But still, can we have this?  
SET HEADING OFF
SELECT
'V_NAME',
'V_ID',
'V_YEAR',
'V_TYPE',
'V_COLOR',
'V_AVAILABLE',
CASE TV.V_TYPE
WHEN 'Car'
THEN
( SELECT 'V_CAR_TYPE' FROM DUAL )
WHEN 'Scooter'
THEN
( SELECT 'V_MILEAGE' FROM DUAL )
WHEN 'Bike'
THEN
( SELECT 'V_ENGINE' FROM DUAL )
END
AS HEADERR
FROM
TB_VEHICLES TV
WHERE
TV.V_NAME = '&1'
UNION
SELECT
TO_CHAR ( V_NAME ),
TO_CHAR ( V_ID ),
TO_CHAR ( V_YEAR ),
TO_CHAR ( V_TYPE ),
TO_CHAR ( V_COLOR ),
TO_CHAR ( V_AVAILABLE ),
CASE TV.V_TYPE
WHEN 'Car'
THEN
( SELECT
TC.V_CAR_TYPE
FROM
TB_CARS TC
WHERE
TV.V_NAME = TC.V_NAME
AND TV.V_ID = TC.V_ID
AND TV.V_TYPE = TC.V_TYPE )
WHEN 'Scooter'
THEN
( SELECT
TO_CHAR ( TS.V_MILEAGE )
FROM
TB_SCOOTERS TS
WHERE
TV.V_NAME = TS.V_NAME
AND TV.V_ID = TS.V_ID
AND TV.V_TYPE = TS.V_TYPE )
WHEN 'Bike'
THEN
( SELECT
TO_CHAR ( TB.V_ENGINE )
FROM
TB_BIKES TB
WHERE
TV.V_NAME = TB.V_NAME
AND TV.V_ID = TB.V_ID
AND TV.V_TYPE = TB.V_TYPE )
END
ADD_ON
FROM
TB_VEHICLES TV
WHERE
TV.V_NAME = '&1'
/
Enter value for 1: i20
V_NAME V_ID V_YEAR V_TYPE V_COLOR V_AVAILABLE V_CAR_TYPE
i20 60 2012 Car Red Yes Sedan
1 row selected.
SQL> /
Enter value for 1: Access
V_NAME V_ID V_YEAR V_TYPE V_COLOR V_AVAILABLE V_MILEAGE
Access 20 2004 Scooter Black No 50
1 row selected.
[Updated on: Wed, 11 September 2013 09:57] Report message to a moderator |
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595538 is a reply to message #595537] |
Wed, 11 September 2013 10:11 |
|
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
|
Junior Member |
|
|
Also this one works...
WITH TEMP_VEHICLES
AS (SELECT
TV.V_NAME,
TV.V_ID,
TV.V_YEAR,
TV.V_TYPE,
TV.V_COLOR,
TV.V_AVAILABLE,
TO_CHAR ( TC.V_CAR_TYPE ) V_CAR_TYPE
FROM
TB_CARS TC,
TB_VEHICLES TV
WHERE
TV.V_NAME = TC.V_NAME
AND TV.V_ID = TC.V_ID
AND TV.V_TYPE = TC.V_TYPE
UNION
SELECT
TV.V_NAME,
TV.V_ID,
TV.V_YEAR,
TV.V_TYPE,
TV.V_COLOR,
TV.V_AVAILABLE,
TO_CHAR ( TC.V_MILEAGE ) V_MILEAGE
FROM
TB_SCOOTERS TC,
TB_VEHICLES TV
WHERE
TV.V_NAME = TC.V_NAME
AND TV.V_ID = TC.V_ID
AND TV.V_TYPE = TC.V_TYPE
UNION
SELECT
TV.V_NAME,
TV.V_ID,
TV.V_YEAR,
TV.V_TYPE,
TV.V_COLOR,
TV.V_AVAILABLE,
TO_CHAR ( TC.V_ENGINE ) V_ENGINE
FROM
TB_BIKES TC,
TB_VEHICLES TV
WHERE
TV.V_NAME = TC.V_NAME
AND TV.V_ID = TC.V_ID
AND TV.V_TYPE = TC.V_TYPE)
SELECT
*
FROM
TEMP_VEHICLES
WHERE
V_NAME = '&1' |
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Nested Query based on value of Dependent Column [message #595544 is a reply to message #595540] |
Wed, 11 September 2013 10:38 |
 |
Michel Cadot
Messages: 68766
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Although it is quite bad you can improve it with the help of the optimizer smartness using (and and adding the missing condition on year and fixing other things):
WITH TEMP_VEHICLES
AS (SELECT
TV.V_NAME,
TV.V_ID,
TV.V_YEAR,
TV.V_TYPE,
TV.V_COLOR,
TV.V_AVAILABLE,
V_CAR_TYPE ADD_ON
FROM
TB_CARS TC,
TB_VEHICLES TV
WHERE
TV.V_NAME = TC.V_NAME
AND TV.V_ID = TC.V_ID
AND TV.V_YEAR = TC.V_YEAR
AND TV.V_TYPE = TC.V_TYPE
AND TV.V_TYPE = 'Car'
AND TC.V_TYPE = 'Car'
UNION ALL
SELECT
TV.V_NAME,
TV.V_ID,
TV.V_YEAR,
TV.V_TYPE,
TV.V_COLOR,
TV.V_AVAILABLE,
TO_CHAR ( TC.V_MILEAGE ) V_MILEAGE
FROM
TB_SCOOTERS TC,
TB_VEHICLES TV
WHERE
TV.V_NAME = TC.V_NAME
AND TV.V_ID = TC.V_ID
AND TV.V_YEAR = TC.V_YEAR
AND TV.V_TYPE = TC.V_TYPE
AND TV.V_TYPE = 'Scooter'
AND TC.V_TYPE = 'Scooter'
UNION ALL
SELECT
TV.V_NAME,
TV.V_ID,
TV.V_YEAR,
TV.V_TYPE,
TV.V_COLOR,
TV.V_AVAILABLE,
TO_CHAR ( TC.V_ENGINE ) V_ENGINE
FROM
TB_BIKES TC,
TB_VEHICLES TV
WHERE
TV.V_NAME = TC.V_NAME
AND TV.V_ID = TC.V_ID
AND TV.V_YEAR = TC.V_YEAR
AND TV.V_TYPE = TC.V_TYPE
AND TV.V_TYPE = 'Bike'
AND TC.V_TYPE = 'Bike')
SELECT
*
FROM
TEMP_VEHICLES
WHERE
V_NAME = '&1'
/
Regards
Michel
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Aug 21 04:56:49 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
