CODEWITHSUNDEEP
mysqlselect
Heena
Heena

Reputation: 754

how to omit columns having null value in select query in mysql?

Select Header1,Header2,Header3,ExtraHeader from tbl_likeinfo where HotelID=2

Here Header3 and ExtraHeader may have null values.In that case I don't need only that null value in my query result but that row containing other column values should be given.

How to achieve that?

Upvotes: 1

Views: 7717

Answers (2)

RC.
RC.

Reputation: 28207

You can't dynamically change the columns selected in the result set based on the data the query is selecting. If you don't want to handle nulls, you could use a CASE statement on the header values to change them to a value of your choosing that represents you could treat the same way, but I wouldn't recommend that approach.

You may want to change your approach. It appears you have your table layout using columns to represent each unique header. If you changed your layout so the table was:

hotel_id     NUMBER
header_name  VARCHAR2(50)
header_value VARCHAR2(100)

Then when you're inserting the headers, do one insert per header received at that time. Then change your select to:

SELECT header_name, header_value FROM headers WHERE hotel_id = 2;

If you happen to be storing header values that were null and when you pull them out, you want to eliminate those, then:

SELECT header_name, header_value 
FROM headers 
WHERE hotel_id = 2 AND header_value is not null;

If order is important to you, then add a column to the table to store the order as you insert them. This layout also allows you to store any amount of header information without having to change the layout of the table in the future.

Hope this helps.

Upvotes: 2

m0skit0
m0skit0

Reputation: 25873

AND Header3 IS NOT NULL AND ExtraHeader IS NOT NULL

Upvotes: 0

Related Questions