• Registered: 2020-06-26
  • Posts: 302

Topic: Data from 2 tables using Report(SQL

Hello,
I've just started using Report(SQL) and I have a question.
Here's my SQL statement:
.
select EquipName, EqModel, EqPurchDate,
       EqNotes, EqSparePart, EquipType
             
from tbl_Equip, tbl_EquipType

where tbl_Equip.EqSparePart LIKE "Installed" 
.
This code worked fine for data from the tbl_Equip table. Then I added a field from another table tbl_EquipType.  When I run it as written above I get the records from tbl_Equip but I also get ALL of the records from tbl_EquipType (it is a lookup table and has 24 entries for EquipType). I only want to see the one that is associated with each tbl_Equip record.
.
I'm thinking the query might need some kind of a JOIN clause.  Since my SQL skills are limited, I'm not sure if that's what I need and if so, how to do it.
.
Thanks
Frank

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 895

Re: Data from 2 tables using Report(SQL

Yes, you need to use JOIN.

A JOIN clause is used to combine rows from two or more tables, based on a related column between them

The simplest JOIN example: 

SELECT EquipName, EqModel, EqPurchDate,
       EqNotes, EqSparePart, EquipType
             
FROM tbl_Equip, tbl_EquipType

WHERE tbl_Equip.RELATED COLUMN = tbl_EquipType.RELATED COLUMN

AND tbl_Equip.EqSparePart LIKE "Installed"

RELATED COLUMN - replace with your related column.

you can read about JOIN on the Internet.

  • Registered: 2020-06-26
  • Posts: 302

Re: Data from 2 tables using Report(SQL

Sparrow,
Per your example, here's what I have tried:
select EquipName, EqModel, EqPurchDate,
       EqNotes, EqSparePart, EquipType
               
from tbl_Equip, tbl_EquipType

WHERE tbl_Equip.id_tbl_EquipType = tbl_EquipType.EquipType

AND tbl_Equip.EqSparePart LIKE "Installed"     
.
My tbl_Equip table has RELATIONSHIP field called id_tbl_EquipType
My tbl_EquipType table has a field called EquipType
My frmEquip.cboEqType field uses the relationship to EquipType.EquipType to get the Type for the form.
.
When I run the query above it does not return any records in my report, it's just blank.
.
Thanks for helping me with this
Frank

.

4 Reply by derek (edited by derek 2021-10-05 20:56:28)

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

Re: Data from 2 tables using Report(SQL

You have specified your join incorrectly.
It should be

where tbl_equip.id_tbl_equiptype = tbl_equiptype.id

Derek.

5 Reply by papafrankc (edited by papafrankc 2021-10-05 23:15:18)

  • Registered: 2020-06-26
  • Posts: 302

Re: Data from 2 tables using Report(SQL

Derek,
That was it.  Now it's working perfectly.
.
I have a couple of questions.
First - I see that you are referring to tbl_equiptype.id. I understand that is referring to the ID for the tbl_Equip table.  When I look in the tbl_Equip structure I don't see a field with that name.  I'm assuming this is just something you have to know exists??
I've seen that referenced before but never fully understood where it came from.
With this format I should be able to use fields from related tables going forward.
.
SOLVED Second - The date field EqPurchDate is in the format of 10/03/2001 in MVD.
However when it gets to FastReport the format is 2001-10-03 00:00:00:000
How do I get the date to show up like it is in MVD ?
Here's what I did: I used part of a formatting command that you provided some time ago for my calcNextSvcDate field: strftime('%m/%d/%Y',date(julianday((select EqPurchDate)))),
.
See I have learned some of the things you taught me earlier smile
.
Many thanks to yourself and Sparrow for helping me with this.
Frank

6 Reply by derek (edited by derek 2021-10-06 00:30:57)

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

Re: Data from 2 tables using Report(SQL

Frank,
The ID is typically called the primary key - every table has one and it's fundamental to understanding how a relational database hangs works.
When you create a relationship to a second table, you are using the primary key of the second table as a link; this link is called a foreign key and the foreign key(s) are stored on the first table. 
A table will have as many foreign keys as it does relationships to other tables (in the attached example, tbl_equip has 2 foreign keys). 
Dmitry has done a fantastic job in automating all of this 'internally' which is why you don't see the ID referred to in the data schema.
Have a look at the attachment (specifically the screenshot and the button (view raw data).  Then add a new piece of equipment and go back to the 'view raw data' and follow the links through to see how all the related information for the new item gets stored. 
It's just my opinion but I think when you have even a basic understanding of how a relational database works, many of the things you do in MVD start to make a lot more sense.
Derek.

Post's attachments

Attachment icon pfreportsql.zip 517.15 kb, 176 downloads since 2021-10-06 

  • Registered: 2020-06-26
  • Posts: 302

Re: Data from 2 tables using Report(SQL

Derek,
Many thanks for the explanation and the sample program.  It does help to clear up a few things.
The fact, as you say, that MVD takes care of so many things automatically is one of the many strong points of the program.
.
And when you venture outside of MVD basics, I see where an understanding of SQL becomes so valuable.
.
Thanks, Frank