• Registered: 2014-08-02
  • Posts: 906

Topic: SQL with join tables

Hello Dmitry

I made an application to manage my comics (MVD 1.52 v5)

On Form1 (main form), I have a grid listing all comics and on the same Form1, I have a panel
giving details for un record in the grid.

I have a main table (Comics) and many others (dictionnaires) (i.e. King of comics)

I would like to use a SQL query whose result displays informations retrieved from other tables linked with table Comics :

I've tried something like this

s := SQLExecute('SELECT Kind.kind from Comics WHERE id=' + IntToStr(Form1.TableGrid1.dbItemID) );
Form1.Label24.Caption := s;

There is no error message, but there is no information displayed for field Kind.

I think I should use instruction JOIN in my request. I've looked for this on Net but there is many cases
which are not well explained.

I traveled the entire forum (even russian forum), but all the cases submitted are simple SQL, of the ones I used to use for simple queries

Please, do you have any information about this SQL very complex (or any MVD users) ?

Thanks for your help

JB

  • Registered: 2014-06-25
  • Posts: 861

Re: SQL with join tables

There is a very interesting solution in this topic: http://myvisualdatabase.com/forum/viewt … p?id=1484. Reply 13
You would not have to use any SQL query at all


Otherwise a join is done using a relationship field in the tables

For example

Author                                   Origin                                            Title
id                                           id                                                   id
Name                                    Country                                         Title
Title                                       id_Name                                       Year
                                                                                                   id_Name
                                                                                                   id_Country


Select Title, Country, Name

from Title,
Author a,
Origin o
where
id_Name=a.id
and id_Country=o.id
and Title=something

But honestly, the solution from derek is the simpliest one

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,669

Re: SQL with join tables

Hi Jean, Tcoton, Dmitry, Everyone!

I am not sure if i understand your problem 100% (i don't even understand my OWN problems 100%!!) but it may be that you need to specify the joins to the other tables as 'left outer' in your SQL query.

I have attached a copy of a project i did which has an example of this - please see the SQL query for Button 4 on Form1.  And if you need it, the administrator password in the project is uppercase 'MVD' !

Hopefully, this might help you a bit.

Derek.

Post's attachments

Attachment icon requisitions jb.zip 483.86 kb, 854 downloads since 2015-06-06 

  • Registered: 2014-08-02
  • Posts: 906

Re: SQL with join tables

Hellon TCoton, Derek, Dmitry

Thanks for your help.
Next week I'll consider your answers

Thanks again

JB

  • Registered: 2014-08-02
  • Posts: 906

Re: SQL with join tables

Hello Derek

I could not help but test your proposal before before taking a week's vacation.
Well, it fits well.
Your solution was the solution.
I thought that I had to use left outer join in the query but I lost myself in the identification syntax
by associating all the different tables id.

Thanks again for your help

JB

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,669

Re: SQL with join tables

Hi Jean,
Glad it helped.
Now you can go on holiday and not be thinking about it!
Derek.