CODEWITHSUNDEEP
mysqlsqlselectjoincase
Javier D.
Javier D.

Reputation: 75

SQL JOIN different tables and show NOT NULL values only

I've been trying to show a specific type of result in SQL but haven't been able to. I guess it's possible, but can't figure out how. I've read this article show only those columns which have data value but couldn't solve my problem. I have 4 tables: sparametros (holds parameters codes and descriptions), sparametrosnumericos (holds numeric parameters values and their code), sparametrostexto (holds text parameters and their values), sparametrosmemo (holds memo type parameters) All of them can be joined by their parameter code, however, a parameter code is unique in the sense that for example, given a code, let's say 1210, and let's suppose it's a text type parameter, then that code doesn't exist in numeric nor in memo parameters either. However it exists in the general sparametros table. In other words, sparametros holds all parameters, and the other tables represent sub sets of that main set. I've tried using left join, but couldn't get results.

This is what I have so far:

SELECT P.SPar00Id, P.SPar00Descripcion, 
IF NOT ISNULL(N.SPar00NumValor) THEN 
        N.SPar00NumValor
ELSEIF NOT ISNULL(T.SPar00TextoValor) THEN
        T.SPar00TextoValor
ELSE
        M.SPar00MemoValor
FROM sparametros p
LEFT JOIN sparametrosnumericos N ON N.SPar00NumId = P.SPar00Id
LEFT JOIN sparametrostexto T ON T.SPar00TextoId = P.SPar00Id
LEFT JOIN sparametrosmemo M ON M.SPar00MemoId = P.SPar00Id
WHERE P.SIns00Id = 1 AND
            N.SIns00Id = 1 AND
            T.SIns00Id = 1 AND
            M.SIns00Id = 1;

I'm using MySQL now (with the Navicat client), but also need to be able to get the same results in SQL Server.

The response I'm getting when executing this request is: "[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT ISNULL(N.SPar00NumValor) THEN "

Upvotes: 1

Views: 1785

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT P.SPar00Id, P.SPar00Descripcion, 
      (CASE WHEN N.SPar00NumValor IS NOT NULL THEN N.SPar00NumValor
            WHEN T.SPar00TextoValor IS NOT NULL THEN T.SPar00TextoValor
            ELSE M.SPar00MemoValor 
       END) colName
FROM sparametros p
LEFT JOIN sparametrosnumericos N ON N.SPar00NumId = P.SPar00Id
LEFT JOIN sparametrostexto T ON T.SPar00TextoId = P.SPar00Id
LEFT JOIN sparametrosmemo M ON M.SPar00MemoId = P.SPar00Id
WHERE P.SIns00Id = 1 AND N.SIns00Id = 1 AND T.SIns00Id = 1 AND M.SIns00Id = 1;

Upvotes: 1

Related Questions