It is currently Thu Nov 21, 2019 10:04 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: Get all contact data for all records in one query
PostPosted: Mon Jul 22, 2019 5:35 am 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4500
Location: Not Sydney!!! (Bad water, no goats)
If I have a query to return all data from the db_addresses such as below:

Code:
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 * 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)

Quote:
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 * 
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Get all contact data for all records in one query
PostPosted: Mon Jul 22, 2019 7:25 am 
Offline
Addict
Addict
User avatar

Joined: Tue Oct 09, 2007 2:15 am
Posts: 1090
MayBe INNER JOIN will help you

http://www.sqlitetutorial.net/sqlite-inner-join/

_________________
PureBasic 5.71 LTS (Windows x86/x64) | Windows10 Pro x64 | Z370 Extreme4 | i7 8770k | 32GB RAM | iChill GeForce GTX 980 X4 Ultra | HAF XF Evo​​
English is not my native language... (I often use DeepL to translate my texts.)


Top
 Profile  
Reply with quote  
 Post subject: Re: Get all contact data for all records in one query
PostPosted: Mon Jul 22, 2019 12:50 pm 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4500
Location: Not Sydney!!! (Bad water, no goats)
Bisonte wrote:


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


Top
 Profile  
Reply with quote  
 Post subject: Re: Get all contact data for all records in one query
PostPosted: Tue Jul 23, 2019 12:52 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 22, 2010 1:17 pm
Posts: 311
Location: United Kingdom
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Get all contact data for all records in one query
PostPosted: Tue Jul 23, 2019 11:52 pm 
Offline
Enthusiast
Enthusiast

Joined: Sat Jul 03, 2004 9:04 am
Posts: 216
Location: Sydney Australia
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Get all contact data for all records in one query
PostPosted: Wed Jul 24, 2019 12:29 pm 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4500
Location: Not Sydney!!! (Bad water, no goats)
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:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Get all contact data for all records in one query
PostPosted: Thu Jul 25, 2019 5:30 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 22, 2010 1:17 pm
Posts: 311
Location: United Kingdom
Code:
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Get all contact data for all records in one query
PostPosted: Fri Jul 26, 2019 1:00 am 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4500
Location: Not Sydney!!! (Bad water, no goats)
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:
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


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye