Menu
I have a table(T1) in which 2 columns(X and Y) are id's. The name's of these corresponding id's are in another table (T2) with the column name.
Suppose I was only using X then, a simple Inner join would have solved my problem in getting the name.
Such as
Select T1.somedata,T1.somedata1,T2.name from T1
Inner Join T2 ON T1.X=T2.id
But,what if I want the name's to be resolved for the T1.Y also?, which name would the Inner Join resolve it to ??
Select T1.somedata,T1.somedata1,T2.name from T1
Inner Join T2 ON T1.X=T2.id
Inner Join T2 ON T1.Y=T2.id
The above query is wrong, I know. Can I get the names of those corresponding to both T1.Xand T1.Y with an INNER Join?
-Beginner
901
Inner Join is the method of retrieval of data from multiple tables based on a required condition and necessary conditions are that there must be common columns or matched columns between the two tables of the database and the data types of columns must be the same.
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns.
To join two tables based on a column match without loosing any of the data from the left table, you would use a LEFT OUTER JOIN. Left outer joins are used when you want to get all the values from one table but only the records that match the left table from the right table.
Method 1: Relational Algebra Relational algebra is the most common way of writing a query and also the most natural way to do so. The code is clean, easy to troubleshoot, and unsurprisingly, it is also the most efficient way to join two tables.
I suggest always add aliases to tables and columns. So you will be sure which data are selected.
select
T1.somedata,
T1.somedata1,
T2X.name as XName,
T2Y.name as YName
from T1 as T1
inner join T2 as T2X on T2X.id = T1.X
inner join T2 as T2Y on T2Y.id = T1.Y
This selects both names as separate columns:
Select T1.somedata,T1.somedata1,T2a.name, T2b.name
from T1
Inner Join T2 as T2a ON T1.X=T2a.id
Inner Join T2 as T2b ON T1.Y=T2b.id
The following would generate two records in the result set:
Select T1.somedata, T1.somedata1, T2.name
from T1
Inner Join T2 ON T1.X=T2.id Or T1.Y=T2.id
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
