LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 11-02-2006, 11:30 PM   #1
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Rep: Reputation: 30
Question selecting records from multiple files in mysql question


Hi, I have a question for those who know about selecting multiple fields from multiple tables in MySQL.
I have 2 test tables, Table1, and Table2

Table1:
Code:
pkey         flds
1            flds1
2            flds2
3            flds3
4            flds4
5            flds5
6            flds6
7            flds7
8            flds8
9            flds9
Table2:
Code:
PKEY         FLDS
2            fs2
4            fs4
5            fs5
6            fs6
8            fs8
when i do the followinf statement:
'select Table2.FLDS,Table1.flds from Table1,Table2 where Table2.PKEY = Table1.pkey'

i get this as result:
Code:
FLDS         flds
fs2            flds2
fs4            flds4
fs5            flds5
fs6            flds6
fs8            flds8
But what i want is that the Table1 be considered as main table and that all the fields from there is loaded and for the values that are not found in Table2 the value 'Link value Not Found' is displayed
Like this:
Code:
FLDS                  flds
Link Value not Found  flds1
fs2                   flds2
Link Value not Found  flds3
fs4                   flds4
fs5                   flds5
fs6                   flds6
Link Value not Found  flds7
fs8                   flds8
Link Value not Found  flds9
Its rather urgent so please help!
 
Old 11-03-2006, 02:11 AM   #2
j-ray
Senior Member
 
Registered: Jan 2002
Location: germany
Distribution: ubuntu
Posts: 1,415

Rep: Reputation: 99
'select distinct Table2.FLDS,Table1.flds from Table1 join Table2 on Table2.PKEY = Table1.pkey'
hope this is what you want
j.

there are several ways to use "join" in mysql to be found in the mysql documentation.

Last edited by j-ray; 11-03-2006 at 02:14 AM.
 
Old 11-03-2006, 09:00 AM   #3
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Original Poster
Rep: Reputation: 30
Thanks for your answer, but thats not it... this returns the same values as before. all the values that are not found in Table2 are not displayed. I want to display all from Table1 even if the corresponding link can't be found in Table2. This is important when displaying financial figures from Table1 and requesting only a description from Table2.
 
Old 11-03-2006, 10:18 AM   #4
j-ray
Senior Member
 
Registered: Jan 2002
Location: germany
Distribution: ubuntu
Posts: 1,415

Rep: Reputation: 99
replace "join" with "left outer join"

Last edited by j-ray; 11-03-2006 at 10:21 AM.
 
Old 11-03-2006, 10:22 AM   #5
Quigi
Member
 
Registered: Mar 2003
Location: Cambridge, MA, USA
Distribution: Ubuntu (Dapper and Heron)
Posts: 377

Rep: Reputation: 31
Lightbulb

Quote:
Originally Posted by MRMadhav
what i want is that the Table1 be considered as main table and that all the fields from there is loaded and for the values that are not found in Table2 the value 'Link value Not Found' is displayed
That's commonly called an outer join, and normally would be formulated like this,
Code:
select Table2.FLDS, Table1.flds from Table1, Table2 where Table2.PKEY (+) = Table1.pkey
Note that (+) that I added. Not sure if MySql understands that syntax, as I don't have it installed here. Could you try it? It will give you NULL values (i.e., the empty string) where table2 doesn't have a corresponding row. To get the specific string you want, I'd use NVL, which I guess is Oracle specific; substitute the appropriate MySql function. Also, Oracle would put all the rows that exist in table2 first; to order them as you requested, add an ORDER BY clause:
Code:
select nvl(table2.flds, 'Link Value not Found') flds, table1.flds
from table1, table2
where table2.pkey (+) = table1.pkey
order by table1.pkey;
In Oracle's sqlplus, the above query (with your tables) gives:
Code:
FLDS                 FLDS
-------------------- -----
Link Value not Found flds1
fs2		     flds2
Link Value not Found flds3
fs4		     flds4
fs5		     flds5
fs6		     flds6
Link Value not Found flds7
fs8		     flds8
Link Value not Found flds9

9 rows selected.
Quite as you wanted.
 
Old 11-03-2006, 12:37 PM   #6
mjones490
Member
 
Registered: Sep 2005
Distribution: LFS
Posts: 60

Rep: Reputation: 22
Quote:
Originally Posted by Quigi
Code:
select Table2.FLDS, Table1.flds from Table1, Table2 where Table2.PKEY (+) = Table1.pkey
I've not seen that sytax before now. Interesting. . .

However, I am pretty sure MySQL will do the Left Outer Join as follows:

Code:
select Table2.FLDS, Table1.flds from Table1 left outer join Table2 on Table1.PKEY = Table2.PKEY
Quote:
Originally Posted by Quigi
Note that (+) that I added. Not sure if MySql understands that syntax, as I don't have it installed here. Could you try it? It will give you NULL values (i.e., the empty string) where table2 doesn't have a corresponding row. To get the specific string you want, I'd use NVL, which I guess is Oracle specific; substitute the appropriate MySql function. Also, Oracle would put all the rows that exist in table2 first; to order them as you requested, add an ORDER BY clause:
Code:
select nvl(table2.flds, 'Link Value not Found') flds, table1.flds
from table1, table2
where table2.pkey (+) = table1.pkey
order by table1.pkey;
I'm not sure of the syntax to replace NULLs in MySQL, but it's probably something very similar to Oracle's NVL () function. Should be easy to look up, though.
 
Old 11-03-2006, 11:32 PM   #7
MRMadhav
Member
 
Registered: Nov 2005
Location: Mauritius
Distribution: PCQLinux, SUSE Linux,Fedora Core 5, Fedora Core 6, Knoppix Live, Kubuntu Edgy, PCLinuxOS
Posts: 167

Original Poster
Rep: Reputation: 30
Hi Thanks Everybody! I Finally was able to get the result that i wanted by doing this command:
'select IFNULL(Table2.FLDS, 'Link Value not Found') FLDS, Table1.flds from Table1 left outer join Table2 on Table1.pkey = Table2.PKEY'

NVL() is replaced by IFNULL()

the ' (+) = ' does not work in MySQL, it returns a syntax error
 
Old 11-05-2006, 04:52 PM   #8
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,226

Rep: Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023Reputation: 2023
Yeah, SQL is like most other languages; there's lots of variations over and above the ANSI std (if any) ....
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting from multiple sound cards pajamabama Linux - Hardware 2 01-29-2006 07:56 PM
DNS question, multiple records newlinuxnewbie Linux - General 2 10-28-2005 08:26 PM
Storing and selecting multiple configurations qscomputing Linux - General 2 09-17-2005 03:00 AM
OOo / MySQL adding records problem pete_bogg Linux - Software 1 04-13-2004 03:31 AM
Multiple Users accessing the same Email records Gomi Linux - Newbie 1 02-03-2004 12:13 AM


All times are GMT -5. The time now is 01:27 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration