RE: count(*) = 0 and I still need the row

From: Javier Morales <Javier_at_infojobs.net>
Date: Wed, 15 Nov 2000 17:26:46 +0100
Message-Id: <10681.122124@fatcity.com>

have you heard about Outer join queries??

you have to add the (+) sign in one side of the join...

where html_page.pk =3D page_views.fk (+)

-----Mensaje original-----
De: Dave Morgan [mailto:dmorgan_at_bartertrust.com] Enviado el: mi=E9rcoles 15 de noviembre de 2000 16:30 Para: Multiple recipients of list ORACLE-L Asunto: count(*) =3D 0 and I still need the row

Hi All,

	For the SQL experts out there.=20
	I have the tables

	PAGE_VIEWS
		VIEW_ID		NUMBER(10,0)
		HTML_PAGE_ID	NUMBER(10,0)
		VIEW_DATE	DATE

	HTML_PAGE
		PAGE_ID		NUMBER(10,0)
		URL		VARCHAR2(255)
		....

	PAGE_VIEWS.HTML_PAGE_ID is a foreign key to HTML_PAGE.PAGE_ID
	I want to get a list of all pages and number of times they are
viewed
	in the past month.=20

	SELECT
		hp.URL,
		count(pv.HTML_PAGE_ID)
	FROM
		PAGE_VIEWS pv, HTMP_PAGE hp
	WHERE=20
		hp.PAGE_ID =3D pv.HTML_PAGE_ID
	AND
		pv.VIEW_DATE > addmonths(SYSDATE, -1);

	This works fine except when the page has not been viewed in which
	case it does not show up in the report. I would like it to show up
	with the number 0. Any ideas?

	This is in a stored procedure so I can be very flexible.

TIA
Dave		=09

--=20

Dave Morgan
Senior Database Administrator
Internet Barter Inc.
www.bartertrust.com
408-982-8774
--=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--=20

Author: Dave Morgan
  INET: dmorgan_at_bartertrust.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Nov 15 2000 - 10:26:46 CST