LinuxQuestions.org - selecting records from multiple files in mysql question

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - selecting records from multiple files in mysql question (https://www.linuxquestions.org/questions/programming-9/selecting-records-from-multiple-files-in-mysql-question-498145/)

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!

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

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.

replace "join" with "left outer join"

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.

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

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.

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

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