CODEWITHSUNDEEP
databasesqlite
KevinM
KevinM

Reputation: 1807

sqlite join statement

I'm just learning join statements in sqlite and I'm having trouble with the the below example.
Here is the statement that has gotten me closest to the results I want:

select listings.id, listings.name, media.image from listing join media where media.listing_id = listings.id

listings:

id | name | phone

1 , john , 555-5555

2 , jane , 555-0000

media:

listing_id | image_url

2 , www.xyz.xyz

in return I want

id | name | image_url

1 , john
2 , jane , www.xyz.xyz

Upvotes: 1

Views: 999

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

That should be 

SELECT listings.id, listings.name, media.image_url 
    FROM listing LEFT JOIN media ON media.listing_id = listings.id

A "left join" gives you results from the left table (listing) even if there is no match in the right table (media). A regular join requires both to exist to give a result for that row.

Upvotes: 2

Related Questions