Menu
I need to join two tables on column, which in one table is defined as string and in other as integer. In both columns actual data stored is integers. This is given to me by design - I can't change this.
So when I do join MySQL is fine - it does conversion silently. PostgreSQL complains. There are CAST operators, which I can add to query to have strings converted to integers, but CAST function is defined in different RDBMSs differently.
Can I write this query in the way that it works in all (or many) RDBMSs? Alternatively, is there DB abstraction layer, which can do this for me? ADODB is already used in the project, but I can't see if and how it can be helpful in solving this problem.
Thank you.
209
Different Types of SQL JOINs(INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
SQL join multiple tables is one of the most popular types of statements executed while handling relational databases. As known, there are five types of join operations: Inner, Left, Right, Full and Cross joins.
In DBMS, a join statement is mainly used to combine two tables based on a specified common field between them. If we talk in terms of Relational algebra, it is the cartesian product of two tables followed by the selection operation.
For example, if there is a function-based index on EMPLOYEE such as SUBSTR(id, 2, LENGTH(id) - 1) then you'll want to use that in your query: SELECT e.name, i.name FROM employee e INNER JOIN instructor i ON SUBSTR(e.id, 2, LENGTH(e.id) - 1) = SUBSTR(i.id, 2, LENGTH(i.id) - 1);
Since you can't CAST to INT in the same way for each database due to data types, you can cast your numeric field to CHAR:
CAST(a.numeric_Field AS CHAR(5))` = b.stringfield
That will work on Postgresql, MySQL, SQL Server, not sure about others.
79
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
