• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

SQL join question

I'm reviewing some old code, and came accross something I implemented a while back and can't remember what it does.

Normally when I join tables, it looks like:
LEFT OUTER JOIN Contacts C ON C.ContactNo = I.BillContactNo (as an example)

Here's what I came accross, and need help understanding what it does:
LEFT OUTER JOIN Contacts C ON C.ContactNo IN (I.BillContactNo, J.OrdContactNo)
0
pzozulka
Asked:
pzozulka
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
LEFT OUTER JOIN Contacts C ON (C.ContactNo = I.BillContactNo
   OR C.ContactNo =J.OrdContactNo)
0
 
pzozulkaAuthor Commented:
I've never seen that format either. Not sure how you are able to join on multiple tables, and even furthermore, not sure how the OR applies here, instead of AND (perhaps).
0
 
awking00Commented:
Without seeing what it is that comes before "LEFT OUTER JOIN", it is difficult to see what is trying to be accomplished. Perhaps you can post the entire query with some sample data and the expected results.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pzozulkaAuthor Commented:
Sure. The entire code is below. I'm not in front of the SQL server right now, so I'm not sure what the data looks like at the moment. Please let me know if the query is enough, or if you'd need me to provide the data as well? Thanks.

Select J.JobNo, J.JobDate, RJ.DisplayName as 'SalesRep', Co.CodeName as 'Status',

CASE WHEN C.ContactNo = J.OrdContactNo THEN 'Yes' ELSE 'No' END AS 'OrdAtty',

C.DisplayName, F.City, S.StateName, C.MainPhone, RC.DisplayName As 'ContactSalesRep', F.FirmName, 

RF.DisplayName AS 'FirmSalesRep', Ca.CaseName


From Invoices I LEFT OUTER JOIN Jobs J ON J.JobNo = I.JobNo

LEFT OUTER JOIN Contacts C ON C.ContactNo IN (I.BillContactNo, J.OrdContactNo)

LEFT OUTER JOIN Firms F ON F.FirmNo = C.FirmNo

LEFT OUTER JOIN Cases Ca ON Ca.CaseNo = J.CaseNo

LEFT OUTER JOIN Resources RJ ON RJ.RsrcNo = J.SalesRep

LEFT OUTER JOIN Resources RC ON RC.RsrcNo = C.SalesRep

LEFT OUTER JOIN Resources RF ON RF.RsrcNo = F.SalesRep

LEFT OUTER JOIN Code Co ON J.Status = Co.CodeNo

LEFT OUTER JOIN State S ON F.StateNo = S.StateNo
 

Where J.Status = 807 AND

I.InvDate BETWEEN '9/12/2012' AND GETDATE()

Group By J.JobNo, J.JobDate, Co.CodeName, C.DisplayName, F.FirmName,

RJ.DisplayName, Ca.CaseName, RC.DisplayName, C.MainPhone, RF.DisplayName, F.City,

C.ContactNo, J.OrdContactNo, S.StateName

Order By RJ.SalesRep, J.JobNo

Open in new window

0
 
hnasrCommented:
For joins use equal with On phrase.

Use where clause with other conditions.

Example:
Tables a(a, r), b(a,b,r)

SELECT a.a, a.r, b.n
FROM a LEFT OUTER JOIN b ON a.a = b.a
WHERE b.r IN ('hi', 'Hello')
0
 
Scott PletcherSenior DBACommented:
>>
LEFT OUTER JOIN Contacts C ON
    C.ContactNo = I.BillContactNo OR
    C.ContactNo =J.OrdContactNo

not sure how the OR applies here, instead of AND (perhaps).
<<


OR is right; AND will not work because then I. and J. columns would have to be exactly the same.

I can join a single row to as many other rows as I need to.  For example:



USE tempdb
GO
DROP TABLE dbo.test1
DROP TABLE dbo.test2
GO
SET NOCOUNT ON

CREATE TABLE dbo.test1 ( c1 int )
CREATE TABLE dbo.test2 ( c2 int, description varchar(30) )

INSERT INTO dbo.test1 VALUES(10)
INSERT INTO dbo.test1 VALUES(20)

INSERT INTO dbo.test2 VALUES(10, '10-10')
INSERT INTO dbo.test2 VALUES(11, '10-11')
INSERT INTO dbo.test2 VALUES(12, '10-12')
INSERT INTO dbo.test2 VALUES(13, '10-13')
INSERT INTO dbo.test2 VALUES(20, '20-20')
INSERT INTO dbo.test2 VALUES(21, '20-21')


SET NOCOUNT OFF

SELECT t1.*, t2.*
FROM dbo.test1 t1
LEFT OUTER JOIN dbo.test2 t2 ON
    t2.c2 BETWEEN t1.c1 AND t1.c1 + 9
ORDER BY
    t1.c1, t2.c2
0
 
hnasrCommented:
Visual query designers, like in msaccess, does not represent the join expressions in visual query design. Although it works in sql view.

It is preferable to equate fields in the On clause and migrate other conditions to Where Clause.
0
 
pzozulkaAuthor Commented:
ScottPletcher:
"LEFT OUTER JOIN dbo.test2 t2 ON
    t2.c2 BETWEEN t1.c1 AND t1.c1 + "

This confused me even more. I've never seen the equal sign -- replaced with BETWEEN or IN -- and used in this format when JOINING tables/columns. Sorry but my brain doesn't seem to compute.

A basic example of IN clause would be --  select * from table where column in (1-1000).

To convert that into regular english, you are simply telling the query to return all rows where a field is between 1 and 1000.

A basic example of BETWEEN clause would be -- select * from table where date between '1/1/2012' and '9/1/2012'.

To conver that into regular english, you are simply telling the query to return all rows where the date is within a provided date range.

Now, my question is, how do you conver the SELECT query given above into regular english. I realize that your query uses the BETWEEN clause, and my query in the original post uses the IN clause -- but either way, I'm having difficulty converting those statements into regular english.
0
 
hnasrCommented:
IN (1, 2, 5): returns true for 1, 2, and 5 but returns false for 3, 4, and other values

Between 1 AND 5: returns True for 1, 2, 3, 4, and 5, but returns false for other values
0
 
Scott PletcherSenior DBACommented:
>> but either way, I'm having difficulty converting those statements into regular english. <<

Quite true ... it does look odd :-).  But SQL can still process it just fine.

>>
LEFT OUTER JOIN dbo.test2 t2 ON
    t2.c2 BETWEEN t1.c1 AND t1.c1 + 9
<<

Says join from table1 to table2 where the value of the table2's c2 column is between the value of t1's c1 column and the value of t1's c1 column plus 9 -- remember, c1 is numeric, so this is just good old addition.

Table1 has 10 and 20 in it.

For 10, the join will match t2.c2 values from 10 to 19 (10 + 9).
For 20, the join will match t2.c2 values from 20 to 29 (20 + 9).

In short, don't think of joins as one-to-one, because they aren't.  Joins affect ALL matching rows on BOTH sides of the join.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now