I'm having a little trouble setting up a SQL Server VIEW to cut down on
the amount of work CFQuery has to do.
I need to pull in a number of tables for a newsletter system
nl_master (master table containing newsletter title and such)
nl_hed (a list of headers connected by the newsletter id "nl_id"
nl_sto (all stories that go in the newsletter
So, the view below is working fine. Everything is returned as it should
- the one record from the master table, the related header, and all the
stories:
SELECT
dbo.nl_master.nl_id,
dbo.nl_master.nl_title,
dbo.nl_heds.nl_hed,
dbo.nl_sto.sto_title,
dbo.nl_sto.sto_body,
FROM dbo.nl_master
INNER JOIN dbo.nl_sto ON dbo.nl_sto.nl_id = dbo.nl_master.nl_id
INNER JOIN dbo.nl_heds ON dbo.nl_heds.nl_hed = dbo.nl_master.nl_hed
Now, my problem is, there's also:
nl_links (a table listing any number of links for each newsletter)
nl_auth (a table listing any number of related authors ...)
... and a few more.
Problem is, the second I try to add even ONE of them, the view goes nutz
and I'll get the three stories returned 16 times or gawd knows what.
Looking at the links table, I've tried inner and outer joins and I keep
having the same problem.
So adding:
INNER JOIN dbo.nl_links ON dbo.nl_links.nl_nlid = dbo.nl_master.nl_id
... or ...
LEFT JOIN dbo.nl_links ON dbo.nl_links.nl_nlid = dbo.nl_master.nl_id
is doing the same thing.
My code structure on the page is sorta:
<cfoutput>
#newsletter.nl_title#
<cfloop query="newsletter">
#newsletter.sto_title#<br>
#newsletter.sto_body#
</cfloop>
<!--- If I add the link cells to the query with any
type of join - then I add this too: --->
<cfloop query="newsletter">
#newsletter.myLNKS#<br>
</cfloop>
</cfoutput>
I can just use a couple of additional queries for each related table,
but that doesn't seem to efficient to me.
No sure what I've got wrong...suggestions?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323962
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
