SQL Query for joining non existent rows with a NULL field

Question

I have 2 tables

A. LinkNames

LINKID NAME
1  Name1
2  Name2
3  Name3

B. Links

WebsiteID  LINKID  LINKPATH
1             1     "WWW.test.com"

I need a query that displays output like this

LINKID   LINKNAME   LINKPATH
1         NAME1     WWW.test.com
2         NAME2     ""
3         NAME3     ""

I have tried all the possible joins but not coming through

Saharsh Shah · Answer 1 · 2015-12-21T07:18:25.563

2

Use LEFT JOIN to join bothe tables.

Try this:

SELECT A.LINKID, 
       A.NAME AS LINKNAME, 
       IFNULL(B.LINKPATH, '""') AS LINKPATH
FROM LinkNames A
LEFT JOIN Links B ON A.LINKID = B.LINKID;

If you want to put WebSiteID = 1 condition then use below query:

SELECT A.LINKID, 
       A.NAME AS LINKNAME, 
       IFNULL(B.LINKPATH, '""') AS LINKPATH
FROM LinkNames A
LEFT JOIN Links B ON A.LINKID = B.LINKID AND B.WebsiteID = 1;

Above query will allow NULL values also.

edited Dec 21 '15 at 07:18

answered Dec 21 '15 at 06:44

Saharsh Shah

28,687
8
48
83

This query works well but as soon as I put a where clause SELECT A.LINKID, A.NAME AS LINKNAME, IFNULL(B.LINKPATH, '""') AS LINKPATH FROM LinkNames A LEFT JOIN Links B ON A.LINKID = B.LINKID WHERE WebsiteID = 1 this does not show the null rows.... Apologies for missing that out in the initial question – appdevfan Dec 21 '15 at 07:14
Thanks a ton:) This works .. I have been struggling since 2 days on this. – appdevfan Dec 21 '15 at 07:27

score 0 · Answer 2 · answered Dec 21 '15 at 06:43

0

You have to use INNER JOIN like below

SELECT LinkNames.LINKID,
       LinkNames.NAME,
       Links.LINKPATH
FROM LinkNames
JOIN Links ON LinkNames.LINKID = Links.LINKID ;

answered Dec 21 '15 at 06:43

Sadikhasan

18,365
21
80
122

SQL Query for joining non existent rows with a NULL field

2 Answers2