CODEWITHSUNDEEP
mysqlsqlfull-text-searchself-join
Koperis
Koperis

Reputation: 13

Search with SELF JOIN

Please help to figure it out the right SQL statement for the search. I use SELF JOIN and I get the right results, but I can't get CONCAT_WS to work the right way.

DATA:

id  base_id name                                type
2   0       Alien Isolation                     Base
5   0       Aliens vs. Predator                 Base
4   0       Aliens versus Predator Classic 2000 Base
6   0       Assassin's Creed                    Base
8   2       Corporate Lockdown                  DLC
9   2       Crew Expendable                     DLC
10  2       Trauma                              DLC
11  2       Last Survivor                       DLC
12  2       Safe Haven                          DLC
13  2       Lost Contact                        DLC
14  1       The Trigger                         DLC
22  0       Age of Wonders III                  Base
23  2       Alien Batman                        DLC

SELECT all base games:

    SELECT games.id, games.name, games.type, COUNT(dlc.base_id) AS dlc_count
    FROM games
    LEFT JOIN games dlc
    ON games.id = dlc.base_id
    WHERE games.base_id = 0
    GROUP BY games.id
    ORDER BY games.name ASC
    LIMIT 0, 10

RESULTS:

id  name                                type    dlc_count
22  Age of Wonders III                  Base    0
2   Alien Isolation                     Base    7
4   Aliens versus Predator Classic 2000 Base    0
5   Aliens vs. Predator                 Base    0
6   Assassin's Creed                    Base    0

SEARCH for word "alien" showing base name for the dlc

    SELECT games.id, CONCAT_WS(' -> ', games.name, dlc.name) AS name, games.type, COUNT(dlc.base_id) AS dlc_count
    FROM games
    LEFT JOIN games dlc
    ON games.id = dlc.base_id
    WHERE MATCH(games.name) AGAINST('alien*' IN BOOLEAN MODE)
    GROUP BY games.id
    ORDER BY games.name ASC
    LIMIT 0, 10

I GET THESE RESULTS:

id  name                                    type    dlc_count   
23  Alien Batman                            DLC     0
2   Alien Isolation -> Corporate Lockdown   Base    7
4   Aliens versus Predator Classic 2000     Base    0
5   Aliens vs. Predator                     Base    0

AND THEY SHOULD BE:

id  name                                    type    dlc_count   
23  Alien Isolation  -> Alien Batman        DLC     0
2   Alien Isolation                         Base    7
4   Aliens versus Predator Classic 2000     Base    0
5   Aliens vs. Predator                     Base    0

Upvotes: 1

Views: 88

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Your logic is a bit hard to follow. You seem to want the count of all "dlc"s in the last column. However, you only want the dlc name that contains "Alien". I think this does what you want:

SELECT g.id,
       CONCAT_WS(' -> ',
                 MAX(CASE WHEN lower(dlc.name) LIKE '%alien%' THEN dlc.name END), g.name) AS name, g.type,    
       COUNT(dlc.base_id) AS dlc_count
FROM games g LEFT JOIN
     games dlc
     ON g.id = dlc.base_id
WHERE MATCH(g.name) AGAINST('alien*' IN BOOLEAN MODE)
GROUP BY g.id, g.name
ORDER BY g.name ASC
LIMIT 0, 10

Upvotes: 0

Ray Perea
Ray Perea

Reputation: 5851

Try this SQL

select 
    id, 
    concat_ws(
        ' -> ',
        ( select base.name from games as base where base.id = games.base_id ),
        name
    ) as concat_name, 
    games.type, 
    ( select count(dlc.base_id) from games as dlc where dlc.base_id = games.id )  as dlc_count

from games where 
    match(name) against('alien*' in boolean mode)

order by name asc

limit 0, 10

Upvotes: 1

Related Questions