How to Use a Table of another table in mysql

Hi.

I have a query where I get several columns of a table using MySQL, using Where certain column equals to something. But what If I need to add a column that exists on the second table.

Example:

SELECT Sello_Digital_UUID, Emitter_RFC, Emitter_CompanyName, Total, EmittedDate FROM Emitted_Invoices.
So, this is the first Table.

But happens that I have another extra table when I store Retained Taxes of each invoice, the table is named “Retained_Taxes”
And has this three columns:

  • Sello_Digital_UUID
  • Emitted Date
  • TotalTaxes
  • KindofTax
  • TaxRate

So I want to Get these columns : TotalTaxes, KindofTax of this second table and the first ones of the first table when Sello_Digital_UUID are the same on both tables.

Is this possible?
I’ve heard about JOIN command of Mysql, but Certainly i have my head totally scrambled like eggs. :smiley:
I’ve tried to do it, but I dunno if this command works to me, or what did you suggest?

Thanks in advance

This should work

SELECT Sello_Digital_UUID, Emitter_RFC, Emitter_CompanyName, Total, EmittedDate, TotalTaxes, KindofTax FROM Emitted_Invoices INNER JOIN Retained_Taxes ON Retained_Taxes.Sello_Digital_UUID = Emitted_Invoices.Sello_Digital_UUID

In this case Inner join requires both tables to have at least one Sello_Digital_UUID that are equal. You could change that to a Left Outer Join which only requires that records exist for Emitted_Invoices.

You could potentially get multiple records where there are more than one Retained_Taxes that match Emitted_Invoices Sello_Digital_UUID.

HTH, but @Dave S is more of a guru in this area.

[quote=276687:@Wayne Golding]This should work

SELECT Sello_Digital_UUID, Emitter_RFC, Emitter_CompanyName, Total, EmittedDate, TotalTaxes, KindofTax FROM Emitted_Invoices INNER JOIN Retained_Taxes ON Retained_Taxes.Sello_Digital_UUID = Emitted_Invoices.Sello_Digital_UUID

In this case Inner join requires both tables to have at least one Sello_Digital_UUID that are equal. You could change that to a Left Outer Join which only requires that records exist for Emitted_Invoices.

You could potentially get multiple records where there are more than one Retained_Taxes that match Emitted_Invoices Sello_Digital_UUID.

HTH, but @Dave S is more of a guru in this area.[/quote]
Thanks Wayne, I’ll gonna test it. In fact as you said, both tables has the same column. But sometimes an Invoice has normally one kind of Tax, But sometimes it goy two kind of Taxes. So It means that for each Invoice or record on Emitted_Invoices has two records in Retained_Taxes Table

This is a great SQL primer: http://www.sohamkamani.com/blog/2016/07/07/a-beginners-guide-to-sql/

This will get all the records in your first table, that HAVE a match in the second table. Records in the first table that DO NOT have a match in the second table will not be returned. It will return MULTIPLE records from the first table IF there are multiple matches in the second table, even if kindoftax and taxrate are duplicated. If you want only UNIQUE matches, add the word DISTINCT right after the SELECT Statement

SELECT a.Sello_Digital_UUID, 
       a.Emitter_RFC, 
       a.Emitter_CompanyName, 
       a.Total, 
       a.EmittedDate,
       b.KindOfTax,
       b.TaxRate
  FROM Emitted_Invoices a,
       Retained_Taxes   b
  Where a.Sello_Digital_UUID=b.Sello_Digital_UUID

IF you want ALL the records in the first table, and just add the data from the second table if there is a match then do it this way, in this case KindofTax and TaxRate will be NULL if there is a record in first table, but no match in the second. The comment above about DISTINCT can apply here as well

SELECT a.Sello_Digital_UUID, 
       a.Emitter_RFC, 
       a.Emitter_CompanyName, 
       a.Total, 
       a.EmittedDate,
       b.KindOfTax,
       b.TaxRate
  FROM Emitted_Invoices a
  LEFT JOIN Retained_Taxes   b
  ON a.Sello_Digital_UUID=b.Sello_Digital_UUID

This applies to almost any type of relational database [mySQL, SQLite, Oracle etc]