SQL Mach two table

Question

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?

Answer 1

Try LEFT JOIN and ISNULL function like this below

SELECT ISNULL(Table1.Name,'NA')
FROM Table1
LEFT JOIN Table2
ON   Table1.NO = Table2.ID

Answer 2

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