December 2, 2011 at 1:39 pm
I'm not sure if my subject described this well, but here's the question:
Looking a registered products table i have a result set based off customers with a certain product, but now i want to use that result set to bring back a list with the same customers but show all their products within the same table.
Essentially what i'm saying is: IF a customer owns product = 'x'; THEN show all products they own.
can anyone help me with an approach to this query? It feels like it could be a subquery, but not sure how to execute...
December 2, 2011 at 2:14 pm
It sounds like a sub-query.
There are a number of ways to do this. You could put the first query in a CTE and then reference it in the main query, or you could put the first query in a sub-query. I find CTE's easier to read.
Probably something like this would work:
WITH [ProductOwners]
AS
( SELECT CustomerID
FROM ProductSales
WHERE ProductID = 'xyz'
GROUP BY CustomerID /*Returns just one record per customer*/
)
SELECT ProductSales.*
FROM ProductSales
JOIN ProductOwners
ON ProductSales.CustomerID = ProductOwners.CustomerID;
For the Love of Codd, though, don't use * in your production code! Expand it into a list of the columns you need. :w00t:
December 5, 2011 at 10:51 am
Hi Journeyman,
Could you explain how the "WITH" statement comes into play? All the data i'm trying to pull comes from one table...registration.
Is 'ProductOwners' in your statement meant to be a variable to join on?
Thanks,
December 5, 2011 at 10:59 am
here's how i wrote the SQL, and error is below...thoughts?
WITH [ProductOwners]
AS
(SELECT c_id
FROM CO$prod_reg
WHERE product LIKE '%PRODUCTNAME%'
GROUP BY c_id)
SELECT CO$prod_reg.*
FROM CO$prod_reg
JOIN ProductOwners
ON CO$prod_reg.c_id = ProductOwners.c_id;
12:54:31 [WITH - 0 row(s), 0.000 secs] [Error Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH [ProductOwners]
AS
(SELECT c_id
FROM CO$prod_reg
WHERE product LIKE '%ver' at line 1
thanks,
--Cooper008
December 5, 2011 at 11:01 am
This is a sql server forum, not mysql.
December 5, 2011 at 11:02 am
mySQL does not support Common Table Expressions. SQL and Oracle do, and you asked ina SQL forum.
instead, you have to move the example into a named Alias...same thing, just a different syntax:
SELECT ProductSales.*
FROM ProductSales
JOIN
( SELECT CustomerID
FROM ProductSales
WHERE ProductID = 'xyz'
GROUP BY CustomerID /*Returns just one record per customer*/
) ProductOwners
ON ProductSales.CustomerID = ProductOwners.CustomerID;
Lowell
December 5, 2011 at 11:35 am
yes, i know, but help is help. Other forums haven' been so helpful...
December 5, 2011 at 11:37 am
mcooper008 (12/5/2011)
yes, i know, but help is help. Other forums haven' been so helpful...
Can't argue there 😀
December 5, 2011 at 11:43 am
no worries - i appreciate you trying to keep me honest:cool:
December 5, 2011 at 11:44 am
I wrote this using a common table expression. It is just another way of writing a sub-query in SQL Server. I like CTE's because they take the sub-query out of the middle of the statement. To my mind, it makes the query easier to read.
I don't work with MySQL, but here is the same query written with a sub-query instead of a CTE:
SELECT ProductSales.* /*Don't use SELECT * in Production Code!*/
FROM ProductSales
JOIN ( SELECT CustomerID
FROM ProductSales
WHERE ProductID = 'xyz'
GROUP BY CustomerID /*Returns just one record per customer*/
) AS [ProductOwners]
ON ProductSales.CustomerID = ProductOwners.CustomerID;
December 5, 2011 at 11:44 am
-this worked perfect! THANK YOU!
It's been a while since i've written SQL code (obviously!).
thanks again.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply