CODEWITHSUNDEEP
phpandroidmysqlsqlite
Sindy McMore
Sindy McMore

Reputation: 127

Getting Data from multiple tables SQLite

I have an android application that has 2 tables, one table stores posts and the other table stores images of the posts incase it has an image, changes are not all posts will have images, if a post has an image its primary key will be stored in the foreign Key table, when loading the posts I have to get all posts wether with image or not then check in the image table to see which posts have images and add the images to the posts below is a graphical overview of my tables

Post Table

`|post_id |post | post_description|
 |--------|-----|-----------------|
 |        |     |                 |`

Image Table

  `|img_id  |fk_postID | imagePath       |
   |--------|----------|-----------------|
   |        |          |                 |`

I could have used a join like Query = "SELECT post_id, post, post_description, imagePath FROM PostTable,
ImageTable, Where PostTable.post_id = ImageTable.fk_postID;

but this query is only returning posts with images and forgeting about posts without images, How can I get all posts be it with image or not? Thanks in advance.

Upvotes: 0

Views: 59

Answers (2)

Gandalf the White
Gandalf the White

Reputation: 2465

Try using Left join and that will result all entries from left table and matched entries from right table. 

SELECT posttable.postid,posttable.post,posttable.postdescription, imagetable.imageid,imagetable.fkpostid,imagetable.imagepath
FROM posttable
LEFT JOIN imagetable
ON posttable.postid=imagetable.fkpostid
ORDER BY posttable.postid;

Code should look like that.

http://www.w3schools.com/sql/sql_join_left.asp

Upvotes: 0

Drew
Drew

Reputation: 24960

ok, you asked, so give this a whirl, see if you like the output

SELECT pt.post_id, pt.post, pt.post_description, im.imagePath 
FROM PostTable pt
left join ImageTable im
on im.fk_postID=pt.post_id

It will bring along for the ride the right table (ImageTable) of those posts that don't have images.

Uses table aliases (pt and im). That helps to be explicit which table the columns come from on the first line in case there are common column names in both, plus a little less typing.

Untested

reference Mysql Left Joins

Upvotes: 1

Related Questions