Hello friends, I hope you can help me, I don't have much experience with SQL queries joining tables with inner join or left join.
I have 2 talbas in which I want to relate the data through the idCliente
1.- clients (Corporate Name, RFC, Street, Neighborhood, Municipality, State, Zip Code)
2.- Merchandise (idTrip, idClient, Client, rfc, currency, merchandise, amount, TypeOperation, DateAdded, details, TypeTransport, DateOut, DateArrival, folio, origin, origin, origin, pdestination, edestination, cdestination, policy, fee, premium , Expenses, VAT, Total)
This is the query I have so far but it tells me that several fields are ambiguous ( idCliente, rfc, FechaAlta y RFC
)
SELECT idViaje, idCliente, Cliente, rfc, moneda, mercancia, importe, TipoOperacion, FechaAlta, detalles, TipoTransporte, FechaSalida, FechaLlegada, folio, porigen, eorigen, corigen, pdestino, edestino, cdestino, poliza, cuota, prima, gastosexp, iva, total, RazonSocial, RFC, Calle, Colonia, Municipio, Estado, CP
FROM merca M, clientes C
WHERE C.idCliente=M.idCliente;
Good day, you should use aliases well so that you avoid ambiguity problems. If you want to show only the merchandise that has a customer (that is not null) then you must use INNER JOIN or JOIN, which are the same. But if you want to display the records even if they contain null values you should use LEFT JOIN or RIGHT JOIN.
The query would look like this:
Cheers!
Dear,
The error that your query shows is basically because your two tables share columns that have the same name, so when you indicate them in the SELECT, the database engine does not know which column you are trying to represent. Now, if you want to take this result and pass it to some front, I recommend using aliases, so you can indicate the specific column. The latter is done after the name of the commune, like this:
clientes.rfc AS rfc_cliente
It would be something like thisI hope I can help you
The SQL query should specify from which table it is obtaining each of the data, to avoid friendships:
In addition to this, you can put an "alias" to the columns that have the same name, so that you can later reference them, as follows:
SELECT M.idViaje, M.idCliente, M.Cliente, M.rfc rfc_merca , M.currency, M.merchandise, M.amount, M.TipoOperacion, M.FechaAlta, M.details, M.TipoTransporte, M.FechaSalida, M.Date of Arrival, M.folio, M.porigen, M.eorigin, M.corigen, M.pdestino, M.edestino, M.cdestino, M.poliza, M.cuota, M.premium, M.expenses, M. vat, M.total, C.RazonSocial, C.RFC rfc_cliente , C.Calle, C.Colonia, C.Municipio, C.Estado, C.CP FROM merca M, clients C WHERE C.idCliente=M.idCliente;
That way you can explicitly tell what you are looking for in the MYSQL Query so that it returns some data that you can manipulate without any problem.
It is not my intention to get too theoretical but, answering your question literally, we would be talking about the NATURAL JOIN operation, which you could implement like this:
As it combines by all the fields that have the same name, there will be no ambiguity, the resulting data is identical. Mind you, it's still not what you meant when you asked the question.