If Column Contains Only Null Values Remove From Array.

[AnalyzeThis]

I am creating a table/form that shows all the materials used in a location as well as what makes up those materials. Each material may contain one or more of many fields. Therefore, I need to only get columns from the materials that have values not equal to NULL.

 

DATABASE (simplified overview)

 

Locations

-------------------

|place| etc

-------------------

|pizza hut| etc

|dominos| etc

 

Usages

-------------------

id | material | location

-------------------

|1| pizza | pizza hut

 

 

Material

-------------------

id | name | topping1 | topping 2 | etc

-------------------

|1| pizza | NULL | 50% | NULL | NULL | etc

|2| pizza1 | NULL | 50% | NULL | NULL | etc

|3| pizza2 | NULL | NULL | NULL | NULL | etc

 

Query (simply)

 

SELECT `Locations`.`Location`,

`Materials`.`Name` AS `Input Material`,

FROM `Usages` LEFT JOIN `Locations` ON `Usages`.`Location` = `Locations`.`ID` LEFT JOIN `Materials` ON `Usages`.`Material` = `Materials`.`ID`

WHERE `Locations`.`Company` = '".$company."'

ORDER BY `Locations`.`Location` ASC");

 

 

What I want to return

 

location | material | topping2 | topping45

----------------------------------

|pizza hut | pizza | 50% | NULL

|pizza hut | pizza2 | NULL | 10%

 

 

Any assistance is greatly appreciated.

 

Thank you,

[Jessica]

Do an INNER join on materials.

[AnalyzeThis]

Hi, Thanks for the assitance. However, I do not believe that in itself gets my desired results. I would like to only return queried collumns with fields.

 

Lets say that materials looks like this

 

====================================================================================

Pizza | Topping 1 | Topping 2 | Topping 3 | Topping 4 | Topping 5 | Topping 6 | Topping 7 | etc... Topping 100 |

====================================================================================

Pizza1 | NULL | NULL | NULL | NULL | 35% | NULL | NULL | NULL | NULL |

Pizza2 | 35% | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |

Pizza3 | 35% | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |

Pizza4 | 35% | NULL | 47% | NULL | NULL | NULL | NULL | NULL | NULL |

 

Then what I would like to return only fields WHERE the selected Materials columns do not contain any NULL values. I have a feeling it is the select portion that I am having troubles with.

 

i.e. select

 

I changed the query to the below (again I removed some additional technical jargon).

 

SELECT `Locations`.`Location`,

 

FROM `Usages` LEFT JOIN `Locations` ON `Usages`.`Location` = `Locations`.`ID` INNER JOIN `Materials` ON `Usages`.`Material` = `Materials`.`ID`

WHERE `Locations`.`Company` = '".$company."' AND `Locations`.`Branch` = '".$branch."' AND `Locations`.`Location` LIKE '%".$location."%'

ORDER BY `Locations`.`Location` ASC"

 

I would like:

 

====================================================================================

Pizza | Topping 1 | Topping 3 | Topping 5 |

====================================================================================

Pizza1 | NULL | NULL | 35% |

Pizza2 | 35% | NULL | NULL |

Pizza3 | 35% | NULL | NULL |

Pizza4 | 35% | 47% | NULL |

[ManiacDan]

Unfortunately your database seems to be incorrectly designed. You should have a "toppings" table which links to materials so you could easily pull any available toppings.

 

You may have to do this filtering in PHP or some other language since pure SQL may be impossible.

[AnalyzeThis]

Hmmm I see what your are saying. I disagree with the database being incorrectly designed, it has gone through a normalization process. But then again I am telling you "toppings" when it really isn't so simple as "toppings."

 

I currently trying a PHP excluded array by field function to select which columns to include, having troubles but pushing through.

 

 

What I was really wondering is there SQL way to exclude all NULL columns from a search. IFNULL, <>, WHEREs do not seem to work?

[ManiacDan]

it has gone through a normalization process

I can't tell since you're not telling us the real purpose of these columns, but if these are pizza toppings (or really...anything that repeats like this) it's at most second normal form, when you need third.

 

You can't exclude a column dynamically from your result using SQL. The SELECT clause determines the columns, period. You have to process this in PHP first.

 

You could...nah. I was going to say something about pivot tables, but it's faster to do your query and then filter by empty columns.

 

SQL works on rows, not columns. You can't WHERE against an entire column, only against a row.

[Jessica]

I can't tell since you're not telling us the real purpose of these columns, but if these are pizza toppings (or really...anything that repeats like this) it's at most second normal form, when you need third.

 

...

 

SQL works on rows, not columns. You can't WHERE against an entire column, only against a row.

 

I think OP may be doing many left/inner joins to the same table, to get his final result with column1, column2, column3 type columns.

[ManiacDan]

Very possible, I hadn't thought of that. If that's the case...hmm...there still isn't really a way to drop those tables out of the join.

[Barand]

====================================================================================

Pizza | Topping 1 | Topping 2 | Topping 3 | Topping 4 | Topping 5 | Topping 6 | Topping 7 | etc... Topping 100 |

====================================================================================

Pizza1 | NULL | NULL | NULL | NULL | 35% | NULL | NULL | NULL | NULL |

Pizza2 | 35% | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |

Pizza3 | 35% | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |

Pizza4 | 35% | NULL | 47% | NULL | NULL | NULL | NULL | NULL | NULL |

 

To find where none of the toppings are null you need a query containing

 

... WHERE topping1 IS NOT NULL
AND topping2 IS NOT NULL
AND topping3 IS NOT NULL
AND topping4 IS NOT NULL
AND topping5 IS NOT NULL
AND topping6 IS NOT NULL
AND topping7 IS NOT NULL
AND topping8 IS NOT NULL
...
...
...
AND topping99 IS NOT NULL
AND topping100 IS NOT NULL

 

so if you want to believe that your table is normalized, then carry on writing queries like that.But you're on your own.

[ManiacDan]

He wants to eliminate ENTIRE COLUMNS where the whole column is null.

[Barand]

No problem, if it's normalized

[AnalyzeThis]

Thanks for all the asstance. I figured it out in PHP then went back through and figured it out in MYSQL.

 

As for those who said there is no SQL way to do it...

 

SELECT

CONCAT_WS(',',

IF(COUNT(`Materials`.`topping1` )>0, '`Materials`.`topping1`' ,NULL),

IF(COUNT(`Materials`.`topping2` )>0, '`Materials`.`topping2`' ,NULL),

IF(COUNT(`Materials`.`topping3` )>0, '`Materials`.`topping3`' ,NULL),

etc...

) FROM {fields}

 

The run query on results.

 

SELECT {results} from {fields}

 

*silence*

[Barand]

That "etc" hides potentially 97 similar rows of code (according to your previous post ... topping 100).

 

If you're prepared to keep on writing queries like that, go ahead.

[AnalyzeThis]

I used a loop for each "SHOW COLUMNS FROM" to make it dynamic.

 

I understand why third normal makes sense. However, it is a square peg in a round whole for this project.