0

I have been working on this practice problem for SQL class for the past 30 minutes. I am having a hard time including rows that have NULL values in it or a numerical value of 0. I will post the question and then the query that I have written:

"Write a query to display the tour name, outing date, and number of registered clients for each outing of that tour on each date. Include only outings that were scheduled to occur after October 27, 2013. Include tours with no outings and outings with no registered clients. Sort the result by the number of clients in descending order, and then by outing date in ascending order."

SELECT TOUR_NAME,OUT_DATE,Count(DISTINCT CLIENT_NUM) AS "Num Clients"
FROM TOUR RIGHT JOIN OUTING USING (TOUR_ID) JOIN REGISTER USING (OUT_ID)
WHERE To_Char(OUT_DATE,'YYYY-MM-DD') > '2013-10-27' 
GROUP BY TOUR_NAME,OUT_DATE
ORDER BY "Num Clients" DESC,OUT_DATE;

I cannot figure out how to pull rows with empty cells. It currently only pulls complete rows.

-EXPECTED RESULTS: --TOUR_NAME --OUT_DATE --Num Clients

Weekend Weekday 29-OCT-13 26 Downtown 28-OCT-13 25 Deluxe Day Away 28-OCT-13 23 Quick Break 30-OCT-13 19 Downtown 27-OCT-13 18 Downtown 30-OCT-13 18 Deluxe Day Away 31-OCT-13 12 Washington Heights 31-OCT-13 10 Weekend Weekday 13-NOV-13 0 Downtown 14-NOV-13 0 Beltway 15-NOV-13 0 Weekend Weekday 15-NOV-13 0 Quick Break 16-NOV-13 0 Power Shots 0 Perfect Endings 0 Primary Point 0

MY ACTUAL RESULTS: TOUR_NAME OUT_DATE Num Clients Weekend Weekday 29-OCT-13 26 Downtown 28-OCT-13 25 Deluxe Day Away 28-OCT-13 23 Quick Break 30-OCT-13 19 Downtown 27-OCT-13 18 Downtown 30-OCT-13 18 Deluxe Day Away 31-OCT-13 12 Washington Heights 31-OCT-13 10

It's not including any rows that has a null value or a zero count value within that row.

I appreciate any help. Thank you.

Sw33tH2O
  • 33
  • 1
  • 1
  • 6
  • Unrelated but: `To_Char(OUT_DATE,'YYYY-MM-DD') > '2013-10-27'` because you convert a DATE to string to compare it. It's better to convert the constant string to a date: `out_date > to_date('2013-10-27','YYYY-MM-DD')` that could also make use of an index defined on `out_date`. Btw: from which tables do the columns `out_date` and `client_num` come from? You should use table aliases to make that clear. –  Apr 25 '14 at 14:27
  • can you add some sample data that you are using that includes the nulls or 0s that you want to see in your output. – rhealitycheck Apr 25 '14 at 14:30
  • Use outer joins instead of simple joins. See http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm – StephaneM Apr 25 '14 at 14:44
  • @StephaneM - A `RIGHT JOIN` _is_ an `OUTER JOIN`. Of course, in this case it's going the wrong direction... The OP will need to use another `OUTER JOIN` for `Register`, though, yes. OP: Avoid the use of `USING()` - you don't have any control over which tables it might pick from, so results will not always be what you expect. Eg, many queries requiring self-joins will need want something other than the usual pk/id. Can we get some small sample data and expected results? This would help to check work. – Clockwork-Muse Apr 25 '14 at 14:57
  • Thank you. I have made edits above showing my expected results and what I am actually getting. out_date belongs to OUTING table and client_num belongs to REGISTER table. – Sw33tH2O Apr 25 '14 at 16:29
  • Sorry, I'm having formatting issues to show how the table looks like – Sw33tH2O Apr 25 '14 at 16:37
  • Here are links to pictures of the ERD,expected results, and my actual results: http://tinypic.com/r/346ufyc/8 http://tinypic.com/r/ranlgw/8 http://tinypic.com/r/2vant5v/8 – Sw33tH2O Apr 25 '14 at 16:51

0 Answers0