Advertise with us

SQL Mach two table

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. 
Last updated:7/9/2013 4:43:54 AM

1 Answers

AVADHESH PATEL
AVADHESH PATEL

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