Login or Register to Ask a Question and Join Our Community


Programming

Query to join two tables in SQL

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Query to join two tables in SQL
# 1  
Old 08-23-2011
Query to join two tables in SQL
Hi,

I have 2 tables.

Code:
Table1

name  map_code    data_code        id
aaa	 2732C	   3333B		10
bbb    1223F	   2545v		11
ccc  				  	        12


Code:
Table2

 
name   map_code   data_code        id    text_description
aaa	  2732C	   3333B		10    description 1
bbb     1223F	   2545v		11    description 2 
ccc  				 	        12    description 3

I want to perform join on these 2 tables. The column to be used to join is map_code & data_code.

The map_code & data_code are null for the id: 13 name: ccc


The output should be like this:
Code:
name   map_code   data_code   id 	  text_description
aaa      2732C       3333B         10	     description 1
bbb	  1223F        2545v         11       description 2 
ccc  	  				   12       description 3

How to get the above output?

Regards
Vanitha
# 2  
Old 08-23-2011
try this .. Its not tested bcoz i dont have sql in my machine..
Code:
select tbl1.name,tbl1.map_code,tbl1.data_code,tbl2.id,tbl2.text_description from table1 tbl1, table2 tbl2 where tbl1.map_code=tbl2.map_code and tbl1.data_code=tbl2.data_code;

# 3  
Old 08-23-2011
Quote:
Originally Posted by jayan_jay
try this .. Its not tested bcoz i dont have sql in my machine..
Code:
select tbl1.name,tbl1.map_code,tbl1.data_code,tbl2.id,tbl2.text_description from table1 tbl1, table2 tbl2 where tbl1.map_code=tbl2.map_code and tbl1.data_code=tbl2.data_code;

This code will not work as there are null values in the column of join..However it may work for this particular problem as it is having only one Null value.But in case there are multiple null values in the table,This query will fail
# 4  
Old 08-24-2011
Quote:
Originally Posted by vanitham
I have 2 tables.

Code:
Table1

name  map_code    data_code        id
aaa     2732C       3333B        10
bbb    1223F       2545v        11
ccc                                12

Code:
Table2

 
name   map_code   data_code        id    text_description
aaa      2732C       3333B        10    description 1
bbb     1223F       2545v        11    description 2 
ccc                               12    description 3

I want to perform join on these 2 tables.
...
The output should be like this:
Code:
name   map_code   data_code   id       text_description
aaa      2732C       3333B         10         description 1
bbb      1223F        2545v         11       description 2 
ccc                           12       description 3

...
Your data looks like this:

Code:
SQL>
SQL> SELECT * FROM table1;
 
NAME       MAP_CODE   DATA_CODE          ID
---------- ---------- ---------- ----------
aaa        2732C      3333B              10
bbb        1223F      2545v              11
ccc                                      12
 
3 rows selected.
 
SQL>
SQL> SELECT * FROM table2;
 
NAME       MAP_CODE   DATA_CODE          ID TEXT_DESCRIPTION
---------- ---------- ---------- ---------- -----------------
aaa        2732C      3333B              10 description 1
bbb        1223F      2545v              11 description 2
ccc                                      12 description 3
 
3 rows selected.
 
SQL>
SQL>

You want to fetch records from both tables that have:
(1) NULL values for both map_code and data_code columns, or
(2) Non-NULL and identical values for map_code and data_code columns respectively.

In a join condition, Oracle takes care of case no. (2) already. So for case no. (1), you could use the NVL function and set the column value to something that both sides agree upon mutually.

Here's an example:

Code:
SQL>
SQL> --
SQL> -- Query 1: Use NVL function that defaults to a character that you are sure will never exist in
SQL> --          the columns used in the join condition
SQL> --
SQL> SELECT x.name,
  2         x.map_code,
  3         x.data_code,
  4         x.id,
  5         y.text_description
  6    FROM table1 x,
  7         table2 y
  8   WHERE NVL (x.map_code,  '~') = NVL (y.map_code,  '~')
  9     AND NVL (x.data_code, '~') = NVL (y.data_code, '~')
 10  /
 
NAME       MAP_CODE   DATA_CODE          ID TEXT_DESCRIPTION
---------- ---------- ---------- ---------- -----------------
aaa        2732C      3333B              10 description 1
bbb        1223F      2545v              11 description 2
ccc                                      12 description 3
 
3 rows selected.
 
SQL>

Of course, you'd want to ensure that the "mutually agreed upon" value is something that *NEITHER* of the two columns could assume.
(To understand why, imagine the output of Query 1 if table1.map_code is null and table2.map_code is "~").

A way out is to use a non-printable character, like so -

Code:
SQL>
SQL> --
SQL> -- Query 2: If no such character could be determined, then use a non-printable character, the
SQL> --          rationale being it's highly unlikely it is used as business data
SQL> --
SQL> select x.name,
  2         x.map_code,
  3         x.data_code,
  4         x.id,
  5         y.text_description
  6    from table1 x,
  7         table2 y
  8   where NVL (x.map_code,  CHR(0)) = NVL (y.map_code,  CHR(0))
  9     and NVL (x.data_code, CHR(0)) = NVL (y.data_code, CHR(0))
 10  /
 
NAME       MAP_CODE   DATA_CODE          ID TEXT_DESCRIPTION
---------- ---------- ---------- ---------- -----------------
aaa        2732C      3333B              10 description 1
bbb        1223F      2545v              11 description 2
ccc                                      12 description 3
 
3 rows selected.
 
SQL>

Otherwise, if you are a truly paranoid programmer, then you probably won't rely on default values; you would be as explicit as you could be -

Code:
SQL>
SQL> --
SQL> -- Query 3: Or use the most explicit version
SQL> --
SQL> SELECT x.name,
  2         x.map_code,
  3         x.data_code,
  4         x.id,
  5         y.text_description
  6    FROM table1 x,
  7         table2 y
  8   WHERE (
  9           (x.map_code IS NULL AND y.map_code IS NULL)
 10           OR
 11           (x.map_code = y.map_code)
 12         )
 13     AND (
 14           (x.data_code IS NULL AND y.data_code IS NULL)
 15           OR
 16           (x.data_code = y.data_code)
 17         )
 18  /
 
NAME       MAP_CODE   DATA_CODE          ID TEXT_DESCRIPTION
---------- ---------- ---------- ---------- -----------------
aaa        2732C      3333B              10 description 1
bbb        1223F      2545v              11 description 2
ccc                                      12 description 3
 
3 rows selected.
 
SQL>

tyler_durden
Last edited by durden_tyler; 08-24-2011 at 10:26 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Identify tables from Oracle sql scripts

Hi, Please let me know if you have any thoughts on how to read a table that has all the oracle sql files or shell scripts at the job and step level to identify all the tables that does merge, update, delete, insert, create, truncate, alter table (ALTER TABLE XYZ RENAME TO ABC) and call them out... (1 Reply)
Discussion started by: techmoris
1 Replies

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

3. Shell Programming and Scripting

Shell script automation using cron which query's MySQL Tables

What I have: I have a input.sh (script which basically connect to mysql-db and query's multiple tables to write back the output to output1.out file in a directory) note: I need to pass an integer (unique_id = anything b/w 1- 1000) next to the script everytime I run the script which generates... (3 Replies)
Discussion started by: kkpand
3 Replies

4. Shell Programming and Scripting

Call and execute query from tables

Hi.. We have a table DB_QUERIES, in which sql queries are stored. SQL> desc DB_QUERIES Name Null? Type ----------------------------------------- -------- ---------------------------- QUERY_ID NOT NULL NUMBER(10) ... (2 Replies)
Discussion started by: sameermohite
2 Replies

5. Programming

Dynamically checking rules in database tables using SQL

I want to check for rows in a table where all values (except the key) is empty. I am using MySQL 5.5. I plan to do this mechanically, so the approach should work for any table in my database schema. Suppose for illustration purposes I start with the following table: CREATE TABLE `sources` ( ... (4 Replies)
Discussion started by: figaro
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

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Discussion started by: balaeswari
6 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. Programming

SQL Add to Multiple Tables

I'm pretty new to the database world and I've run into a mental block of sorts. I've been unable to find the answer anywhere. Here's my problem: I have several tables and everything is as normalized as possible (as I've been lead to understand normalization.) Normalization has lead to some... (1 Reply)
Discussion started by: flakblas
1 Replies

10. Shell Programming and Scripting

Tables to query to find users for database from shell script

I am coding shell script. I need to connect to different databases like DB2, Oracle and Sybase. I would then need to query tables where it has all the groups, users for that database. I would also need who has what kind of permissions. EG: I know for DB2 some TABAUTH table needs to be... (0 Replies)
Discussion started by: pinnacle
0 Replies
Login or Register to Ask a Question