mySQL multiple columns

Question

I am looking to write a query that outputs the columns of two tables. I was looking to do a join maybe but if I join on either the left or right tables, a value might not be returned. I thought of using the UNION keyword but it renames the columns to one name. Then I do not know which column has what.

I want my query to select the columns in both tables, but not care if a match for username exists in either table, or neither. I just want all the columns that don't have values to be there anyway.

SELECT *
FROM client_table
JOIN staff_table 
ON client_table.username = staff_table.username
WHERE client_table.username = " . mysql_real_escape_string($gotMyUser) . " 
OR staff_table " . mysql_real_escape_string($gotMyUser) . " LIMIT 1

UPDATE:

here is the output of the query

Array
(
//FIRST TABLE
[client_id] => 332058
[username] => jake
[firstname] => jake
[lastname] => ****
[email] => *****
[phone] => 
[phone_ext] => 
[mobile] => 
[department] => 
[is_active] => 1
[default_site] => 0
[google] => 

//SECOND TABLE
[staff_id] => 
[group_id] => 
[dept_id] => 
[passwd] => 
[signature] => 
[isactive] => 
[isadmin] => 
[isvisible] => 
[onvacation] => 
[daylight_saving] => 
[append_signature] => 
[change_passwd] => 
[timezone_offset] => 
[max_page_size] => 
[auto_refresh_rate] => 
[created] => 
[lastlogin] => 
[updated] => 

)

This is exactly what i want because i want to know if the user either has a staff id or a client id but depending on which join (LEFT RIGHT or FULL) i do it may not return any results. I wish for the result to contain all of these columns everytime i execute the query and then i test:

if($array['client_id']){
//do stuff with client
}

else if($array['staff_id']){
//do stuff with staff
}

Answer 1

The FULL OUTER JOIN concept is exactly what you need as Julius Davies suggested. However, since they don't have those in MySQL you will need to do a LEFT JOIN and UNION that with a RIGHT JOIN.

Example (notice it is the same query duplicated in 2 blocks, one uses right and the other left outer joins):

SELECT *
FROM client_table
LEFT OUTER JOIN staff_table 
ON client_table.username = staff_table.username
WHERE client_table.username = " . mysql_real_escape_string($gotMyUser) . " 
OR staff_table " . mysql_real_escape_string($gotMyUser) . " 

UNION


SELECT *
FROM client_table
RIGHT OUTER JOIN staff_table 
ON client_table.username = staff_table.username
WHERE client_table.username = " . mysql_real_escape_string($gotMyUser) . " 
OR staff_table " . mysql_real_escape_string($gotMyUser) . " 

See this question

Answer 2

Try changing "JOIN" to "FULL OUTER JOIN"

http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join

Answer 3

I think you want a union all, but you have to get the column names right:

SELECT 'client_table' as table_name, 
       client_id, username, firstname, lastname,  email, phone, phone_ext,mobile,department,is_active,default_site, google,
       NULL as staff_id, NULL as group_id, NULL as dept_id, NULL as passwd, NULL as signature, NULL as isactive, NULL as isadmin, NULL as isvisible, NULL as onvacation, NULL as daylight_saving,
       NULL as append_signature, NULL as change_passwd, NULL as timezone_offset, NULL as max_page_size, NULL as auto_refresh_rate, NULL as created, NULL as lastlogin, NULL as updated
FROM client_table
WHERE client_table.username = " . mysql_real_escape_string($gotMyUser) . " 
UNION all
select 'staff_table' as table_name,
       NULL as client_id, NULL as username, NULL as firstname, NULL as lastname,  NULL as email,
       NULL as phone, NULL as phone_ext, NULL as mobile, NULL as department, NULL as is_active, NULL as default_site, NULL as google,
       staff_id, group_id, dept_id, passwd, signature, isactive, isadmin, isvisible, onvacation, daylight_saving,
       append_signature, change_passwd, timezone_offset, max_page_size, auto_refresh_rate, created, lastlogin, updated
from staff_table 
where staff_table.username = " . mysql_real_escape_string($gotMyUser) . "

Answer 4

Try this, mysql doesn't have option to do full outer join, so you can do union of both left and right join

SELECT *
FROM client_table
LEFT OUTER JOIN staff_table 
ON client_table.username = staff_table.username
WHERE client_table.username = " . mysql_real_escape_string($gotMyUser) . " 
OR staff_table " . mysql_real_escape_string($gotMyUser) . "
UNION
SELECT *
FROM client_table
RIGHT OUTER JOIN staff_table 
ON client_table.username = staff_table.username
WHERE client_table.username = " . mysql_real_escape_string($gotMyUser) . " 
OR staff_table " . mysql_real_escape_string($gotMyUser) . "

Answer 5

I THINK what you are looking to do is know which table the record is coming from. It would help if you would post some example data and the results that you expect.

If I'm correct, and assuming that both client_table and staff_table have the same structure, you could do something like:

SELECT 'client_table' as table_name, client_table.*
FROM client_table
WHERE client_table.username = " . mysql_real_escape_string($gotMyUser) . " 
UNION
select 'staff_table' as table_name, staff_table.*
from staff_table 
where staff_table.username = " . mysql_real_escape_string($gotMyUser) . "

By adding the table_name, you know where the data is coming from.