LEFT JOIN issue in Mysql


 
Thread Tools Search this Thread
Top Forums Programming LEFT JOIN issue in Mysql
# 1  
Old 02-01-2012
LEFT JOIN issue in Mysql

I have a data table as follows:
Code:
mysql> select * from validations where source = "a03";
+------------+-------+--------+
| date       | price | source |
+------------+-------+--------+
| 2001-01-03 |    80 | a03    |
| 2001-01-04 |    82 | a03    |
| 2001-01-05 |    84 | a03    |
| 2001-01-06 |    86 | a03    |
| 2001-01-07 |    88 | a03    |
| 2001-01-08 |    90 | a03    |
+------------+-------+--------+

And some dates as follows:
Code:
mysql> select * from dates;
+------------+
| date       |
+------------+
| 2001-01-01 |
| 2001-01-02 |
| 2001-01-03 |
| 2001-01-04 |
| 2001-01-05 |
| 2001-01-06 |
| 2001-01-07 |
+------------+

In a left join the result is the following:
Code:
mysql> SELECT dt.date, v.price, v.source FROM dates dt LEFT JOIN validations v ON dt.date = v.date WHERE source = "a03";
+------------+-------+--------+
| date       | price | source |
+------------+-------+--------+
| 2001-01-03 |    80 | a03    |
| 2001-01-04 |    82 | a03    |
| 2001-01-05 |    84 | a03    |
| 2001-01-06 |    86 | a03    |
| 2001-01-07 |    88 | a03    |
+------------+-------+--------+

Why are the dates first and second of january not returned?
# 2  
Old 02-01-2012
It would be, except your where clause removed it from the result:

i.e...
Code:
SQL> SELECT dt.c1, v.price, v.source FROM za2 dt LEFT JOIN za1 v on dt.c1 = v.c1;

        C1      PRICE SOURCE
---------- ---------- ----------
         1         10 a03
         2         10 a03
         3

SQL> SELECT dt.c1, v.price, v.source FROM za2 dt LEFT JOIN za1 v on dt.c1 = v.c1 where source='a03';

        C1      PRICE SOURCE
---------- ---------- ----------
         1         10 a03
         2         10 a03

# 3  
Old 02-01-2012
Yes, that was pretty elementary. Should have been:
Code:
SELECT dt.date, v.price, v.source FROM dates dt LEFT JOIN validations v ON dt.date = v.date WHERE source IS NULL OR source = "a03";


Last edited by figaro; 02-01-2012 at 06:54 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. Shell Programming and Scripting

left join using awk

Hi guys, I need AWK to merge the following 2 files: file1 1 a 1 1 2 b 2 2 3 c 3 3 4 d 4 4 file2 a a/a c/c a/c c/c a/a c/t c c/t c/c a/t g/g c/c c/t desired output: 1 a 1 1 a/a c/c a/c c/c a/a c/t 2 b 2 2 x x x x x x 3 c 3 3 c/t c/c a/t g/g c/c c/t 4 d 4 4 x x x x x x (2 Replies)
Discussion started by: g1org1o
2 Replies

3. Shell Programming and Scripting

left join using awk

Hi guys, I need to use awk to join 2 files file_1 A 001 B 002 C 003 file_2 A XX1 B XX2 output desired A 001 XX1 B 002 missing C 003 XX2 thank you! (2 Replies)
Discussion started by: g1org1o
2 Replies

4. Programming

MySQL join four tables!

Hello; I want merge four MySQL tables to get the intersection that have a common field for all of them. Join two tables is fine to me, but my this case is different from common situations and there are not very many discussions about it. Can anybody give me some idea? Thanks a lot! Here is part... (8 Replies)
Discussion started by: yifangt
8 Replies

5. Shell Programming and Scripting

Left Join in Unix based on Key?

So I have 2 files: File 1: 111,Mike,Stipe 222,Peter,Buck 333,Mike,Mills File 2: 222,Mr,Bono 444,Mr,Edge I want output to be below, where 222 records joined and all none joined records still in output 111,Mike,Stipe 222,Peter,Buck,Mr,Bono 333,Mike,Mills 444,Mr,Edge (4 Replies)
Discussion started by: stack
4 Replies

6. Solaris

No Space Left - Memory/Swap issue

:wall:I'm having a bit of a problem with Solaris 10u8 and one of our applications requesting memory and being told, "no space left". The break down: 24GB Physical Memory 8GB swap at the time of occurance, here's what a memory breakdown looks like: Page Summary Pages ... (21 Replies)
Discussion started by: aychbee45
21 Replies

7. Web Development

MySQL Join

Hey! I hope to find someone who can help me with a join question. I just can't figure it out. I have (among others) three tables in the database "CastControl". They are named "cc_users", "cc_plan" and "cc_servers". In cc_users, (among others) is a row named "username" and another named... (1 Reply)
Discussion started by: noratx
1 Replies

8. Programming

sql,multiple join,outer join issue

example sql: select a.a1,b.b1,c.c1,d.d1,e.e1 from a left outer join b on a.x=b.x left outer join c on b.y=c.y left outer join d on d.z=a.z inner join a.t=e.t I know how single outer or inner join works in sql. But I don't really understand when there are multiple of them. can... (0 Replies)
Discussion started by: robbiezr
0 Replies

9. Shell Programming and Scripting

Left join on files using awk

nawk 'NR==FNR{a;next} {if($1 in a) print $1,"Found" else print}' OFS="," File_B File_A The above code is not working help is appreciated (6 Replies)
Discussion started by: pinnacle
6 Replies
Login or Register to Ask a Question