I tried to look over your other thread but was having trouble following, so this is what I came up with for a new single query.
select salesdetail.product_no, salesdetail.sales_id, sales.contact_id, users.referer,users.ad
from salesdetail,sales, users
where salesdetail.product_no = #pnumber#
and sales.sales_id = salesdetail.sales_id
and users.contact_id = sales.contact_id
I use this method all the time, I'm sure someone can say why the joins are better, but I find this to be easy to understand and implement.
In regards to the: <!--- not all sales have user/contact_id, need to make sure select statement below doesn't run and throw an sql error if variable is empty //---
It maybe just me but for my integer/int fields I always try to have a default value, -1 or 0. Yes people can argue that it adds 1 more character per record to the overall database size but its easier to run queries when I have a database that I control the input of integer fields and the default is numeric. And if you wanted to fix this, its an easy fix, just login to your mySQL manager, set the default value for that field and run a simple query: update tablename set fieldname = 0 where fieldname = ''
If you do want/need to keep the different queries, I do see one thing you can clean up.
FROM:
Code:
<cfquery name="users3" datasource="#request.dsn#">
select referer, ad, contact_id from users where contact_id = #users2.contact_id#;
</cfquery>
TO:
Code:
<cfquery name="users3" datasource="#request.dsn#">
select referer, ad, contact_id
from users
where contact_id = #users2.contact_id#
and length(referer) > 1
and referer <> ' unknown'
and referer not like '%127.1.1.1%'
</cfquery>
This way you don't need the database to pull back data if you aren't going to use it.