Lets say I have the below data, the last column keyword is nothing but the combination of the other 4 cols.
╔════╦══════╦════════════╦═════════╦════════════╦════════════════════════════════╗
║ ID ║ Name ║ Add1 ║ Add2 ║ Add3 ║ Keyword ║
╠════╬══════╬════════════╬═════════╬════════════╬════════════════════════════════╣
║ 1 ║ John ║ W Brown St ║ Edison ║ Washington ║ JohnW Brown StEdisonWashington ║
║ 2 ║ Paul ║ E High Rd ║ Peapack ║ New Jersey ║ PaulE High RdPeapackNew Jersey ║
║ 3 ║ John ║ Greams Rd ║ Peapack ║ Washington ║ JohnGreams RdPeapackWashington ║
╚════╩══════╩════════════╩═════════╩════════════╩════════════════════════════════╝
Now user can enter any of the text fields Name, Add1, Add2, Add3 and click on search and it should search in all the fields.
For this, I am trying this,
SELECT *, COUNT(ID) AS FREQUENCY FROM TABA WHERE ID IN
(
(SELECT ID FROM TABA WHERE KEYWORD LIKE '%WASHINGTON%')
UNION ALL
(SELECT ID FROM TABA WHERE KEYWORD LIKE '%JOHN%')
UNION ALL
(SELECT ID FROM TABA WHERE KEYWORD LIKE '%PEAPACK%')
)
GROUP BY ID
ORDER BY FREQUENCY
Expected Output:
╔════╦══════╦════════════╦═════════╦════════════╦═══════════╗
║ ID ║ Name ║ Add1 ║ Add2 ║ Add3 ║ Frequency ║
╠════╬══════╬════════════╬═════════╬════════════╬═══════════╣
║ 3 ║ John ║ Greams Rd ║ Peapack ║ Washington ║ 3 ║
║ 1 ║ John ║ W Brown St ║ Edison ║ Washington ║ 2 ║
║ 2 ║ Paul ║ E High Rd ║ Peapack ║ New Jersey ║ 1 ║
╚════╩══════╩════════════╩═════════╩════════════╩═══════════╝
But I am getting all the frequency values as 1. What am I doing wrong?