Solved

INNER JOIN or UNION?

Posted on 2006-11-02
17
585 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
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 125 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

726 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