Quinlanlent Quinlanlent - 3 months ago 6
MySQL Question

Filtering rows in a table union mysql

Hello I am a having a very hard time in in this bad database structure, and I need help in filtering rows in a table union is it possible?

What's happening is that I am trying to select

pre1, pre2, pre3, pre4, pre5
in the table where the value is not null so all the rows appear but when I am trying to filter out the rows where
trno = <value>
I get an error because I do not know how to do this. Can you help me?

The code I am working on is this

SELECT * FROM
(
SELECT pre1 AS col FROM ccsubject
UNION
SELECT pre2 AS col FROM ccsubject
UNION
SELECT pre3 AS col FROM ccsubject
UNION
SELECT pre4 As col FROM ccsubject
UNION
SELECT pre5 AS col FROM ccsubject
) T1
WHERE col IS NOT NULL


I also tried this

SELECT * FROM
(
SELECT pre1 AS col FROM ccsubject
UNION
SELECT pre2 AS col FROM ccsubject
UNION
SELECT pre3 AS col FROM ccsubject
UNION
SELECT pre4 As col FROM ccsubject
UNION
SELECT pre5 AS col FROM ccsubject
UNION
SELECT trno AS tr FROM ccsubject
) T1
WHERE col IS NOT NULL AND tr = 29005


Here's my table structure

enter image description here

Answer

Try This:

SELECT * FROM
(
SELECT pre1 AS col FROM ccsubject WHERE trno = 29005 AND pre1 IS NOT NULL
UNION
SELECT pre2 AS col FROM ccsubject WHERE trno = 29005 AND pre2 IS NOT NULL
UNION
SELECT pre3 AS col FROM ccsubject WHERE trno = 29005 AND pre3 IS NOT NULL
UNION
SELECT pre4 As col FROM ccsubject WHERE trno = 29005 AND pre4 IS NOT NULL
UNION
SELECT pre5 AS col FROM ccsubject WHERE trno = 29005 AND pre5 IS NOT NULL
) T1