MYSQL query, getting the same row twice...

[ajlisowski]

I have a quick mysql question and for some reason my brain cant figure it out right now.

 

basically I have a table.

 

Id

Name

Label

1

Product 1

[/td]

[td]1

Product 2

Label 2

 

I want to write a query that pulls the id and the name if the label is empty but pulls two rows, one id and name, one id and label if the label is not empty.

 

So my result would be

 

ID

Name

1

Product 1

2

Product 2

2

Label 2

 

Any ideas how I should go about this? I feel like it shouldnt be too tough, but Im blanking.

[AyKay47]

something like this..

 

$sql = "SELECT * FROM table_name";
$query = mysql_query($sql);
print "<table>";
while($row = mysql_fetch_array($query, MYSQL_ASSOC)){
      $id = $row['id'];
      $name = $row['name'];
      $label = $row['label'];
      if($label == ''){
            print "<tr><td>$id</td><td>$name</td></tr>";
      }else{
            print "<tr><td>$id</td><td>$label</td></tr>";
      } 
}
print "</table>";

[ajlisowski]

Well the problem is that i do ordering by and searching by that column. So I need to work it in the query itself. Otherwise when you order by that column, you will only order by the name and not the label. Same with searching.

 

I believe I have figured it out though.

 

SELECT `id`, `name` AS `labelname` FROM `table` UNION ALL SELECT `id`, `label` AS `labelname` FROM `table` WHERE `label`!='';

 

This way when I order BY `labelname`  it will work properly. My filter query has to be different for each UNION portion, but thats not too big of a deal. Ive tested this and it appears to be working, but I have to explore the results fully to make sure.

 

Thanks for your help though. If it wasnt for some specific requirements, the php solution would work fine and likely be quite a bit quicker.