I need help getting back the right results from a query.
I have an article table (articles), an authors table (authors), and a
join table to reference the two (articles_authors) .
The articles have multiple authors.
I am trying to do a search of author first/ last name and return the
list of articles with the all the associated authors.
So far I have tried the following queries:
=================================================
SELECT Article.id, Article.title, Article.source, CONCAT_WS('-',
Author.last_name, Author.first_name) AS Auths FROM articles AS
Article, Authors AS Author, articles_authors AS ArticlesAuthors WHERE
Author.first_name LIKE '%a%' AND ArticlesAuthors.authors_id =
Author.id AND Article.id = ArticlesAuthors.articles_id
=================================================
=================================================
SELECT Article.id, Article.title, Article.source, GROUP_CONCAT
(CONCAT_WS('-', Author.last_name, Author.first_name)) AS Auths FROM
articles AS article, Authors AS Author, articles_authors AS
ArticlesAuthors WHERE Author.first_name LIKE '%a%' AND
ArticlesAuthors.articles_id = Article.id AND Author.id =
ArticlesAuthors.authors_id
=================================================
The first query will return all the proper information, but one line
for every author of the article. The second article returns only one
line with all the authors grouped together.
I am just looking for a combination of the two, a line with the
article information with the associated authors.
Any help would be greatly appreciated.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
