We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

INNER JOIN or UNION?

Sam Cohen
Sam Cohen asked
on
Medium Priority
603 Views
Last Modified: 2012-06-21
I need a Sql, but not sure which to use(INNER JOIN or UNION). This is what i have:
2 tables with many differnt fields but the "Name" field is the same in both tables.
THis is what i want:
$name = "sam"
I want all records from both table where Name = $name

Can some one give me the correct Sql?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>I want all records from both table where Name = $name
UNION:

$strSQL = " select * from table1 where name = '$name' UNION ALL select * from table2 where name = '$name' ";
Top Expert 2006

Commented:
Hi,

If all you want is the name field then I would use a union query

$sql = "Select t1.`name` from tableName as t1 where t1.`name` = '$name'
union
Select t2.`name` from tableName2 as t2 where t2.`name` = '$name'
;";
Sam CohenConsultant to Digital/Developer
CERTIFIED EXPERT

Author

Commented:
Angell doesnt seem to work , here is my sql:

$res = mysql_fetch_array(mysql_query('SELECT * FROM Members_tb WHERE Nack ="'.$name.'" UNION ALL SELECT * FROM Dm_tb WHERE Name="'.$name.'"'));
Top Expert 2006

Commented:
Hi,

You can not do a select all (select *)

If all the fields are not the same then a union can not work

This requires the same number and same format fields in the same order to combine them

If you want more fields to be included you would be best posting what they are and how you want them displayed for the two different tables.
Sam CohenConsultant to Digital/Developer
CERTIFIED EXPERT

Author

Commented:
So what can i do??
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Raynard indicates the probable issue. you might however post the error message if that is not the problem on your tables..
Sam CohenConsultant to Digital/Developer
CERTIFIED EXPERT

Author

Commented:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/scohen/public_html/aos/_playerstats.php on line 20
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
looks like it is the problem.
so if the fields list is not identical, and field data types are not identical, you have to replace the SELECT * by a SELECT field1, field2 ... for each table so the data types and the number of fields returned is the same.
Sam CohenConsultant to Digital/Developer
CERTIFIED EXPERT

Author

Commented:
ok..see i have 21 fields in table1 and 15 in table2 both contains the field Name.
Is there abetter way on doing this , do i have to list all fields?
Top Expert 2006

Commented:
You would have to list all the matching fields in both parts before and after the union if you want all data from both tables.
Sam CohenConsultant to Digital/Developer
CERTIFIED EXPERT

Author

Commented:
but there is only one matching field....Name

Top Expert 2006

Commented:
Well.... I think that you need to consider what you are hoping to acheive - there does not seem to be much point in combining two queries into one if there is nothing similar between the tables except for the name field.

It would be easier to use PHP to run each table separatley and then use the records for each query rather than in one go
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:

$res = mysql_fetch_array(mysql_query('SELECT `Name` FROM Members_tb WHERE Nack ="'.$name.'" UNION ALL SELECT `Name` FROM Dm_tb WHERE `Name`="'.$name.'"'));
Well,

why not use a JOIN here? Since all except Name have different names there shouldnt be a big problem:

SELECT * FROM `Members_tb` AS mem LEFT OUTER JOIN `Dm_tb` as dm ON mem.adressen_id = dm.id
UNION
SELECT * FROM `Members_tb` AS mem RIGHT OUTER JOIN `Dm_tb` as dm ON mem.adressen_id = dm.id

This works like a FULL OUTER JOIN so that all rows are included.

Lukas
Sam CohenConsultant to Digital/Developer
CERTIFIED EXPERT

Author

Commented:
lukas
can you be a bit clear? where in you Sql can i put the $name Var
Oops sorry,


SELECT * FROM `Members_tb` AS mem LEFT OUTER JOIN `Dm_tb` as dm ON mem.Name = dm.Name WHERE mem.Name = '$name'
UNION
SELECT * FROM `Members_tb` AS mem RIGHT OUTER JOIN `Dm_tb` as dm ON mem.Name = dm.Name WHERE mem.Name = '$name'

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Sam CohenConsultant to Digital/Developer
CERTIFIED EXPERT

Author

Commented:
Simple and straight forword. Thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.