CODEWITHSUNDEEP
sql-servert-sql
prospector
prospector

Reputation: 3469

Inner join the value of two combined columns

I need an inner join on two combined columns.

SELECT 
    [FullName], [Email], [Phone], [Goal], [TimeOfLead], [Location], [IPAddress] 
FROM 
    [Leads] 
INNER JOIN
    Clinics on [Clinics.City] + ', ' + [Clinics.State] as Location = Leads.Location 
WHERE
    Leads.Location = Clinics.Location

The Leads Location will always be the same as a city and state in the clinics table.

If I didn't explain this well enough, please let me know and I'll modify the question.

Upvotes: 0

Views: 51

Answers (1)

Shiva
Shiva

Reputation: 20935

This should do it. You don't need the WHERE clause again, since you are making the connection in the INNER JOIN itself.

SELECT DISTINCT
    L.[FullName], L.[Email], L.[Phone], L.[Goal], L.[TimeOfLead], L.[Location], L.[IPAddress] 
FROM 
    [Leads] L
INNER JOIN
    Clinics C on L.Location = C.City + ', ' + C.State

Here's a SQL Fiddle with some sample data and the working query. http://sqlfiddle.com/#!3/f53eb/2

CREATE TABLES, POPULATE SAMPLE DATA

CREATE TABLE Leads
( 
  FullName NVARCHAR(50) NULL,
  Email NVARCHAR(50) NOT NULL, 
  Phone NVARCHAR(20) NULL, 
  Goal NVARCHAR(50) NULL, 
  TimeOfLead NVARCHAR(50) NULL,
  Location NVARCHAR(50) NOT NULL, 
  IPAddress NVARCHAR(50) NULL  
);

CREATE TABLE Clinics
(
  ClinicName NVARCHAR(40) NOT NULL,
  City NVARCHAR(40) NOT NULL,
  State NVARCHAR(2) NOT NULL,
);

INSERT INTO Leads (FullName, Email, Location) VALUES
('John Duh', '[email protected]', 'San Francisco, CA'),
('Jane Dough', '[email protected]', 'Austin, TX'),
('Bill Clinton', '[email protected]', 'Hope, AK');

INSERT INTO Clinics VALUES
('San Fran Clinic','San Francisco','CA'),
('Austin Clinic','Austin','TX'),
('Boise Clinic','Boise','ID'),
('Hope Clinic','Hope','AK');

EXECUTE QUERY. 

SELECT DISTINCT 
    L.[FullName], L.[Email], L.[Phone], L.[Goal], L.[TimeOfLead], L.[Location], L.[IPAddress] 
FROM 
    [Leads] L
INNER JOIN
    Clinics C on L.Location = C.City + ', ' + C.State

RESULTS

FULLNAME    EMAIL   PHONE   GOAL    TIMEOFLEAD  LOCATION    IPADDRESS
John Duh    [email protected]    (null)  (null)  (null)  San Francisco, CA   (null)
Jane Dough  [email protected]  (null)  (null)  (null)  Austin, TX  (null)
Bill Clinton    [email protected]    (null)  (null)  (null)  Hope, AK    (null)

Upvotes: 2

Related Questions