SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) / Some way to write an INNER JOIN, but joining on either of two columns / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 02:44:08 GMT20RE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxThank you Dwain!Thu, 01 Nov 2012 02:16:34 GMTmatt6749RE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspx[quote][b]matt6749 (10/29/2012)[/b][hr]Hi,Each doctor in my doctors table has a main zip code and an alternate zip code.SELECT D.* FROM Doctors DINNER JOIN ZipCodes Z ON D.MainZip = Z.ZipCodeOR D.AlternateZip = Z.ZipCodeI've been trying, but can't figure out how to do this (the OR part).. thanks.[/quote]Your query can probably be made to work. Try this:[code="sql"]DECLARE @Doctors TABLE (name VARCHAR(10), MainZip VARCHAR(5), AlternateZip VARCHAR(5))INSERT INTO @DoctorsSELECT 'Dr. Dwain', '12345', NULLUNION 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 @ZipCodesSELECT '12345' UNION ALL SELECT '12346' UNION ALL SELECT '12347'-- Returns 2 results of Dr. ChrisSELECT D.* FROM @Doctors DINNER JOIN @ZipCodes Z ON D.MainZip = Z.ZipCodeOR D.AlternateZip = Z.ZipCode[/code]If the issue is dups being returned, add DISTINCT to your SELECT.Otherwise, look at CELKO's method. That should work too.Thu, 01 Nov 2012 00:04:07 GMTdwain.cRE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxOk thank you all, I will digest this! I appreciate it.Mon, 29 Oct 2012 17:32:20 GMTmatt6749RE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxIt'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?Mon, 29 Oct 2012 16:44:07 GMTDavid Webb-CDSRE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxThe 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) Mon, 29 Oct 2012 16:42:37 GMTCELKORE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxWhat is the problem? Do you have NULLs in the the MainZip when it's not present/used?[code="sql"]SELECT D.* FROM Doctors DINNER JOIN ZipCodes Z ON (D.MainZip IS NOT NULL AND D.MainZip = Z.ZipCode) OR (D.MainZip IS NULL AND D.AlternateZip = Z.ZipCode)[/code]Mon, 29 Oct 2012 16:41:48 GMTScottPletcherRE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxI think this might work:INNER JOIN ZipCodes Z ON (D.MainZip = Z.Zip OR D.AlternateZip = Z.Zip)Mon, 29 Oct 2012 16:40:13 GMTmatt6749RE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxI'm confused, a LEFT JOIN returns all the rows from the table, which I don't want..Mon, 29 Oct 2012 16:26:11 GMTmatt6749RE: Some way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxAs 2 LEFT JOINs to the same ZipCodes table.Mon, 29 Oct 2012 16:18:03 GMTAlexander SuprunSome way to write an INNER JOIN, but joining on either of two columnshttp://www.sqlservercentral.com/Forums/Topic1378503-392-1.aspxHi,Each doctor in my doctors table has a main zip code and an alternate zip code.SELECT D.* FROM Doctors DINNER JOIN ZipCodes Z ON D.MainZip = Z.ZipCodeOR D.AlternateZip = Z.ZipCodeI've been trying, but can't figure out how to do this (the OR part).. thanks.Mon, 29 Oct 2012 16:14:44 GMTmatt6749