[ANSWERED] Group_Concat and using a query result in the where clause
-
Two seperate questions, but apply to the same query, so here goes:
SELECT ProdID, ProdTitle, CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName) AS Author FROM Products LEFT OUTER JOIN ...
This works and returns each item with multiple "author" like:
ProdID Prod Title Author
123 Title 1 John Doe
123 Title 1 Jane Doe
465 Title 2 John Doe
456 Title 2 Jane SmithBut what I want is:
ProdID Prod Title Authors
123 Title 1 John Doe, Jane Doe
465 Title 2 John Doe, Jane SmithSo I add a Group Concat to the Author and i get one line and a BLOB in the AUTHORS
SELECT ProdID, ProdTitle, GROUP_CONCAT(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName)) AS Authors FROM Products LEFT OUTER JOIN...
Question #1 is why does this not work. Question #2 is how can I use the resulting "Authors" in the WHERE clause against a search string. Edit 1 ref Question 2: I realize I can redo the concat in the where clause, and that is a fine answer if there are no other options, I just thought you could use the newly created "column" in the where clause directly. Seems cleaner then doing the code twice.
***************** "We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
-
Two seperate questions, but apply to the same query, so here goes:
SELECT ProdID, ProdTitle, CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName) AS Author FROM Products LEFT OUTER JOIN ...
This works and returns each item with multiple "author" like:
ProdID Prod Title Author
123 Title 1 John Doe
123 Title 1 Jane Doe
465 Title 2 John Doe
456 Title 2 Jane SmithBut what I want is:
ProdID Prod Title Authors
123 Title 1 John Doe, Jane Doe
465 Title 2 John Doe, Jane SmithSo I add a Group Concat to the Author and i get one line and a BLOB in the AUTHORS
SELECT ProdID, ProdTitle, GROUP_CONCAT(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName)) AS Authors FROM Products LEFT OUTER JOIN...
Question #1 is why does this not work. Question #2 is how can I use the resulting "Authors" in the WHERE clause against a search string. Edit 1 ref Question 2: I realize I can redo the concat in the where clause, and that is a fine answer if there are no other options, I just thought you could use the newly created "column" in the where clause directly. Seems cleaner then doing the code twice.
***************** "We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
You're not stating what database you're using or what error message you're getting.
GROUP_CONCATneeds aGROUP BYclause in the query. It's unclear if you have one.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
You're not stating what database you're using or what error message you're getting.
GROUP_CONCATneeds aGROUP BYclause in the query. It's unclear if you have one.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
Sorry! Thought the Pre Tags listed the language... Using MySQL. The group by was the error. I also ended up converting back to chars... For anyone's help, here is the code:
SELECT ProdID, ProdTitle, GROUP_CONCAT(convert(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName), CHAR(24))) AS Authors FROM Products LEFT OUTER JOIN ... GROUP BY ProdID
The second question is more of a best practice I guess. Is there a way to then use the "Authors" list in the where clause? I want the whole list returned, but only with matches to a search string. I know I can duplicate the entire GROUP_CONCAT(convert(CONCAT... but it would be nice if it was only once. Also, will the server actually run those twice? as that seems inefficient. Thanks again!
***************** "We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
-
Sorry! Thought the Pre Tags listed the language... Using MySQL. The group by was the error. I also ended up converting back to chars... For anyone's help, here is the code:
SELECT ProdID, ProdTitle, GROUP_CONCAT(convert(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName), CHAR(24))) AS Authors FROM Products LEFT OUTER JOIN ... GROUP BY ProdID
The second question is more of a best practice I guess. Is there a way to then use the "Authors" list in the where clause? I want the whole list returned, but only with matches to a search string. I know I can duplicate the entire GROUP_CONCAT(convert(CONCAT... but it would be nice if it was only once. Also, will the server actually run those twice? as that seems inefficient. Thanks again!
***************** "We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW