1

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!

comwiz756
  • 41
  • 1
  • 9
  • 2
    What is your query supposed to do, in plain English? – Quassnoi May 22 '13 at 19:52
  • It depends how you use `EXISTS`. Just remember that `NULL` is **not** equal to `NULL`. – PM 77-1 May 22 '13 at 19:55
  • so what would be the proper way to do it? – comwiz756 May 22 '13 at 19:58
  • 1
    Your explanation does not really explain anything to me :) What's with Florida? Do you want visits to Florida, or visits on buses which have ever been to Florida, or what? Could you please post some sample data and desired resultset? – Quassnoi May 22 '13 at 19:58

2 Answers2

1

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'
sgeddes
  • 62,311
  • 6
  • 61
  • 83
0
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
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • 1
    The question is tagged `oracle11g`. Since 9i, Oracle supports `ANSI` joins. – Quassnoi May 22 '13 at 20:00
  • this throws error saying "outer join operator(+) not allowed in operand of OR or IN. Try (select where (A(+) and not B)) union all (select where (B)) – comwiz756 May 22 '13 at 20:05
  • @Quassnoi true but since the original question was in ANSI_89 vs ANSI_92 I kept with the OP standard... [as to why people use 89 still...](http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89) – xQbert May 22 '13 at 21:21
  • @anupk which is why I suggested the 2nd version. To me, I wasn't sure I had the + in the correct, and allowed, notation. I did make one change on the OR... which may have caused the issue Misplaced paranthese. – xQbert May 22 '13 at 21:22