CODEWITHSUNDEEP
mysql
rosen_
rosen_

Reputation: 248

show all the data from 2 tables even when they has no same data

I have 2 tables, I wanna join to get all of data, if doc_name in tb1 same with doc_re the result will show doc_ty also.

tb1                        tb2
=======================    ========================  
|id_1|doc_name|doc_url|    | id | doc_re | doc_ty |
=======================    ========================
|  1 |  doc2  | url2  |    |  1 | doc1   | ty1    |
|  2 |  doc5  | url5  |    |  2 | doc2   | ty2    |
|  3 |  doc6  | url6  |    |  3 | doc5   | ty5    |
|  4 |  doc7  | url7  |    ========================
=======================

so, from the table, the example result is : doc2 url2 ty2 doc5 url4 ty5 doc6 url6 blank doc7 url7 blank

here's the code :

$query = mysql_query(" SELECT 
                             w.doc_url
                             s.doc_ty,
                       FROM
                             tb2 s
                       INNER JOIN 
                             tb1 w  ON s.doc_re = w.doc_name ")

but, from that query I just get the result that two tables has same data ( from example doc2 and doc5 only). If I want to get doc6 and doc 7 also, should I make a query again that ON s.doc_re != w.doc_name ? thank you :)

Upvotes: 0

Views: 142

Answers (3)

Campey
Campey

Reputation: 390

The inner join only returns results with both sides. To include unmatched results, you should use an outer join.

In your example if you change from INNER JOIN to LEFT JOIN you will see the behaviour you expect.

Take a look at this to help understand the joins: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Upvotes: 0

itsmatt
itsmatt

Reputation: 31416

The LEFT JOIN is what you want to use here. It includes all the rows from the left side regardless of whether they're present on the right side, if we think of this as one table on the left and one on the right. So...

SELECT tbl1.doc_name, tbl1.doc_url, tbl2.doc_ty 
FROM tbl1 LEFT JOIN tbl2
ON tbl1.doc_name = tbl2.doc_re;

Reference: http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 1

pfdsppp
pfdsppp

Reputation: 13

You should use left (outer) join to do that. See the link http://www.w3schools.com/sql/sql_join_left.asp

Upvotes: 1

Related Questions