Laravel join and select from multiple tables

Question

So I have Laravel 5.2 and in my SQL database I have several tables: books with fields title, author and year, journals with fields title, year and price newspapers with fields title, town, year

I need to get a list of all titles from all three tables, where the year is 1994. I've tried to do the following

$titles = DB::table('books')->where('books.year', 1994)->leftjoin('journals as journals', 'books.year', '=', 'journals.year')->leftjoin('newspapers as newspapers', 'books.year', '=', 'newspapers.year')->select('books.title', 'journals.title', 'newspapers.title')->get();

But with this query I get entries full of nulls, and only newspapers are filled in. What am I doing wrong?

Autista_z · Accepted Answer · 2017-05-31 11:58:22Z

3

In this case (if the tables are not related) you should use union, no join.

$books = DB::table('books')->select('title')->where('year', 1994);
$journals = DB::table('journals')->select('title')->where('year', 1994);
$titles = DB::table('newspapers')->select('title')->where('year', 1994)->union($books)->union($journals)->get();

edited May 31, 2017 at 11:58

answered May 31, 2017 at 11:46

Autista_z

2,53118 silver badges25 bronze badges

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

Comments

Rahul · Accepted Answer · 2017-05-31 11:38:53Z

1

You need to specify year column :

$titles = DB::table('books')
           ->where('books.year', 1994)
           ->leftjoin('journals as journals', 'books.year', '=', 'journals.year')
           ->leftjoin('newspapers as newspapers', 'books.year', '=', 'newspapers.year')
            ->select('books.title', 'journals.title', 'newspapers.title')
            ->get();

answered May 31, 2017 at 11:38

Rahul

2,4802 gold badges12 silver badges17 bronze badges

1 Comment

Coffee Over a year ago

oh, I have it specified, I just lost it writing a post. sorry

Collectives™ on Stack Overflow

Laravel join and select from multiple tables

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related