Mysql – How to get full details of each person when joining tables

join;MySQL

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.

Best Answer

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.

SELECT
    cc.id,
    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

Related Solutions

MySQL not using indexes when joining against another table

What about this this should remove the need for an "Using temporary; Using filesort" because the data is in the right sort already.

You need to know the trick why MySQL needs "Using temporary; Using filesort" to remove that need.

See second sqlfriddle for an explain about removing the need

SELECT
      *
    FROM base_article

    STRAIGHT_JOIN 
      mag_article
    ON
      (mag_article.basearticle_ptr_id = base_article.id)

    WHERE
      base_article.is_published = 1

    ORDER BY
      base_article.date_published DESC

see http://sqlfiddle.com/#!2/302710/2

Works pretty good i needed this also some time ago for Country / city tables see demo here with example data http://sqlfiddle.com/#!2/b34870/41

Edited you also may want to analyse this answer if base_article.is_published = 1 always returns 1 record like your explain explained an INNER JOIN deliverd table may give better performance like the queries in the answer below

https://stackoverflow.com/questions/18738483/mysql-slow-query-using-filesort/18774937#18774937

Mysql – How to sum the buy order and sell order in one row per each ticker

below should help you :

CREATE TABLE dbo.Trading
        ([Ticker] int, [iOrder] int, [Price] int, [Amount] int)
    ;

INSERT INTO dbo.Trading
    ([Ticker], [iOrder], [Price], [Amount])
VALUES
    (1, 1, 90, 180),
    (1, 1, 89.5, 179),
    (1, -1, 91, 91),
    (2, 1, 5, 20),
    (2, -1, 4, 16),
    (2, -1, 3, 9)
;


SELECT Ticker
    ,sum(CASE 
            WHEN iorder = 1
                THEN Amount
            ELSE 0
            END) AS BuyAmount
    ,sum(CASE 
            WHEN iorder = - 1
                THEN amount
            ELSE 0
            END) AS SellAmount
FROM dbo.Trading
GROUP BY Ticker

Best Answer

Related Solutions

MySQL not using indexes when joining against another table

Mysql – How to sum the buy order and sell order in one row per each ticker

Related Question