当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Table join in mysql returning null

问题描述:

I have two tables in mysql, when I tried to join these two tables based on common columns, it returned null, even when values in the columns in both these tables were same.

Table_1 structure :

id VARCHAR (!2) - index

string1 VARCHAR (15)

string2 VARCHAR (50)

string3 VARCHAR (20)

city VARCHAR (50)

zip VARCHAR (20)

Table_2 structure :

string1 VARCHAR (15) -- index

string2 VARCHAR (50) -- index

string3 VARCHAR (20)

city VARCHAR (50)

zip VARCHAR (20)

id_1 int (6)

I used the query:

SELECT p.id, pr.id_1

FROM table_1 as p

LEFT JOIN table_2 as pr

ON p.string1 = pr.string1

AND p.string2 = pr.string2

AND p.city = pr.city

AND p.zip = pr.zip

This query is returning NULLs for id_1. Most of the rows are having the same data for the columns compared in both these tables, I am not understanding why this is happening. Can someone please tell me why this is happening and why this is happening.

The data model:

table_1

id_1 string1 string2 string3 city zip

1 abc cde efg ghi 00001-13

2 cde efg abc abc 00002

id_2 string1 string2 string3 city zip

3 abc cde efg ghi 00001-13

4 cde efg abc abc 00002

The result expected:

id_1 id_2

1 3

2 4

The result obtained:

id_1 id_2

1 null

2 null

网友答案:
SELECT p.id, pr.id_1
  FROM table_1 as p 
   LEFT JOIN table_2 as pr
    ON p.string1 = pr.string1
     AND p.string1 = pr.string2 
     AND p.city = pr.city 
     AND p.zip = pr.zip 

I no see any problem in this query

demo

网友答案:
SELECT p.id, pr.id_1
  FROM table_1 as p 
   LEFT JOIN table_2 as pr
    ON p.string1 = pr.string1
     AND p.string2 = pr.string2 
     AND p.city = pr.city 
     AND p.zip = pr.zip;

this query is hundred percent correct. there was no reason for wrong output.

分享给朋友:
您可能感兴趣的文章:
随机阅读: