Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Some way to write an INNER JOIN, but joining on either of two columns Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 4:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 102, Visits: 214
Hi,

Each doctor in my doctors table has a main zip code and an alternate zip code.

SELECT D.*
FROM Doctors D
INNER JOIN ZipCodes Z
ON D.MainZip = Z.ZipCode
OR D.AlternateZip = Z.ZipCode

I've been trying, but can't figure out how to do this (the OR part).. thanks.
Post #1378503
Posted Monday, October 29, 2012 4:18 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:10 PM
Points: 222, Visits: 1,027
As 2 LEFT JOINs to the same ZipCodes table.


Alex Suprun
Post #1378505
Posted Monday, October 29, 2012 4:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 102, Visits: 214
I'm confused, a LEFT JOIN returns all the rows from the table, which I don't want..
Post #1378508
Posted Monday, October 29, 2012 4:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 102, Visits: 214
I think this might work:
INNER JOIN ZipCodes Z ON (D.MainZip = Z.Zip OR D.AlternateZip = Z.Zip)
Post #1378511
Posted Monday, October 29, 2012 4:41 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:50 PM
Points: 2,326, Visits: 3,504
What is the problem? Do you have NULLs in the the MainZip when it's not present/used?


SELECT D.* 
FROM Doctors D
INNER JOIN ZipCodes Z ON
(D.MainZip IS NOT NULL AND D.MainZip = Z.ZipCode) OR
(D.MainZip IS NULL AND D.AlternateZip = Z.ZipCode)



SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1378513
Posted Monday, October 29, 2012 4:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 PM
Points: 1,945, Visits: 3,173
The ZIP codes are attributes of the Doctors addresses. There should not be a JOIN at all!

CREATE TABLE Doctors
(doctor_license_nbr CHAR(12) NOT NULL PRIMARY KEY,
doctor_first_name VARCHAR(20) NOT NULL,
doctor_first_name VARCHAR(20) NOT NULL,
main_zip_code CHAR(5) NOT NULL
CHECK (main_zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'),
alternate_zip_code CHAR(5) --- nullable?
CHECK (alternate_zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'),
CHECK (main_zip_code <> alternate_zip_code),
..);

I am guessing at the specs you did not post. There are more validations, but I think you get the point. If you want to a look up against a particular list in a table:

SELECT D.*
FROM Doctors AS D, Z AS Special_ZIP_codes
WHERE D.main_zip_code
IN (SELECT zip_code FROM Special_ZIP_codes)
OR D.alternate_zip_code
IN (SELECT zip_code FROM Special_ZIP_codes)



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1378514
Posted Monday, October 29, 2012 4:44 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 867, Visits: 7,569
It's not clear what you're trying to accomplish. Some sample data and sample expected results would help to clear that up. If the mainzip and alternatezip both match rows in the zipcode table are you saying you just want the row that matches the mainzip returned, otherwise return whichever row matches?



And then again, I might be wrong ...
David Webb
Post #1378516
Posted Monday, October 29, 2012 5:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 102, Visits: 214
Ok thank you all, I will digest this! I appreciate it.
Post #1378524
Posted Thursday, November 1, 2012 12:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 3,428, Visits: 5,381
matt6749 (10/29/2012)
Hi,

Each doctor in my doctors table has a main zip code and an alternate zip code.

SELECT D.*
FROM Doctors D
INNER JOIN ZipCodes Z
ON D.MainZip = Z.ZipCode
OR D.AlternateZip = Z.ZipCode

I've been trying, but can't figure out how to do this (the OR part).. thanks.


Your query can probably be made to work. Try this:

DECLARE @Doctors TABLE (name VARCHAR(10), MainZip VARCHAR(5), AlternateZip VARCHAR(5))

INSERT INTO @Doctors
SELECT 'Dr. Dwain', '12345', NULL
UNION ALL SELECT 'Dr. Jeff', NULL, '12346'
UNION ALL SELECT 'Dr. Chris', '12345', '12347'
UNION ALL SELECT 'Dr. Paul', '22222', '33333'

DECLARE @ZipCodes TABLE (ZipCode VARCHAR(5))

INSERT INTO @ZipCodes
SELECT '12345' UNION ALL SELECT '12346' UNION ALL SELECT '12347'

-- Returns 2 results of Dr. Chris
SELECT D.*
FROM @Doctors D
INNER JOIN @ZipCodes Z
ON D.MainZip = Z.ZipCode
OR D.AlternateZip = Z.ZipCode


If the issue is dups being returned, add DISTINCT to your SELECT.

Otherwise, look at CELKO's method. That should work too.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1379636
Posted Thursday, November 1, 2012 2:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 102, Visits: 214
Thank you Dwain!
Post #1379666
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse