Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: LEFT JOIN issue in Mysql
Top Forums Programming LEFT JOIN issue in Mysql Post 302594690 by figaro on Wednesday 1st of February 2012 03:21:45 AM
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?
 

9 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

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

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

8. 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

9. 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

LEARN ABOUT DEBIAN

locale::script

Locale::Script(3perl)					 Perl Programmers Reference Guide				     Locale::Script(3perl)

NAME

       Locale::Script - standard codes for script identification

SYNOPSIS

	  use Locale::Script;

	  $script  = code2script('phnx');		      # 'Phoenician'
	  $code    = script2code('Phoenician'); 	      # 'Phnx'
	  $code    = script2code('Phoenician',
				 LOCALE_CODE_NUMERIC);	      # 115

	  @codes   = all_script_codes();
	  @scripts = all_script_names();

DESCRIPTION

       The "Locale::Script" module provides access to standards codes used for identifying scripts, such as those defined in ISO 15924.

       Most of the routines take an optional additional argument which specifies the code set to use. If not specified, the default ISO 15924
       four-letter codes will be used.

SUPPORTED CODE SETS

       There are several different code sets you can use for identifying scripts. The ones currently supported are:

       alpha
	   This is a set of four-letter (capitalized) codes from ISO 15924 such as 'Phnx' for Phoenician.

	   This code set is identified with the symbol "LOCALE_SCRIPT_ALPHA".

	   The Zxxx, Zyyy, and Zzzz codes are not used.

	   This is the default code set.

       numeric
	   This is a set of three-digit numeric codes from ISO 15924 such as 115 for Phoenician.

	   This code set is identified with the symbol "LOCALE_SCRIPT_NUMERIC".

ROUTINES

       code2script ( CODE [,CODESET] )
       script2code ( NAME [,CODESET] )
       script_code2code ( CODE ,CODESET ,CODESET2 )
       all_script_codes ( [CODESET] )
       all_script_names ( [CODESET] )
       Locale::Script::rename_script  ( CODE ,NEW_NAME [,CODESET] )
       Locale::Script::add_script  ( CODE ,NAME [,CODESET] )
       Locale::Script::delete_script  ( CODE [,CODESET] )
       Locale::Script::add_script_alias  ( NAME ,NEW_NAME )
       Locale::Script::delete_script_alias  ( NAME )
       Locale::Script::rename_script_code  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Script::add_script_code_alias  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Script::delete_script_code_alias  ( CODE [,CODESET] )
	   These routines are all documented in the Locale::Codes man page.

SEE ALSO

       Locale::Codes
       Locale::Constants
       http://www.unicode.org/iso15924/
	   Home page for ISO 15924.

AUTHOR

       See Locale::Codes for full author history.

       Currently maintained by Sullivan Beck (sbeck@cpan.org).

COPYRIGHT

	  Copyright (c) 1997-2001 Canon Research Centre Europe (CRE).
	  Copyright (c) 2001-2010 Neil Bowers
	  Copyright (c) 2010-2011 Sullivan Beck

       This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.14.2							    2011-09-26						     Locale::Script(3perl)
All times are GMT -4. The time now is 01:31 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy