Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

INNER JOIN or UNION?

Posted on 2006-11-02
17
Medium Priority
?
590 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?
0
Comment
Question by:Sam Cohen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17856718
>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
 
LVL 35

Expert Comment

by:Raynard7
ID: 17856721
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
 
LVL 8

Author Comment

by:Sam Cohen
ID: 17856754
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 35

Expert Comment

by:Raynard7
ID: 17856765
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
 
LVL 8

Author Comment

by:Sam Cohen
ID: 17856774
So what can i do??
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17856780
Raynard indicates the probable issue. you might however post the error message if that is not the problem on your tables..
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 17856784
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17856820
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
 
LVL 8

Author Comment

by:Sam Cohen
ID: 17856845
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
 
LVL 35

Expert Comment

by:Raynard7
ID: 17856857
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
 
LVL 8

Author Comment

by:Sam Cohen
ID: 17856899
but there is only one matching field....Name

0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17856915
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17856932

$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
 
LVL 9

Expert Comment

by:lucki_luke
ID: 17857146
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
 
LVL 8

Author Comment

by:Sam Cohen
ID: 17857407
lukas
can you be a bit clear? where in you Sql can i put the $name Var
0
 
LVL 9

Accepted Solution

by:
lucki_luke earned 500 total points
ID: 17857441
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
 
LVL 8

Author Comment

by:Sam Cohen
ID: 17861059
Simple and straight forword. Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question