?
Solved

SQL join question

Posted on 2012-09-13
10
Medium Priority
?
683 Views
Last Modified: 2012-09-14
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
Comment
Question by:pzozulka
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38397160
LEFT OUTER JOIN Contacts C ON (C.ContactNo = I.BillContactNo
   OR C.ContactNo =J.OrdContactNo)
0
 
LVL 8

Author Comment

by:pzozulka
ID: 38397239
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
 
LVL 32

Expert Comment

by:awking00
ID: 38399389
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Author Comment

by:pzozulka
ID: 38399409
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38400362
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38400435
>>
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38400536
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
 
LVL 8

Author Comment

by:pzozulka
ID: 38400593
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38400610
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38400630
>> 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question