Left Join on Empty Table Returns Error in MySQL

Question

So I have a few tables in my database. Let's say I have

CREATE TABLE ARTICLE (
  id INT NOT NULL IDENTITY PRIMARY KEY,
  time_stamp BIGINT(20) NOT NULL UNIQUE,
  body TEXT NOT NULL,
  title VARCHAR(255),
  web_address VARCHAR(255) NOT NULL UNIQUE
);

and

CREATE TABLE BLOG (
  id INT NOT NULL IDENTITY PRIMARY KEY,
  time_stamp BIGINT(20) NOT NULL UNIQUE,
  body TEXT NOT NULL,
  title VARCHAR(255) NOT NULL UNIQUE
);

and

CREATE TABLE LINKTABLE (
  id INT NOT NULL IDENTITY PRIMARY KEY,
  parent_id INT,
  quote_id INT,
  article_id INT,
  blog_id INT,
  FOREIGN KEY (parent_id) REFERENCES Blog(id)     
    ON DELETE CASCADE, 
  FOREIGN KEY (blog_id) REFERENCES Blog(id)       
    ON DELETE CASCADE, 
  FOREIGN KEY (article_id) REFERENCES Article(id)
    ON DELETE CASCADE, 
  FOREIGN KEY (quote_id) REFERENCES Quote(id)
    ON DELETE CASCADE
);

Now, if I have the following command:

SELECT b.id as blog_id, l.blog_id as linktable_blog_id, l.id as linktable_id
  FROM linktable l
    LEFT JOIN blog b ON 
      (l.blog_id=b.id OR l.parent_id=b.id)

where there exists an entry in linktable and blog that satisfies the constraints I get returned the following as I would expect:

{"blog_id":1,"linktable_blog_id":1,"linktable_id":1}

HOWEVER supposing I have

  SELECT a.id as article_id, b.id as blog_id, l.blog_id as linktable_blog_id, l.id as linktable_id
  FROM linktable l
    LEFT JOIN blog b ON 
      (l.blog_id=b.id OR l.parent_id=b.id)
    LEFT JOIN article a ON 
      l.article_id=a.id

Where there is neither an article nor an entry in the linktable I get returned

{}//empty

Where what I am trying to do is get

{"blog_id":1,"linktable_blog_id":1,"linktable_id":1, "article_id": null}

Any suggestions?

I think it can be reached by using UNION for 2 separate joins.. — Murat Tutumlu
– Murat Tutumlu, Commented Nov 7, 2019 at 13:51
maybe...but I want to avoid UNIONs as it makes the scala code nastier. — Peter Weyand
– Peter Weyand, Commented Nov 7, 2019 at 13:57
What about writing the second join as inner select query to use in "or where in ()"? — Murat Tutumlu
– Murat Tutumlu, Commented Nov 7, 2019 at 14:19
I'm not sure how that would work - where in () requires predefined categories, and I don't know a priori what fields will be in the tables im querying. — Peter Weyand
– Peter Weyand, Commented Nov 7, 2019 at 14:22

Gordon Linoff · Accepted Answer · 2019-11-07 13:48:49Z

1

If you want to keep all blogs then blog needs to be the first table in the LEFT JOIN:

SELECT a.id as article_id, b.id as blog_id,
       l.blog_id as linktable_blog_id, l.id as linktable_id
FROM blog b LEFT JOIN
     linktable l
     ON l.blog_id = b.id OR l.parent_id = b.id LEFT JOIN
     article a 
     ON l.article_id = a.id;

answered Nov 7, 2019 at 13:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Peter Weyand Over a year ago

I think I want something like this (where I keep all the articles AND blogs, but not the linktable necessarily). ``` SELECT a.id as article_id, b.id as blog_id, l.blog_id as linktable_blog_id, l.id as linktable_id FROM blog b, article a LEFT JOIN linktable l ON (l.blog_id=b.id OR l.parent_id=b.id) OR l.article_id=a.id```

Strawberry Over a year ago

@PeterWeyand Don't mix join styles, and, better still, only use explicit join syntax (so not implicit, comma-join syntax).

Peter Weyand Over a year ago

This example doesn't work btw:

anorm.AnormException: 'article.id' not found, available columns: ARTICLE.ID, ARTICLE_ID, BLOG.ID, BLOG_ID, LINKTABLE.BLOG_ID, LINKTABLE_BLOG_ID, LINKTABLE.ID, LINKTABLE_ID

Collectives™ on Stack Overflow

Left Join on Empty Table Returns Error in MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related