UNION and Fetch Row

[afridy]

Hai folks,

	
$query="SELECT * FROM candidates INNER JOIN approvals 
			ON candidates.cid = approvals.cid 
			WHERE agency_code='$agency_code'
			
			UNION
			
            SELECT * FROM candidates_ans INNER JOIN approvals_ans 
			ON candidates_ans.cid = approvals_ans.cid 
			WHERE agency_code='$agency_code'";

if ($result=mysql_query($query) or die (mysql_error()));
while ($row=mysql_fetch_array($result)){

xxxxxx

}

I want to know the table name currently the while loop is fetching records

[swampBoogie]

You need to add that information as a constant in the select list.

select <columnsOfInterest>,
       'Candidates' as origin
  from candidates
  join approvals
    on candidates.cid = approvals.cid 
 where agency_code = '$agency_code'
 union all
select <columnsOfInterest>,
       'Candidates_ans'
  from candidates_ans 
  join approvals_ans
    on candidates_ans.cid = approvals_ans.cid 
 where agency_code = '$agency_code

You can use union all which avoids the need for attempting to eliminate any duplicates in the result set.

[afridy]

You need to add that information as a constant in the select list.

select <columnsOfInterest>,
       'Candidates' as origin
  from candidates
  join approvals
    on candidates.cid = approvals.cid 
 where agency_code = '$agency_code'
 union all
select <columnsOfInterest>,
       'Candidates_ans'
  from candidates_ans 
  join approvals_ans
    on candidates_ans.cid = approvals_ans.cid 
 where agency_code = '$agency_code

You can use union all which avoids the need for attempting to eliminate any duplicates in the result set.

Hai Swampboogi,
thanks for the reply, ill look at ur solution.
mean time on google i found a similler tem table solution. but know idea how to extract the table name while fetching.

	$query="SELECT * FROM candidates as tbl_candidates INNER JOIN approvals 
			ON tbl_candidates.cid = approvals.cid 
			WHERE agency_code='$agency_code'
			
			UNION
			
            SELECT * FROM candidates_ans as tbl_candidates_ans INNER JOIN approvals_ph_ans 
			ON tbl_candidates_ans.cid = approvals_ph_ans.cid 
			WHERE agency_code='$agency_code'";

[swampBoogie]

That rewrite will not help. The table names are not part of the result set, that's why you need to explicitly add a column with that information.

[afridy]

Hai swamp

	$query="SELECT 'cid' as cid1,'position' as position1,'name' as name1,'dob' as dob1,'phone' as phone1,'education' as education1,'experience' as experience1,'salary' as salary1,'date' as date1  
	        FROM candidates INNER JOIN approvals 
			ON candidates.cid = approvals.cid 
			WHERE agency_code='$agency_code'
			
			UNION
			
            SELECT 'cid' as cid2,'position' as position2,'name' as name2,'dob' as dob2,'phone' as phone2,'education' as education2,'experience' as experience2,'salary' as salary2,'date' as date2 
			FROM candidates_ans as tbl_candidates_ans INNER JOIN approvals_ans 
			ON tbl_candidates_ans.cid = approvals_ans.cid 
			WHERE agency_code='$agency_code'";

if ($result=mysql_query($query) or die (mysql_error()));
$tot=mysql_num_rows($result);
while ($row=mysql_fetch_array($result)){

   echo $row['cid1'];
   echo $row['cid2'];
   echo "<br>";
}

its not working, no looping happening it seem

[afridy]

sorry, in my prvious post, the second table query after union is little messed up. here is the correct one

	$query="SELECT 'cid' as cid1,'position' as position1,'name' as name1,'dob' as dob1,'phone' as phone1,'education' as education1,'experience' as experience1,'salary' as salary1,'date' as date1,'candidates' as tbl1  
	        FROM candidates INNER JOIN approvals 
			ON candidates.cid = approvals.cid 
			WHERE agency_code='$agency_code'
			
			UNION
			
            SELECT 'cid' as cid2,'position' as position2,'name' as name2,'dob' as dob2,'phone' as phone2,'education' as education2,'experience' as experience2,'salary' as salary2,'date' as date2,'candidates_ans' as tbl2 
			FROM candidates_ans INNER JOIN approvals_ph_ans 
			ON candidates_ans.cid = approvals_ph_ans.cid 
			WHERE agency_code='$agency_code'";
			
}

Thanks swamboogi,
now it works fine!!! great lesson!

[guido2004]

1) Get rid of the quotes around the column names, or use backticks instead. Using quotes makes that 'cid' (for example) is considered a string instead of a column name.
2) The columns in query result will have the names from the first select. So it's useless to give them other names in the select after the UNION. For example, the first column will be called cid, and not cid2 (a column can't have two names).

@swampBoogie ; in this case UNION ALL isn't necessary, because adding the table name in a column, no rows from the two tables will be identical

[afridy]

I am sorry, i am totally messed up today

Now as per #2 i have modified my query

			SELECT 'cid' as cid1,'candidates' as tbl1
	        FROM candidates
			INNER JOIN approvals 
			ON candidates.cid = approvals.cid 
			WHERE agency_code='$agency_code'
			
			UNION
			
			SELECT 'cid' as cid1,'candidates_ans' as tbl2
	        FROM candidates_ans 
			INNER JOIN approvals_ans 
			ON candidates_ans.cid = approvals_ans.cid 
			WHERE agency_code='$agency_code'";

mysql num rows shows only 1 matching record for above.This is false.
because, if i executecute the above query with only the first SELECT (without union and below that), it produces 12 results which is correct. whats wrong?

[swampBoogie]

@guido

I know that there will be no duplicates in the result. The union all is intended as a hint to the optimizer that there is no need to eliminate duplicates.

[guido2004]

The union all is intended as a hint to the optimizer that there is no need to eliminate duplicates.

Ah, that's interesting. Thanks.

mysql num rows shows only 1 matching record for above.This is false.
because, if i executecute the above query with only the first SELECT (without union and below that), it produces 12 results which is correct. whats wrong?

Echo out the query, and run it in PHPMyAdmin to see the result. But my guess is it's because of #1 in my post above.

[afridy]

1) Get rid of the quotes around the column names, or use backticks instead. Using quotes makes that 'cid' (for example) is considered a string instead of a column name.
2) The columns in query result will have the names from the first select. So it's useless to give them other names in the select after the UNION. For example, the first column will be called cid, and not cid2 (a column can't have two names).

@swampBoogie ; in this case UNION ALL isn't necessary, because adding the table name in a column, no rows from the two tables will be identical

Thanks for the valuable tips!!

[afridy]

Hai folks,

i am now able to see the correct totals by fixing the following.

Ur suggestions

*remove quotes from tables name
and
*union all are helped to solve the problem.

Thank you for the great help

            SELECT candidates.cid as cid1,'candidates' as tblname
            FROM candidates
            INNER JOIN approvals
            ON candidates.cid = approvals.cid
            WHERE agency_code='$agency_code'
           
            UNION ALL
           
            SELECT candidates_ans.cid as cid1,'candidates_ans' as tblname
            FROM candidates_ans
            INNER JOIN approvals_ph_ans
            ON candidates_ans.cid = approvals_ph_ans.cid
            WHERE agency_code='$agency_code'";

Have a nice day!

[afridy]

small note : with and without union it works in my case.tx.

last updated code

            SELECT candidates.cid as cid1,'candidates' as tblname
            FROM candidates
            INNER JOIN approvals
            ON candidates.cid = approvals.cid
            WHERE agency_code='$agency_code'
           
            UNION
           
            SELECT candidates_ans.cid as cid1,'candidates_ans' as tblname
            FROM candidates_ans
            INNER JOIN approvals_ph_ans
            ON candidates_ans.cid = approvals_ph_ans.cid
            WHERE agency_code='$agency_code'";

[RichardAskew]

Slightly off topic but please try and refrain from using the mysql_ libraries:

From http://www.php.net/manual/en/functio...-fetch-row.php

Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.

[r937]

small note : with and without union it works in my case.tx.

UNION (without ALL) requires a complete sort of the entire result set on all columns, as part of the task of detecting duplicate rows

UNION ALL is preferred in this case because we already know there won't be any duplicate rows

skipping the unnecessary sort is better, wouldn't you say?