I have a problem with querying multiple SQL tables and getting the results joined correctly. I have 3 tables 1) a main table with articles, 2) a table with a list of subject tags, and 3) an associative table that links tags to articles.
I tried to construct the following query:
- Query a main table
- For each result get the associated tags in a 3rd table (linked by an associative table)
- Where there are no tags associated still include the results from the main table ( I tried various joins)
I have spend a couple of days trying and probably read over 30 posts on stackexchange but still have the problem that I can't both return all results and get the tags correctly associated with the records in the main table. I have tried left / outer / inner joints but they will all return all tags for all records, not associate them correctly.
Here is the first example where the results are correct but all available tags are added to each record. [Results correct but tags wrong 1
Here is the second example where tags are correctly associated with results but results with no tags are not returned [Tags correct but results with no tags not included 2
Here is one SQL code (corresponding to the second screen shot).
SELECT
Ar.Title,
Ar.Analysis_CategoryLevelOne_Name AS MainCategory,
Ar.Analysis_CategoryLevelTwo_Name AS SubCategory,
null AS GenericName,
Ar.Summary,
Ar.Author AS Source,
'Article' AS ContentType,
MATCH (Ar.Title, Summary, Content)
AGAINST ('a*' IN BOOLEAN MODE) AS Relevance,
GROUP_CONCAT(Su.Name) AS Tags
FROM `conArticles` AS Ar,
`refSubjectTags` AS Su,
`linkArticlesToSubjectTags` AS Link
WHERE MATCH (Title, Summary, Content)
AGAINST ('a*' IN BOOLEAN MODE) AND CurrentOrBackup = 'Current'
AND Ar.Articles_id = Link.linkArticlesToSubjectTags_Articles_Id
AND Link.linkArticlesToSubjectTags_SubjectTags_Id = Su.SubjectTags_Id
GROUP BY Ar.Articles_Id
ORDER BY 'Relevance' 'DESC';
I would greatly appreciate help with how to both get all results and the tags correctly associated with the records in the main table.
UPDATE: After suggestions to try two left joins, I have below revised query. Whilst I now get all the correct results from the main table, all the tags are associated with each result, which is not what I want (the output is shown in the first image attached). Regarding the table structure, it corresponds to the one Nosyara posed i.e. I have an intermediate/associative table which only contains id pairs between the main and the tags table ( Articles_Id <=> SubjectTags_Id ). I have added a screen shot of the associative table [Associative table 3
SELECT
Ar.Title,
Ar.Analysis_CategoryLevelOne_Name AS MainCategory,
Ar.Analysis_CategoryLevelTwo_Name AS SubCategory,
null AS GenericName,
Ar.Summary,
Ar.Author AS Source,
'Article' AS ContentType,
MATCH (Ar.Title, Summary, Content)
AGAINST ('a*' IN BOOLEAN MODE) AS Relevance,
GROUP_CONCAT(Su.Name) AS Tags
FROM `refSubjectTags` AS Su,
`conArticles` AS Ar
LEFT JOIN linkArticlesToSubjectTags Link
ON Ar.Articles_id = Link.linkArticlesToSubjectTags_Articles_Id
LEFT JOIN refSubjectTags Su2
ON Link.linkArticlesToSubjectTags_SubjectTags_Id = Su2.SubjectTags_Id
WHERE MATCH (Title, Summary, Content)
AGAINST ('a*' IN BOOLEAN MODE)
GROUP BY Ar.Articles_Id
ORDER BY 'Relevance' 'DESC';
NOTE: This has been solved. See correct code further down under Answers.
You need 2 left joins.
Here I created simplified schema
CREATE TABLE IF NOT EXISTS `main` ( `title` varchar(200));
CREATE TABLE IF NOT EXISTS `tags` ( id int(10), `Name` varchar(200));
CREATE TABLE IF NOT EXISTS `links` ( `title` varchar(200), `tag` int(10));
INSERT INTO main VALUES ('Foo'), ('Bar'), ('Baz');
INSERT INTO tags VALUES (1, 'tag1'), (2, 'tag2'), (3, 'tag3'), (4, 'tag4');
INSERT INTO links VALUES ('Foo', 1), ('Foo', 2), ('Bar', 3), ('Bar', 4), ('Hello', 5), ('Foo', 6);
And this is SQL:
select ar.title, group_concat(t.name) as tags
from main ar
left join links lnk on ar.title=lnk.title
left join tags t on lnk.tag = t.id
group by title
Output:
title tags
----- -----
Bar tag3,tag4
Baz (null)
Foo tag1,tag2