SQL query proble Missing records

[Fangbeast]

With the following compound query, I am linking two tables to create a result set using FOREIGN KEY references.

I want to return ALL fields in each record where the Recordid field in the Recipes table as long as the Recordid field in the Recipes table matches the Recordid field in the Pictures table.

Recipes table has 7 entries, only recipe 7 has a picture.

If I select record 7 using the below query, the data is returned.

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

If I select record 1 which doesn't have a picture stored, no record is returned.

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

How do I modify this statement to return a record whether it has a picture or not??

[infratec]

Hi,

without a working ...

Maybe this works, but not tested:

Code: Select all

Select m.*, coallesce(n.Picture, "")
FROM Recipes As m, Pictures As n
WHERE n.Recordid = m.Recordid
And m.Recordid = '1'

Bernd

[Fangbeast]

Hi,

without a working ...

Maybe this works, but not tested:

Code: Select all

Select m.*, coallesce(n.Picture, "")
FROM Recipes As m, Pictures As n
WHERE n.Recordid = m.Recordid
And m.Recordid = '1'

Bernd

Hi Bernd. Tested it on my sample database with SQLiteExpert and it returned nothing at all. Found the SQLite coalesce function (with one l) and can't say I understand it.

[normeus]

This is the sample from dataBase.pb

With some records to match your request but it seems to work this way:

Code: Select all

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetTemporaryDirectory()+"Database.sqlite"


If CreateFile(0, DatabaseFile$)
   CloseFile(0)
   
   If OpenDatabase(0, DatabaseFile$, "", "")
   
      CheckDatabaseUpdate(0, "CREATE TABLE Recipes (name CHAR(50), Recordid INT)")

      CheckDatabaseUpdate(0, "INSERT INTO Recipes (name, Recordid) VALUES ('apple pie', '1')")
      CheckDatabaseUpdate(0, "INSERT INTO Recipes (name, Recordid) VALUES ('pear cobbler', '2')")
      CheckDatabaseUpdate(0, "INSERT INTO Recipes (name, Recordid) VALUES ('banana split', '3')")
      
       CheckDatabaseUpdate(0, "CREATE TABLE Pictures (picture CHAR(50), Recordid INT)")
  
      CheckDatabaseUpdate(0, "INSERT INTO Pictures (Recordid) VALUES ( '1')")
      CheckDatabaseUpdate(0, "INSERT INTO Pictures ( Recordid) VALUES ( '2')")
      CheckDatabaseUpdate(0, "INSERT INTO Pictures (Picture, Recordid) VALUES ('984726a73744857', '3')")    
      
      
      If DatabaseQuery(0, "SELECT m.*,n.Picture FROM Recipes AS m, Pictures AS n WHERE n.Recordid = m.Recordid AND m.Recordid = '3'")
      
         While NextDatabaseRow(0)
           Debug GetDatabaseString(0, 0) ;m.name
           Debug GetDatabaseString(0, 1) ;m.recordid
           Debug " -- second table ---- "
           Debug GetDatabaseString(0, 2) ;n.Pictures
         Wend
      
         FinishDatabaseQuery(0)
      EndIf
      
      CloseDatabase(0)
   Else
      Debug "Can't open database !"
   EndIf
Else
   Debug "Can't create the database file !"
EndIf

could you tweak it to match your data so we can see your error?

Thank you.
Norm.

[Fangbeast]

Hello normeus, there is no database error. My select statement is only showing records that have pictures linked in the pictures table and I can't see in that select statement where it is limiting the returned data to that.

These are the two tables

Code: Select all

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 = '7'

This correctly returns the combined table with all the data for record 7 as it has a corresponding picture in the Pictures table for record 7.

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

This returns no data at all because there is no picture available for record 3 but I can't see in that query why it would not show the data for record 3 even without a picture??? Should it not show a complete row with an empty field for where the picture isn't or can't I do that in SQL?

[s0ula55a551n]

Unless I am missing something you are asking it to return all records where recordid match on recipes and pictures, so if there is no pictures record for a recipe it will never return that record cause there is no match

[s0ula55a551n]

U need to do select all recipes and do a Join to pictures to get what you are after

[infratec]

Hi,

you need a LEFT JOIN:

Code: Select all

"SELECT m.*, n.Picture FROM Recipes AS m LEFT JOIN Pictures AS n ON n.Recordid = m.Recordid WHERE m.Recordid = '1'"

Bernd

[normeus]

And here is the working example with the info I missed in the first place. There is no record for a picture at all, I just ASSuMed a record was being created but no picture on the record. thanks @infratec for the correct statement.

Code: Select all

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetTemporaryDirectory()+"Database.sqlite"


If CreateFile(0, DatabaseFile$)
   CloseFile(0)
   
   If OpenDatabase(0, DatabaseFile$, "", "")
   
      CheckDatabaseUpdate(0, "CREATE TABLE Recipes (name CHAR(50), Recordid INT)")

      CheckDatabaseUpdate(0, "INSERT INTO Recipes (name, Recordid) VALUES ('apple pie', '1')")
      CheckDatabaseUpdate(0, "INSERT INTO Recipes (name, Recordid) VALUES ('pear cobbler', '2')")
      CheckDatabaseUpdate(0, "INSERT INTO Recipes (name, Recordid) VALUES ('banana split', '3')")
      
       CheckDatabaseUpdate(0, "CREATE TABLE Pictures (picture CHAR(50), Recordid INT)")
  
      ;CheckDatabaseUpdate(0, "INSERT INTO Pictures (Recordid) VALUES ( '1')")     ;  No picture meaning no record at all
      ;CheckDatabaseUpdate(0, "INSERT INTO Pictures ( Recordid) VALUES ( '2')")    ;  so REM out the creation of records 1 and 2
      CheckDatabaseUpdate(0, "INSERT INTO Pictures (Picture, Recordid) VALUES ('984726a73744857', '3')")    
      
      
      ;If DatabaseQuery(0, "SELECT m.*,n.Picture FROM Recipes AS m, Pictures AS n WHERE n.Recordid = m.Recordid AND m.Recordid = '1'")
      If DatabaseQuery(0, "Select m.*, n.Picture FROM Recipes As m LEFT JOIN Pictures As n ON n.Recordid = m.Recordid WHERE m.Recordid = '1'")
         While NextDatabaseRow(0)
           Debug GetDatabaseString(0, 0) ;m.name
           Debug GetDatabaseString(0, 1) ;m.recordid
           Debug " -- second table ---- "
           Debug GetDatabaseString(0, 2) ;n.Pictures
         Wend
      
         FinishDatabaseQuery(0)
      EndIf
      
      CloseDatabase(0)
   Else
      Debug "Can't open database !"
   EndIf
Else
   Debug "Can't create the database file !"
EndIf

I usually work out these statements using sqlite browser portable version http://sqlitebrowser.org/

Norm

[Fangbeast]

See, I knew there was a way!!! I am still so far behind when it comes to SQL that I can't see the forest for the alien abducted cows!!

:)

[Fangbeast]

Thanks everyone for your help, the basics are done (Sigh!).

There is so much more grunt work to be done before the program is finished. The summer temperatures certainly don't help.

Then to rework the import code (groooaaan)