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.
I’ve tried to do it, but I dunno if this command works to me, or what did you suggest?
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.
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 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]