SQL query: select everything from 2 tables and if filename not exists display No files

I have 2 tables: Issues and Attachement

Issue table contains 10 records and has the following design

IssueId | IssueName | IssueD开发者_如何学运维ate | IssueDescription

Attachement contains 5 records table has the following desin

IDAttachement | Filename | IssueID

I want to select everything from the 2 tables total 10 records as the following if filename not exists display No files

IssueID       IssueName        Filename

1             Issue1           file1

2             Issue2           Nofile

3             Issue3           Nofile

4             Issue4           Nofile

5             Issue5           Nofile

6             Issue  6         file6

7             Issue 7          file 7

8               Issue 8          file 8

9               Issue 9          file 9

10            Issue 10        Nofile

please help me to display Nofile if there was no attachement for the issue

I need the SQL select Query

SELECT i.IssueID,i.IssueName, COALESCE (a.Filename,'Nofile') Filename
FROM Issue i
LEFT JOIN Attachement a ON i.IssueID = a.IssueID

SELECT IssueID, IssueName, Filename
       FROM Issues JOIN Attachement ON (Issues.IssueId = Attachement.IssueID);

If you were to rename your ID fields so they are both called IssueId, you can do even better:

SELECT IssueId, IssueName, Filename
       FROM Issues JOIN Attachement USING(IssueId);

If you want to include issues for which no attachment record exists at all (rather than one with empty "Filename" field), say LEFT OUTER JOIN instead of JOIN.

LEFT OUTER JOIN is what you are looking for. Code is approximate although case sensitivity mayr may not be a factor in your RDBMS

SELECT
    I.IssueID
,   I.IssueName
,   coalesce(A.FileName, 'NoFile') AS FileName
FROM
    ISSUES I
    LEFT OUTER JOIN
        Attachement A
        ON A.IssueID = I.IssueId

select i.IssueID,i.IssueName,
(case when a.FileName then a.FileName else "NO FILE" end)
FROM Issue i LEFT JOIN Attachement a ON i.IssueID = a.IssueID