> I have a query calling on data in multiple tables. The
> database is MS Access. Here is the query that I am using
>
> SELECT p.name, p.product_id, p.content_id,
> p.creationdate, h.hits AS hits,
> h.clickthroughs AS clicks, s.pagetitle, s.parent, c.pagetitle
> AS category FROM Products p, Product_Hits h, ContentPages s,
> ContentPages c Where p.Status = 1 AND p.product_id =
> h.product_id AND p.content_id = s.content_id AND s.parent =
> c.content_id ORDER BY #sortorder#
>
> All the data is returned fine accept that The HITS and CLICKS
> if there is not HIT available for a particular product in the
> PRODUCT_HITS table, then that product does not show up. I
> could break this into tow separate queries except that I need
> to be able to dynamically change the sort order.
>
> Basically I need to list all of the products even if there
> has been not HIT record created for it. Is this possible
> using a single query?
Yes, you need to create an outer join instead of an inner join:
SELECT p.name,
p.product_id,
p.content_id,
p.creationdate,
h.hits AS hits,
h.clickthroughs AS clicks,
s.pagetitle,
s.parent,
c.pagetitle AS category
FROM Products p,
INNER JOIN ContentPages s ON p.content_id = s.content_id
INNER JOIN ContentPages c ON s.parent = c.content_id
LEFT OUTER JOIN Product_Hits h ON p.product_id = h.product_id
ORDER BY #sortorder#
Of course, I wasn't able to test this query, but this should be in the
ballpark.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241152
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
