INNER JOIN or UNION?

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?
LVL 8
Sam CohenConsultant to Digital/DeveloperAsked:
Who is Participating?
 
lucki_lukeConnect With a Mentor Commented:
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'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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' ";
0
 
Raynard7Commented:
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'
;";
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Sam CohenConsultant to Digital/DeveloperAuthor 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.'"'));
0
 
Raynard7Commented:
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.
0
 
Sam CohenConsultant to Digital/DeveloperAuthor Commented:
So what can i do??
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Raynard indicates the probable issue. you might however post the error message if that is not the problem on your tables..
0
 
Sam CohenConsultant to Digital/DeveloperAuthor 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
Sam CohenConsultant to Digital/DeveloperAuthor 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?
0
 
Raynard7Commented:
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.
0
 
Sam CohenConsultant to Digital/DeveloperAuthor Commented:
but there is only one matching field....Name

0
 
Raynard7Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

$res = mysql_fetch_array(mysql_query('SELECT `Name` FROM Members_tb WHERE Nack ="'.$name.'" UNION ALL SELECT `Name` FROM Dm_tb WHERE `Name`="'.$name.'"'));
0
 
lucki_lukeCommented:
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
0
 
Sam CohenConsultant to Digital/DeveloperAuthor Commented:
lukas
can you be a bit clear? where in you Sql can i put the $name Var
0
 
Sam CohenConsultant to Digital/DeveloperAuthor Commented:
Simple and straight forword. Thanks
0
All Courses

From novice to tech pro — start learning today.