CODEWITHSUNDEEP
sqloracle-databaseoracle11g
comwiz756
comwiz756

Reputation: 41

Making sure that the query returns null values too in the output

I am just curious on how to make sure that the query returns null values too along with other values.

UPDATE: The following query is supposed to output a table with a location id, visit date visit time and bus name for each visits. The query below gives the output but does not include the nulls like in the table below. The result should include nulls also.

There are 3 tables in total: Location1, bus & visit. So I am using this(below) query to extract IDs and join all the tables. The query looks for visits only to Florida.

For instance, take the following query:

SELECT LOC.LOC_ID, VISIT.VISIT_DATE, VISIT.VISIT_TIME, BUS.BUS_NAME 
FROM BUS, LOCATION1, VISIT
WHERE VISIT.BUS_ID = BUS.BUS_ID 
AND VISIT.LOC_ID = LOCATION1.LOC_ID 
AND BUS.BUS_NAME IN (
SELECT BUS_NAME 
FROM BUS 
WHERE BUS_ID IN (
SELECT BUS_ID 
FROM VISIT 
WHERE LOC_ID IN (
SELECT LOC_ID 
FROM LOCATION1 
WHERE LOC_NAME='Florida')));

Let us suppose, there are visits which have not yet been assigned a bus and thus some values are null.

So, from what I believe using IN operator will return set of values but not the null values. What if i use exists instead?

The table can look like this:

 Loc_name   Loc_id         visit_date          visit_time              bus_name
  Florida     1            26-mar-2009           2:00pm                  xyz
  Florida     3            29-jul-2010           3:00pm                  abc
  Florida     8            22-may-2013           2:50pm                  (null)
  .
  .
  .

hope someone can help me with this.

thanks

UPDATE 2:

Found the Answer!

SELECT LOC.LOC_ID, VISIT.VISIT_DATE, VISIT.VISIT_TIME, BUS.BUS_NAME 
FROM LOCATION1
LEFT JOIN VISIT ON LLOCATION1.LOC_ID = VISIT.LOC_ID 
LEFT JOIN BUS ON BUS.BUS_ID = VISIT.BUS_ID
WHERE LOC_NAME = 'Florida';

Thanks again!

Upvotes: 1

Views: 429

Answers (2)

xQbert
xQbert

Reputation: 35323

SELECT LOC.LOC_ID, VISIT.VISIT_DATE, VISIT.VISIT_TIME, BUS.BUS_NAME 
FROM BUS, LOCATION1, VISIT
WHERE VISIT.BUS_ID = BUS.BUS_ID(+) 
AND VISIT.LOC_ID = LOCATION1.LOC_ID(+)
AND (BUS.BUS_NAME IN (
SELECT BUS_NAME 
FROM BUS 
WHERE BUS_ID IN (
SELECT BUS_ID 
FROM VISIT 
WHERE LOC_ID IN (
SELECT LOC_ID 
FROM LOCATION1 
WHERE LOC_NAME='Florida'))) OR Bus.Bus_name is null);

You need to do outer joins instead of full join which is what the (+) notation is for. IN addition you need to handle where the bus.Bus_name could be null.

Though I'm not sure where the (+) go in this instance I would need to understand the relationships of when a visit.bus and bus.bus relate as well as a visit.loc and a location relate.

Though I think this is easier to read

SELECT LOC.LOC_ID, VISIT.VISIT_DATE, VISIT.VISIT_TIME, BUS.BUS_NAME 
FROM Location1 Loc
LEFT JOIN VISIT on Loc1.Loc_ID = Visit.Loc_ID and Loc_Name = 'Florida'
LEFT JOIN BUS on Bus.Bus_ID = Visit.Bus_ID

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

If I'm understanding your question/query correctly, I think it can be greatly simplified (not sure why you're using all the IN statements). Try using an OUTER JOIN to get your desired results:

SELECT LOC.LOC_ID, VISIT.VISIT_DATE, VISIT.VISIT_TIME, BUS.BUS_NAME 
FROM LOCATION1 LOC
    LEFT JOIN VISIT ON LOC.LOC_ID = VISIT.LOC_ID
    LEFT JOIN BUS ON VISIT.BUS_ID = BUS.BUS_ID 
WHERE LOC.LOC_NAME='Florida'

Upvotes: 1

Related Questions