I know this has to have a simple solution and I am not seeing it.

I have a table, person, which contains all information about each person such as first name, last name, age, address.

I have a second table called consumer_client which contains a row containing person id for each consumer and client like this:

id | consumer_person_id | client_person_id

What I want is to fetch both full names when I join person to client_consumer like this:

id | Joe Mama | Mike Smith

I cannot seem to get that with a simple JOIN syntax like

SELECT * FROM person JOIN client_consumer WHERE person.person_id = cc.person_id

as it always returns just the consumer_person_id and client_person_id along with other information in person. I want it replaced with the full name instead of person_id.


I think you are looking at this the wrong way around. You should be selecting from the client_consumer table and joining to the person table. This way the query is easily read and understood.

    consumer.full_name as consumer_full_name,
    client.full_name as client_full_name
FROM client_consumer cc
JOIN person consumer ON
    consumer.person_id = cc.consumer_person_id
JOIN person client ON
    client.person_id = cc.client_person_id

Here is another way to look at it starting from your example.

First we set up the example:

-- Setup Data
       person_id int IDENTITY(1,1)
      ,first_name varchar(25)
      ,last_name Varchar(25));

CREATE TABLE consumer_client (
       id int IDENTITY(1,1)
      ,consumer_person_id Int
      ,client_person_id Int);

INSERT INTO dbo.person
        (first_name, last_name)
VALUES  ('John', 'Smith')

INSERT INTO dbo.consumer_client
        (consumer_person_id, client_person_id)
VALUES  (1,2)

Now we select the data joining to the Person table a second time for the Client Name:

-- Select Data

SELECT p1.person_id AS id, p1.first_name + ' ' + p1.last_name AS [Consumer Name], 
       p2.first_name + ' ' + p2.last_name AS [Client Name]
FROM person p1
JOIN consumer_client cc ON p1.person_id = cc.consumer_person_id
JOIN person p2 ON p2.person_id = cc.client_person_id


id Consumer Name  Client Name
-- -------------  -----------
 1 John Smith     Bob Jones

You didn't correctly join the two tables. And you also didn't identify what "cc" was.

Try this:

FROM person 
JOIN client_consumer cc ON person.person_id = cc.person_id

You are asking to return only certain results and not the id. I made some modifications and added the pertinent columns. However, you may have an issue as the columns you stated aren't there and there are 2 id columns. We need to know which you want to join on or if you need to join on both. Here is the concatenation that you require:

SELECT  (p.firstname +' '+ p.lastname) AS fullname 
FROM client_consumer cc 
JOIN person ON p.person_id = cc.consumer_person_id 
  • Thanks for the tip on formatting the select part. I will be start using that!
    – netrox
    Feb 15 '17 at 1:42

What you are asking to do is pretty straightforward, and I see some good answers already, but here is the simplest version. You need to join the consumer_client table twice to the person table. So

SELECT p1.*, p2.*
FROM consumer_client cc
LEFT JOIN person p1 ON p1.person_id = cc.consumer_person_id
LEFT JOIN person p2 ON p2.person_id = cc.client_person_id

Although you'll probably want to alias the columns in the select statement, since you'll get duplicate columns with a straight up SELECT *

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.