Ambiguos Column – UNION

QuestionsAmbiguos Column – UNION
Raistlin Majere asked 2 years ago

Hi. I have a statement like that…

Select A as column1 , B as column2 , C as column3 FROM Table1

UNION

Select D as column1 , E as column2 , F as column3 FROM Table2

UNION

Select G as column1 , H as column2 , I as column3 FROM Table3

While I try to use search, I get ambigous column error on WHERE clause… How can I use search on columns?

4 Answers
Abu Ghufran Staff answered 2 years ago

You can specify table.fieldname in column setting which will be used in  where clause:

$col[“dbname”] = “table1.A”;

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Raistlin Majere answered 2 years ago

This was not solved my problem, because I need to search on combined table…

Abu Ghufran Staff answered 2 years ago

The easiest solution would be to shift your UNION query in a subquery and use parent query in grid. This way UNION’d tables will be treated as single table of subquery.

e.g.

SELECT column1, column2, column3 FROM
(
Select A as column1 , B as column2 , C as column3 FROM Table1
UNION
Select D as column1 , E as column2 , F as column3 FROM Table2
UNION
Select G as column1 , H as column2 , I as column3 FROM Table3
) as t

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Alex answered 8 months ago

i had to do this in jqgrid_dist.php to add filters to unions in query

this on sql_count

$sql_count = $this->select_command.$wh;
                if (!empty($wh))
                {
                  $union= strrpos($sql_count,’union’);
                  if ($union !== false)
                     {
                        $sql_count= str_replace(‘union’, $wh.’ union ‘, $sql_count);
                     }
                  }

                $sql_count = “SELECT count(*) as c FROM (“.$sql_count.”) pg_tmp”;

and this on sql_query

 $sel_comm=$this->select_command.$wh;
                if (!empty($wh))
                {
                  $union= strrpos($sel_comm,’union’);
                  if ($union !== false)
                     {
                        $sel_comm= str_replace(‘union’, $wh.’ union ‘, $sel_comm);
                     }
                    // phpgrid_error($sql_count);
                  }

// coment this line
// $this->select_command.$wh.” ORDER BY $sidx $sord LIMIT $limit OFFSET $start”;

// and use this
$SQL = $sel_comm.” ORDER BY $sidx $sord LIMIT $limit OFFSET $start”;

Your Answer

14 + 16 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?