May 16, 2011 at 10:21 pm
Hi Experts,
can we use more than one join for 2 tables.
Thanks
Venki
May 16, 2011 at 11:30 pm
Hi,
Could you perhaps clarify your question?
Giving an example may help.
If you are asking if two tables can be joined on multiple conditions, the answer is yes.
May 16, 2011 at 11:50 pm
SELECT md.number,sd.description from main_data md,sub_data sd
WHERE 1=1
AND md.vendor_id=sd.vendor_id(+)
AND md.inventory_id=sd.item_id(+)
Here i am using 2 tables main_data and sub_data, as for join rules we have to give n-1 join conditions, that menas for 2 tables we have only one join condition,but here they used 2 join conditions for 2 tables.
Thanks
Venki
May 17, 2011 at 12:04 am
I'm not sure why you believe that only n-1 conditions are allowed.
In the case of a table using a composite key, your join conditions could be as many members as there are to the key.
As a side note, that join syntax is not recommended(for sql server). If I remember correctly, that is the old ansi syntax for a right join. You may want to double check the meaning of where the (+) is positioned.
Rather use the following syntax:
From TableA A
Right Join TableB B
On A.F1 = B.F1
And A.F2 = B.F2
Excuse formatting; awkward writing from mobile.
May 17, 2011 at 1:05 am
Do you want to extract the only matching condition from Sub table or anything from Sub_data table. Use the current format:
*************************
SELECT md.number,sd.description
from main_data md
Right* Join sub_data sd on (md.vendor_id=sd.vendor_id AND md.inventory_id=sd.item_id)
-------------------------------------
*= If everything from Sub_data then use Right Outer Join
May 17, 2011 at 2:44 am
ursfriend77 (5/16/2011)
SELECT md.number,sd.description from main_data md,sub_data sdWHERE 1=1
AND md.vendor_id=sd.vendor_id(+)
AND md.inventory_id=sd.item_id(+)
Here i am using 2 tables main_data and sub_data, as for join rules we have to give n-1 join conditions, that menas for 2 tables we have only one join condition,but here they used 2 join conditions for 2 tables.
Thanks
Venki
Do you mean "Can the same table be joined more than once?" If so, then yes of course;
SELECT md.number, sd1.description, sd2.<<somecolumn>>
FROM main_data md
<<whatever>> JOIN sub_data sd1 ON md.vendor_id = sd1.vendor_id
<<whatever>> JOIN sub_data sd2 ON md.inventory_id = sd2.item_id
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 17, 2011 at 6:33 am
The problem is, we don't understand what you mean by N+1 JOIN conditions. Real life examples would help us give you the proper code. We're not asking about company data. We're asking for an English translation of a business rule.
Not knowing what you're asking for, here are a few more suggestions. Don't forget that you can use OR in the JOIN clauses.
SELECT t1.Col1, t1.Col2, t2.Col3
FROM table1 t1
INNER JOIN table2 t2
ON t1.Col1 = t2.Col1
or t1.Col1 = t2.Col2
Caveat: Just because you can, doesn't mean you should. Code like the above is messy and likely to screw up your results set if you don't know what you're doing. Be very careful before introducing something like this to your environment.
Also, If you're joining table 1 on one condition with table 2 having multiple conditions (one to many join), you can always use IN. This keyword is safer and its behavior is very predictable.
SELECT t1.Col1, t1.Col2, t2.Col3
FROM table1
WHERE Col1 IN (SELECT Col1 FROM table2)
Lastly, there's the "I want Table1.Col1 and Table2.Col1 to be equal, but I only want the values from Table2 where Col2 equals 'ABC' " version of JOINs.
SELECT t1.Col1, t1.Col2, t2.Col3
FROM table1 t1
INNER JOIN table2 t2
ON t1.Col1 = t2.Col1
AND t2.Col2 = 'ABC'
May 17, 2011 at 6:57 am
The problem is, we don't understand what you mean by N+1 JOIN conditions.
I took N-1 (not N+1) to mean that he believes that the table to join condition ratio should as a rule be n:n-1. For example:
2(n) tables joined by 1(n-1) condition or
5(n) tables joined by 4(n-1) conditions.
n-1 is certainly a minimum - I'm not sure how it could be a maximum.
May 17, 2011 at 7:08 am
Thanks, diamond, but I'm not enough of a geek to be able to easily translate that. Any chance you could explain what you just said using English examples?
May 17, 2011 at 7:48 am
Brandie Tarvin (5/17/2011)
Thanks, diamond, but I'm not enough of a geek to be able to easily translate that. Any chance you could explain what you just said using English examples?
I'll try 😉
If you had 2 cities, the minimum amount of roads you would need to connect them would be 1(2-1).
Similarly, if you had 10 cities, the minimum amount of roads needed to connect them (allowing travel from any city, to any other) would be 9(10-1).
It can therefore be said that one requires a minimum of n-1 roads between cities to enable travel from and to any city on the map.
If someone were to ask you how many roads you would need to connect a number of cities, you could answer them: n-1. As opposed to saying "you would need 1 less road then your count of cities"
Hope that helps?
May 17, 2011 at 7:53 am
ursfriend77 (5/16/2011)
SELECT md.number,sd.description from main_data md,sub_data sdWHERE 1=1
AND md.vendor_id=sd.vendor_id(+)
AND md.inventory_id=sd.item_id(+)
Here i am using 2 tables main_data and sub_data, as for join rules we have to give n-1 join conditions, that menas for 2 tables we have only one join condition,but here they used 2 join conditions for 2 tables.
Thanks
Venki
"as for join rules we have to give n1 join conditions". May I ask why this seemingly arbitrary rule? Are you dealing with a real-world problem to solve (in which case I would expect the question to be more along the lines of "I need to return the following data, and I'm having problems doing so"), or are you dealing with a homework assignment?
-Ki
May 18, 2011 at 9:54 am
I have seen and have used JOINS based on multiple conditions
SELECT c.LastName, c.FirstName
FROM Contacts c INNER JOIN Areas a
ON c.PostalCode = a.PostalCode
AND c.City = a.City
WHERE c.TaxesPaid = 1
another variation is the so called "Formula Join" which basically adds a JOIN condition in lieu of a WHERE statement. The above would be rewritten as
SELECT c.LastName, c.FirstName
FROM Contacts c INNER JOIN Areas a
ON c.PostalCode = a.PostalCode
AND c.City = a.City
AND c.TaxesPaid = 1
Viewed in a query diagram plane, the "diamond" indicator would be filled with an fx notation.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply