Returning data from linked tables

[Fangbeast]

Folks, I have attached my foreign database to my primary database aliased out as "RemoteDatabase" and then I want to return the data of two linked tables.

Code: Select all

"ATTACH 'D:\Keeper.sqlite' AS RemoteDatabase"

Is this the correct way of addressing the tables in the attached database?

Code: Select all

"SELECT m.*, "                      +   ; Get ALL fields from the picture info table
"n.* "                              +   ; Get only the picture blob from the blob table
"FROM "                             +   ; Now where do we get the data from
"RemoteDatabase.Keeper AS m, "      +   ; Fetch data from the Keeper table in the attached database
"RemoteDatabase.Attachments AS n "  +   ; Fetch attachments from the Attachments table int he attached database
"WHERE "                            +   ; Now add the conditions
"n.Attachmentid = m.Recordid"       +   ; Link the attachment id to the record id as that;s how they were saved

**UPDATE** Bugger, this query is wrong. Returns only those records that have attachments. Forget I asked folks!! (And you did :)

[spikey]

You want a LEFT JOIN.

Try:

Code: Select all

SELECT * FROM RemoteDatabase.Keeper AS m LEFT JOIN RemoteDatabase.Attachments AS n ON m.Recordid = n.Attachmentid;

[Fangbeast]

Thanks spikey, will try it. Just need sleep first but the cat keeps stealing the bed damn it!

[RobertSF]

Is this the correct way of addressing the tables in the attached database?

Code: Select all

"SELECT m.*, "                      +   ; Get ALL fields from the picture info table
"n.* "                              +   ; Get only the picture blob from the blob table
"FROM "                             +   ; Now where do we get the data from
"RemoteDatabase.Keeper AS m, "      +   ; Fetch data from the Keeper table in the attached database
"RemoteDatabase.Attachments AS n "  +   ; Fetch attachments from the Attachments table int he attached database
"WHERE "                            +   ; Now add the conditions
"n.Attachmentid = m.Recordid"       +   ; Link the attachment id to the record id as that;s how they were saved

You didn't ask about this, but seeing your code reminds me of a tip an old SQL master passed along to me. Format your queries this way, he suggested.

Code: Select all

Global SQLQuery.s = "SELECT Requests.ID" +
                    ",      PReq.PName " +
                    ",      Requests.Details " +
                    ",      Requests.Entered " +
                    ",      Requests.Assigned " +
                    ",      PFul.PName " +
                    ",      Requests.Started " +
                    ",      Requests.Completed " +
                    "FROM (Requests LEFT JOIN People AS PReq ON Requests.Requestor = PReq.ID) " +
                    "               LEFT JOIN People AS PFul ON Requests.Fulfiller = PFul.ID"

It looks a little strange, but guess what? You will never again get tripped up by a missing or extra comma. It's easy to see that every column name has a comma, and since the comma is on the same line as the next column name, you can easily rearrange columns within the query without having to delete or add commas (except for the first column, but that column is usually the primary key or record ID and is the least likely to be moved.

[Fangbeast]

You didn't ask about this, but seeing your code reminds me of a tip an old SQL master passed along to me. Format your queries this way, he suggested.

It's all good RobertSF. I am always trying to find better ways of formatting sql queries because my eyesight trips me up every time. That's why I bought an UltraWide monitor and set the dpi to 125% but I still cause trouble.

Going to start using SetDatabaseString to input the data as that will help me avoid errors with commas and single quote escaping everywhere. Less errors.

Just a few bugs in my search procedure and then I will redo the entire program.