SQL problem

[Jasevinda]

I have a database full of contact details but some havethe same email address
but have different names all these email address have to be emailed info
automatically but at the moment they get duplicate emails is there a way in my
SQL query that i can get rid of the duplicate emails? J

[vkunirs]

Hi

do you mean that, you want to get the unique mail id's.

then use the distinct in your query.
select distinct email from table.

[sdwebguy99]

... or use the CFMAIL QUERY and GROUP attributes. It will work the same as a
CFOUTPUT GROUP attribute. (One email will be sent, but you can loop over each
duplicate item if you need to list separate things, for example.)

[Andy]

Can anyone see what i'm doing wrong here please?

SELECT category.CategoryID, category.Category, category.Category_Image,
category.Category_Text, products.ProductID, products.CategoryID,
products.Product_Title, products.Product_Description,
products.Product_Image, products.Product_Price, Prices.ID, Prices.UserID,
Prices.ProductID, Prices.Price, retailers.UserID, retailers.Retailer_Name,
retailers.Retailer_Logo, retailers.username, retailers.show_prices
FROM retailers INNER JOIN ((category INNER JOIN products ON
category.CategoryID = products.CategoryID) INNER JOIN Prices ON
products.ProductID = Prices.ProductID) ON retailers.UserID = Prices.UserID
WHERE category.categoryID = MMColParam1 AND retailer.Retailer_Name =
MMColParam2

MMColParam1 2 Request("CategoryID")
MMColParam2 andy Request.Cookies("ckusername")

When i test the RS i get the 'too few parameters. Expected 2


Thanks in advance
Andy

[jgladnick]

I have an events scheduler program which displays events based on what category
the end user (via the web) selects.
-->So if you click on "workshops", it will display only the workshops, and if
you click on "meals" it will display only "meals".

My problem is that I don't even want to give the option to click on if no
items have been input for a given category.
-->So if there aren't any meals at a certain event, the user should not have
the opportunity to click on meals to begin with.

This change was thrown in at the last second, and I'm trying to avoid changing
the entire program in order to accommodate it. I am sure there is an easy way
to link the tables in SQL, but after 30 minutes of trial and error im still
stumped.

Live link: [url]http://www.pma.com/fsdevarea/schedule.cfm[/url]

All the items on the schedule are contained in one table, called
ItemCategories. After the query runs that grabs all the applicable items, i
need to run another one to determine which categories have information. There
are 13 categories, and each ItemCategory record has columns Category1..13,
which can either be a 1 or 0.

So basically, I need to know if any columns have no matching records, and then
not display them.

How can i construct a SQL query that will output only the categories that have
valid records?






Notes: I am aware the program became a little klugey :)

<cfset topbg = 1> <!--- Which background image to use --->

<!--- Local Variables --->
<cfif IsDefined("URL.CategoryID")>
<!--- Get Category Name --->

<CFQUERY NAME="GrabCatName" DATASOURCE="#dsn#" >
SELECT CategoryDescription
FROM ItemCategories
WHERE CategoryID = #URL.CategoryID#
</cfquery>

<cfset PageTitle = "#GrabCatName.CategoryDescription#: Schedule">
<cfelse>
<cfset PageTitle = "Complete Schedule">
</cfif>


<!--- Get all Items for specified Category --->
<CFQUERY NAME="GetCategoryNames1" DATASOURCE="#dsn#" >
SELECT CategoryName, CategoryDescription, CategoryID
FROM ItemCategories
ORDER BY CategoryName
</cfquery>

<!--- Get all Items for Networking Opportunities--->
<CFQUERY NAME="GetCategoryNames2" DATASOURCE="#dsn#" >
SELECT CategoryName, CategoryDescription, CategoryID
FROM ItemCategories
WHERE CategoryID <= 6
ORDER BY CategoryID, CategoryName
</cfquery>

<!--- Get all Items for Learning & Educational events--->
<CFQUERY NAME="GetCategoryNames3" DATASOURCE="#dsn#" >
SELECT CategoryName, CategoryDescription, CategoryID
FROM ItemCategories
WHERE CategoryID >= 6
ORDER BY CategoryID DESC, CategoryName
</cfquery>


<!--- Get all categories specified event --->
<CFQUERY NAME="GetItems" DATASOURCE="#dsn#" >
SELECT ID, ItemName, ItemDescription, ItemDate, StartTime, EndTime, Location,
Teaser, ItemNote
FROM ItemInformation
WHERE EventID = 1
<cfif IsDefined("url.type")>
<cfif URL.TYPE EQ "all"></cfif>
<cfif URL.TYPE EQ "net">
AND Category1 = 1
OR Category2 = 1
OR Category3 = 1
OR Category4 = 1
OR Category5 = 1
OR Category6 = 1
</cfif>
<cfif URL.TYPE EQ "edu">
AND Category6 = 1
OR Category7 = 1
OR Category8 = 1
OR Category9 = 1
OR Category10 = 1
OR Category11 = 1
OR Category12 = 1
OR Category13 = 1
</cfif>
<cfelse>
<cfif IsDefined("URL.CategoryID")>AND Category#URL.CategoryID# = 1</cfif>
</cfif>

ORDER by Itemdate, StartTime
</cfquery>



<cfinclude template="templates/top.cfm">


<style type="text/css">

#tablist{
padding: 3px 0;
margin-left: 0;
margin-bottom: 0;
margin-top: 0.1em;
font: bold 14px arial;
}

#tablist li{
list-style: none;
display: inline;
margin: 0;
}

#tablist li a{
padding: 3px 0.5em;
margin-left: 3px;
border-bottom: none;
background: white;
}

#tablist li a:link, #tablist li a:visited{
color: navy;
}

#tablist li a.current{
background: lightyellow;
}

#tabcontentcontainer{
width: 900px;
/* Insert Optional Height definition here to give all the content a unified
height */
padding: 5px;
}

.tabcontent{
display:none;
}

</style>

<script type="text/javascript">

/***********************************************
* Tab Content script- ? Dynamic Drive DHTML code library ([url]www.dynamicdrive.com[/url])
* This notice MUST stay intact for legal use
* Visit Dynamic Drive at [url]http://www.dynamicdrive.com/[/url] for full source code
***********************************************/

//Set tab to intially be selected when page loads:
//[which tab (1=first tab), ID of tab content to display]:
var initialtab=[1, "addnew"]

////////Stop editting////////////////

function cascadedstyle(el, cssproperty, csspropertyNS){
if (el.currentStyle)
return el.currentStyle[cssproperty]
else if (window.getComputedStyle){
var elstyle=window.getComputedStyle(el, "")
return elstyle.getPropertyValue(csspropertyNS)
}
}

var previoustab=""

function expandcontent(cid, aobject){
if (document.getElementById){
highlighttab(aobject)
detectSourceindex(aobject)
if (previoustab!="")
document.getElementById(previoustab).style.display ="none"
document.getElementById(cid).style.display="block"
previoustab=cid
if (aobject.blur)
aobject.blur()
return false
}
else
return true
}

function highlighttab(aobject){
if (typeof tabobjlinks=="undefined")
collecttablinks()
for (i=0; i<tabobjlinks.length; i++)
tabobjlinks[i].style.backgroundColor=initTabcolor
var themecolor=aobject.getAttribute("theme")? aobject.getAttribute("theme") :
initTabpostcolor

aobject.style.backgroundColor=document.getElementB yId("tabcontentcontainer").sty
le.backgroundColor=themecolor
}

function collecttablinks(){
var tabobj=document.getElementById("tablist")
tabobjlinks=tabobj.getElementsByTagName("A")
}

function detectSourceindex(aobject){
for (i=0; i<tabobjlinks.length; i++){
if (aobject==tabobjlinks[i]){
tabsourceindex=i //source index of tab bar relative to other tabs
break
}
}
}

function do_onload(){
var cookiename=(persisttype=="sitewide")? "tabcontent" :
window.location.pathname
var cookiecheck=window.get_cookie && get_cookie(cookiename).indexOf("|")!=-1
collecttablinks()
initTabcolor=cascadedstyle(tabobjlinks[1], "backgroundColor",
"background-color")
initTabpostcolor=cascadedstyle(tabobjlinks[0], "backgroundColor",
"background-color")
if (typeof enablepersistence!="undefined" && enablepersistence && cookiecheck){
var cookieparse=get_cookie(cookiename).split("|")
var whichtab=cookieparse[0]
var tabcontentid=cookieparse[1]
expandcontent(tabcontentid, tabobjlinks[whichtab])
}
else
expandcontent(initialtab[1], tabobjlinks[initialtab[0]-1])
}

if (window.addEventListener)
window.addEventListener("load", do_onload, false)
else if (window.attachEvent)
window.attachEvent("onload", do_onload)
else if (document.getElementById)
window.onload=do_onload


</script>

<script type="text/javascript">

//Dynamicdrive.com persistence feature add-on

var enablepersistence=true //true to enable persistence, false to turn off (or
simply remove this entire script block).
var persisttype="local" //enter "sitewide" for Tab content order to persist
across site, "local" for this page only

function get_cookie(Name) {
var search = Name + "="
var returnvalue = "";
if (document.cookie.length > 0) {
offset = document.cookie.indexOf(search)
if (offset != -1) {
offset += search.length
end = document.cookie.indexOf(";", offset);
if (end == -1) end = document.cookie.length;
returnvalue=unescape(document.cookie.substring(off set, end))
}
}
return returnvalue;
}

function savetabstate(){
var cookiename=(persisttype=="sitewide")? "tabcontent" :
window.location.pathname
var cookievalue=(persisttype=="sitewide")?
tabsourceindex+"|"+previoustab+";path=/" : tabsourceindex+"|"+previoustab
document.cookie=cookiename+"="+cookievalue
}

window.onunload=savetabstate

</script>



<script>
//Modeless window script- By DynamicDrive.com
//for full source code and terms of use
//visit [url]http://www.dynamicdrive.com[/url]

function modelesswin(url,mwidth,mheight){
if (document.all&&window.print) //if ie5

eval('window.showModelessDialog(url,"","help:0;res izable:1;dialogWidth:'+mwidth+
'px;dialogHeight:'+mheight+'px")')
else

eval('window.open(url,"","width='+mwidth+'px,heigh t='+mheight+'px,resizable=1,sc
rollbars=1")')
}

//To load via link, use something like below:
//<a href="javascript:modelesswin('http://yahoo.com',600,400)">Click here</a>
</script>




<!--- Display Schedule --->
<cfoutput>#GetItems.recordcount# total items</cfoutput>
<BR><BR>










<ul id="tablist">
<li>Sort by:&nbsp;&nbsp;</li>
<li><a href="schedule.cfm" onmouseover="return overlib('Display All scheduled
items', DELAY, 200, VAUTO, HAUTO);" onmouseout="return nd();" class="current"
onClick="window.location='schedule.cfm';return expandcontent('sc1', this);"
theme="#FFFFFF" >Complete Schedule</a></li>
<li><a href="?type=net" onmouseover="return overlib('Display All Networking
Opportunities', DELAY, 200, VAUTO, HAUTO);" onmouseout="return nd();"
onClick="window.location='?type=net';return expandcontent('sc2', this);"
theme="#FFFFFF" >Networking</a></li>
<li><a href="?type=edu" onmouseover="return overlib('Display All Learning &
Education Items', DELAY, 200, VAUTO, HAUTO);" onmouseout="return nd();"
onClick="window.location='?type=edu';return expandcontent('sc3', this)"
theme="#FFFFFF" >Learning</a></li>
</ul>

<DIV id="tabcontentcontainer">

<div id="sc1" class="tabcontent" style="font:9pt arial;">

<cfoutput query="GetCategoryNames1">
<a onmouseover="return overlib('#CategoryDescription#', DELAY, 200, VAUTO,
HAUTO);" onmouseout="return nd();" class="olivelink"
href="schedule.cfm?CategoryID=#CategoryID#">#Categ oryName#</a>&nbsp;&nbsp;
</cfoutput>


</div>


<!--- 2nd id --->

<div id="sc2" class="tabcontent">

<cfoutput query="GetCategoryNames2">
<a onmouseover="return overlib('#CategoryDescription#', DELAY, 200, VAUTO,
HAUTO);" onmouseout="return nd();" class="olivelink"
h

[Funke]

I don't want to sound preachy but this is not as easy as it should be because
your database is very inefficient - there is no relational data that I can see.
Ideally you would have an Items table with a single field in it for CategoryID
(the ID of the Category from the Categories table) or, if an Item can belong to
multiple categories, a table called something like Item_Categories (i.e. a
many-to-many relationship) that maintains which Items belong to which Category.
If this were the case you would simlpy perform a join between the tables to get
which Categories have items.

Anyhow, if you must do this with the existing database I suggest looping
through each Category and building a query for each CategoryID that checks the
ItemInformation table's corresponding column, i.e. something like (there is
probably a better way than this, but should work):


<!--List of all categories --->
<CFQUERY NAME="GetCategories" DATASOURCE="#dsn#" >
SELECT CategoryName, CategoryDescription, CategoryID
FROM ItemCategories
ORDER BY CategoryName
</CFQUERY>

<CFOUTPUT query="GetCategories">

<!--Query to see if category has items --->
<cfset qry = "SELECT ID FROM ItemInformation WHERE Category#CategoryID# = 1">

<CFQUERY NAME="GetCatItem" DATASOURCE="#dsn#" >
#qry#
</CFQUERY>

<CFIF GetCatItem.RecordCount GT O>
<!--- Output the Category here if it has items --->
#CategoryName# <br>
</CFIF>

</CFOUTPUT>

[jorgepino]

can you post some code and the error message you are getting

[CF_Oracle]

Maybe you mean by "does not exist " that value is NULL(?) Then what kind of result would you like to get? Depending on your answers it could be different solution for this problem.

[Scott*e]

SELECT *
FROM table
WHERE
<cfif isdefined("sessionFacilityID")>
FacilityID= #session.FacilityID#
<cfelse>
1=2
</cfif>

[BKBK]

>... WHERE FacilityID=#FacilityID#
What database type did you set fot the column FacilityID? For example, you
might
need to use '#FacilityID#', instead of #FacilityID#, if the type is varchar.

Do something like this before the query, to make sure FacilityID is always
defined in your code before the query runs. To me, the phrase, If the
FacilityID
does not exist in the table only makes sense if, by that, you mean the literal
value #FacilityID# does not appear in any row of the FacilityID column. In
that
case the query should just return nothing, not an error.