Solved

INNER JOIN or UNION?

Posted on 2006-11-02
17
582 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 142

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

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 142

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
Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 142

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now