Linking results fom 2 tables, records are missing

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Linking results fom 2 tables, records are missing

Flakheart
This post has NOT been accepted by the mailing list yet.
G'day folks, I am in a pickle. I have the following two tables defined that are linked with a foreign key and I wanted to return a combined result set based on the two tables when I furnish the record number for the query.

CREATE TABLE IF NOT EXISTS Recipes(
Recipetitle,
Numberofservings,
Recipeauthor,
Categories,
Subcategories,
Preparationtime,
Cookingtime,
Difficulty,
Recipeversion,
Recipesource,
Copyright,
Reciperating,
Importedfrom,
Authorcomments,
Instructions,
Nutritionaldata,
Othercomments,
Deleted,
Updated,
Favourite,
Locked,
Recordid INTEGER PRIMARY KEY AUTOINCREMENT,
UNIQUE (Recipetitle, Instructions) ON CONFLICT FAIL)

CREATE TABLE IF NOT EXISTS Pictures(
Pictureid INTEGER PRIMARY KEY AUTOINCREMENT,
Picture BLOB,
Recordid INTEGER,
FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid))

My database has 7 records for the purposes of the test and record 7 has a picture linked to it.

However, when I issue the following query:

SELECT m.*, n.Picture
FROM Recipes AS m, Pictures AS n
WHERE n.Recordid = m.Recordid
AND m.Recordid = '3'

This returns nothing unless I give it record 7 as the id. Nowhere in (my limited understanding) that query can I see why it doesn't display data for any record number I give it except for record 7.

Can anyone help please?