SQL MACH TWO TABLE

Babe Zaharias

Total Post:19

Points:133
Posted by  Babe Zaharias
MYSQL 
 1323  View(s)
Ratings:
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. 
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL Mach two table

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!