selecting records from multiple files in mysql question

MRMadhav · 11-02-2006, 11:30 PM

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!

j-ray · 11-03-2006, 02:11 AM

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

MRMadhav · 11-03-2006, 09:00 AM

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.

j-ray · 11-03-2006, 10:18 AM

replace "join" with "left outer join"

Quigi · 11-03-2006, 10:22 AM

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.

mjones490 · 11-03-2006, 12:37 PM

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.

MRMadhav · 11-03-2006, 11:32 PM

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

chrism01 · 11-05-2006, 04:52 PM

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