Sub Query problem

[Buda123]

I have one simple question,
this is my query:

SELECT users.*, (SELECT TOP 1 Product_name FROM Products WHERE user_id =
users.user_id) AS Product
FROM users

Query is working fine, it returns all users and products which are tied to
users, problem is when I am filtering data by product (subquery) I don't want
all users only users which are tied to product.

Did anyone had this before, thanks

[paross1]

Hmmm, if I am reading this right, I would say that you would want to add a a
parameter to the where clause of your sub-select restricting your query to a
specific product, something like this:

SELECT users.*,
(SELECT TOP 1 Product_name
FROM Products
WHERE user_id = users.user_id
AND Product_name = '#productname#') AS Product
FROM users

You could make it optional with <cfif> tags so that if your #productname# is
blank, or doesn't exist, you return users for all products.

Phil

[Buda123]

Thanks Phil,
I wanted to avoid <if> statements in loop since I have 6-7 filters, I wanted
to solve everithing inside query. I got the solution, last night was long :),

Here it is if anybody need it in the future:

SELECT users.*, (SELECT TOP 1 Product_name FROM Products WHERE user_id =
users.user_id <cfif isDefined('Form.bill_end_date')>AND bill_end_date <
#CreateODBCDate(Form.bill_end_date)#</cfif>) AS Product
FROM users
<cfif isDefined('Form.bill_end_date')>
WHERE user_id IN (SELECT user_id FROM Products <cfif
isDefined('Form.bill_end_date')>WHERE bill_end_date <
#CreateODBCDate(Form.bill_end_date)#</cfif>)
</cfif>

[paross1]

I meant by using <cfif> tags within your query, which is exactly what you ended up doing. :D

Phil