Get all contact data for all records in one query

Everything else that doesn't fall into one of the other PB categories.
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4747
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Get all contact data for all records in one query

Post by Fangbeast »

If I have a query to return all data from the db_addresses such as below:

Code: Select all

SELECT * FROM db_addresses
Is there any way to select all the contact data for each address from the db_addressescontacts table for each one in the same query?

Something like:

Code: Select all

SELECT * FROM db_addresses (SELECT FROM db_addressescontacts WHERE db_addresses.recordid = db_addressescontacts.recordid)
Tried this, didn't work but there must be a way to tie these together.

Data return would look something like: (Benny only has 2 items of contact data, others, more)
Benny Benassi
20204849
forkle@dingo.com
Robert Pyjama
callan@festering.com
http://grr.poop.bum.nz
id:20174394739
skypid:flappit
IF I use this:

Code: Select all

SELECT *  
FROM db_addresses  
AS m  
LEFT JOIN db_addressescontacts  
AS n  
ON m.recordid = n.recordid
I get multiple duplicated records for each contact item which isn't what I need.
Amateur Radio, D-STAR/VK3HAF
User avatar
Bisonte
Addict
Addict
Posts: 1226
Joined: Tue Oct 09, 2007 2:15 am

Re: Get all contact data for all records in one query

Post by Bisonte »

PureBasic 6.04 LTS (Windows x86/x64) | Windows10 Pro x64 | Asus TUF X570 Gaming Plus | R9 5900X | 64GB RAM | GeForce RTX 3080 TI iChill X4 | HAF XF Evo | build by vannicom​​
English is not my native language... (I often use DeepL to translate my texts.)
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4747
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Re: Get all contact data for all records in one query

Post by Fangbeast »

Bisonte wrote:MayBe INNER JOIN will help you

http://www.sqlitetutorial.net/sqlite-inner-join/
Don't understand it, that's the thing. Am playing with a few query variations and will keep reading tomorrow but for now, when I do:

Code: Select all

SELECT
 titlename,
 firstname,
 middlename,
 lastname
FROM
 db_addresses
INNER JOIN db_addressescontacts ON db_addressescontacts.recordid = db_addresses.recordid;
Which returns multiple names for each contact data but no contact data (obviously) and then I reversed the query to see what would happen. Interesting but now what I need in the previous post.

Oh well, not that I was doing anything useful yet:):)
Amateur Radio, D-STAR/VK3HAF
User avatar
spikey
Enthusiast
Enthusiast
Posts: 581
Joined: Wed Sep 22, 2010 1:17 pm
Location: United Kingdom

Re: Get all contact data for all records in one query

Post by spikey »

Difficult to answer this sort of question without knowing what the schema contains - SELECT * doesn't tell me much, other than the obvious!

Post the schema for both tables - and an INSERT ... VALUES type SQL for some demo data wouldn't go amiss either... Saves time trying to guess what the schema might be...

I can tell you that a JOIN isn't going to deliver the results you want though. It expects to make an aggregate of the one for one correlation between two tables - possibly inserting nulls when one or other of the tables doesn't fully correlate. That's not what you are describing - it was what you were getting though :D
swan
Enthusiast
Enthusiast
Posts: 225
Joined: Sat Jul 03, 2004 9:04 am
Location: Sydney Australia
Contact:

Re: Get all contact data for all records in one query

Post by swan »

If your getting duplicate records with Inner Join (as long as their the right records) start with Select Distinct * (& so on). It will return only one of each record.
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4747
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Re: Get all contact data for all records in one query

Post by Fangbeast »

spikey wrote:Difficult to answer this sort of question without knowing what the schema contains - SELECT * doesn't tell me much, other than the obvious!

Post the schema for both tables - and an INSERT ... VALUES type SQL for some demo data wouldn't go amiss either... Saves time trying to guess what the schema might be...

I can tell you that a JOIN isn't going to deliver the results you want though. It expects to make an aggregate of the one for one correlation between two tables - possibly inserting nulls when one or other of the tables doesn't fully correlate. That's not what you are describing - it was what you were getting though :D
It's 5 degrees here and I can barely move my fingers.

Just got the schema for now, more later when I can unfreeze myself.

Code: Select all

Table db_addresses
        titlename TEXT
        firstname TEXT
        middlename TEXT
        lastname TEXT
        medical TEXT
        business TEXT
        nickname TEXT
        customname TEXT
        amateurname TEXT
        pobox TEXT
        street TEXT
        suburb TEXT
        city TEXT
        state TEXT
        postcode TEXT
        country TEXT
        displayformat TEXT
        displayas TEXT
        ownername TEXT
        category TEXT
        comment TEXT
        picture BLOB
        label TEXT
        archived TEXT
        favourite TEXT
        locked TEXT
        deleted TEXT
        updated TEXT
        recordid  INTEGER PRIMARY KEY AUTOINCREMENT
        UNIQUE(titlename,firstname,middlename,lastname,business,nickname,customname)

Table db_addressescontacts
        contactid INTEGER
        contacttype TEXT
        contactdetails TEXT
        recordid  INTEGER PRIMARY KEY AUTOINCREMENT
      	FOREIGN KEYcontactid REFERENCES db_addresses(recordid)) ON DELETE CASCADE
Amateur Radio, D-STAR/VK3HAF
User avatar
spikey
Enthusiast
Enthusiast
Posts: 581
Joined: Wed Sep 22, 2010 1:17 pm
Location: United Kingdom

Re: Get all contact data for all records in one query

Post by spikey »

Code: Select all

SELECT recordid, '1' AS seq, 'Name' as contacttype, titlename, firstname, middlename, lastname 
FROM db_addresses 
UNION 
SELECT contactid, '2' AS seq, contacttype, contactdetails, NULL AS firstname, NULL AS middlename, NULL AS lastname 
FROM db_addressescontacts
ORDER BY recordid, seq ASC;
There are a few important notes:-

1) This is a UNION. Unions will only work if the two sets contain the same number of columns, so I've had to pad out the 'db_addressescontacts' output with three NULLs to balance. If you alter the output you must alter it symmetrically or it will break the query.
2) Columns are named from the first SELECT statement executed.
3) The "# as seq" bits are mandatory - it won't work without properly without them, so don't try to tidy them out. Just exclude them when you display the results.
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4747
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Re: Get all contact data for all records in one query

Post by Fangbeast »

Eek!! Never understand that code in a million years (Join that to never understand women at all)

It's interesting but not outputting the contacts for each person after each person's details from db_addresses.

Thanks for the help Spikey, when the weather warms I can actually think a little when the neurons start to flow.

This is what I will do below as I understand it. Just going to flesh the statements out a bit with some conditionals that come from text boxes: (Obviously lots of custom stuff in there but this has worked befored. Not elegant but works)

Code: Select all

Procedure ShowCategoryAddresses()
  ; 
  GetCurrentCategory()
  ; 
  ; Debug "The current line is:::           " + Str(CurrentCategoryLine.i)
  ; Debug "The current category name is:::  " + CurrentCategory.s
  ; Debug "The current table name is:::     " + TableName.s
  ; Debug "The current procedure is:::      " + #PB_Compiler_Procedure
  ; Debug "-----------------------------------------------------------------------------------------"
  ; Remove the columns for this database
  RemoveGadgetColumn(#Gadget_MyInfo_Titles, #PB_All)
  ; Set the columns for this database
  AddGadgetColumn(#Gadget_MyInfo_Titles,  1,  "Item title",     440)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  2,  "Home phone",     100)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  3,  "Work phone",     100)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  4,  "Mobile",         100)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  5,  "Fax number",     100)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  6,  "Email address",  160)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  7,  "Callsign",        80)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  8,  "Archived",         0)
  AddGadgetColumn(#Gadget_MyInfo_Titles,  9,  "Favourite",        0)
  AddGadgetColumn(#Gadget_MyInfo_Titles, 10,  "Locked",           0)
  AddGadgetColumn(#Gadget_MyInfo_Titles, 11,  "Deleted",          0)
  AddGadgetColumn(#Gadget_MyInfo_Titles, 12,  "Updated",          0)
  AddGadgetColumn(#Gadget_MyInfo_Titles, 13,  "Recordid",         0)
  ; 
  Queue\CurrentCategory.s = Category\Category.s
  Queue\CategoryItems.s   = Category\Hasitems.s
  ; 
  Program\LastCategory.s = Queue\CurrentCategory.s
  ; 
  ;Debug "We got a current category selection:::  " + Queue\CurrentCategory.s
  ; Keep track of the current line to feed to the string display routine
  CurrentLine.i = #False
  ; Create a new list of record numbers we will use to fetch data with for linked tables
  NewList RecordNumbers.s()
  ; Fetch all recordid numbers from the selected table
  If DatabaseQuery(Program\DatabaseHandle, "SELECT recordid FROM db_addresses ORDER BY recordid") <>  #DatabaseQueryFail
    While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
      CurrentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 0)
      If CurrentRecordid.s  <>  #Empty$
        AddElement(RecordNumbers.s())
        RecordNumbers.s() = CurrentRecordid.s
      Else
        ; There was no current record id. Should never happen
      EndIf
      CurrentRecordid.s = #Empty$
    Wend
    FinishDatabaseQuery(Program\DatabaseHandle)
  Else
    SetInfoBarArea("Headings",  "Error",  "The database query has failed or was empty: " + DatabaseError(), #PB_Compiler_Procedure)
  EndIf
  ; Contact new list for temporary contact data to feed to display routine
  NewList TempContacts.ContactData()
  ; Iterate through the linked list and fetch each record along with the associated contact data
  ForEach RecordNumbers.s()
    ; Fetch all address data from the db_addresses table for each recordid in the list
    SetDatabaseString(Program\DatabaseHandle, 0, RecordNumbers.s())
    If DatabaseQuery(Program\DatabaseHandle, "SELECT * FROM db_addresses WHERE recordid  = ?") <>  #DatabaseQueryFail
      While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
        CurrentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 0)
        CurrentRecordid.s = #Empty$
      Wend
      FinishDatabaseQuery(Program\DatabaseHandle)
    Else
      SetInfoBarArea("Headings",  "Error",  "The database query has failed or was empty: " + DatabaseError(), #PB_Compiler_Procedure)
    EndIf
    ; Fetch all contact data for each recordid in the list that has linked contact data
    SetDatabaseString(Program\DatabaseHandle, 0, RecordNumbers.s())
    QueryString.s = "SELECT contacttype, contactdata FROM db_addressescontacts WHERE recordid  = ?"
    If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <>  #DatabaseQueryFail
      While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
        CurrentContactType.s = GetDatabaseString(Program\DatabaseHandle, 0)
        CurrentContactData.s = GetDatabaseString(Program\DatabaseHandle, 1)
        If CurrentContactData.s  <>  #Empty$
          AddElement(TempContacts.s())
          TempContacts.s()\contacttype.s  = CurrentContactType.s
          TempContacts.s()\contactdata.s  = CurrentContactData.s
        Else
          ; There was no current record id. Should never happen
        EndIf
        CurrentRecordid.s = #Empty$
      Wend
      FinishDatabaseQuery(Program\DatabaseHandle)
    Else
      SetInfoBarArea("Headings",  "Error",  "The database query has failed or was empty: " + DatabaseError(), #PB_Compiler_Procedure)
    EndIf
    ; Display the new string of address data, parse the list for each line
    DisplayAddressTitleString(CurrentLine.i, List ShowContacts.TempContactsData())
    ; 
    CurrentLine.i + 1
    ; 
  Next
EndProcedure
Amateur Radio, D-STAR/VK3HAF
Post Reply