Home > DeveloperSection > Forums > SQL Mach two table
Babe Zaharias
Babe Zaharias

Total Post:19

Points:133
Posted on    July-09-2013 3:35 AM

 MYSQL MYSQL 
Ratings:


 1 Reply(s)
 975  View(s)
Rate this:
Hi Expert!

I've 2 table.

Table1

Has count about 2700 rows
Colums: ID, NO, NAME
Table2:

Has count about 300 rows
Colums: ID, NAME
where:

Table1.NO = Table2.ID
I want to list Table1(2700 rows) but if Table1 doesn't contain some of Table2's rows I want to write "NA"

How can i do that with SQL.

Thanks in advance. 


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    July-09-2013 4:43 AM

Hi,

I assume you want to output the Name from table2, if it's present, in which case:

SELECT 
    a.id,
    isnull(b.name, 'NA'),
    a.name
FROM
    table1 a
LEFT JOIN
    table2 b
    ON
    a.no = b.id
will do it for you (I've also output the id and name from table1).

EDIT:

Apologies, I didn't see the MySQL tag until I posted. You will need to use the coalesce function instead if isnull, like so:

SELECT 
    a.id,
    coalesce(b.name, 'NA'),
    a.name
FROM
    table1 a
LEFT JOIN
    table2 b
    ON
    a.no = b.id

Don't want to miss updates? Please click the below button!

Follow MindStick