differentiating results of sql right join

Question

Hi

I have a below SQL query

SELECT `User`.`username` , Permalink.perma_link_id, Permalink.locale, 
    Permalink.title, DATEDIFF( CURDATE( ) , Permalink.created ) AS dtdiff,
    `TargetSegment`.segment_text, TargetSegment.source_segment_id, 
    TargetSegment.perma_link_id ,TargetSegment.created ,
    TargetSegment.updated,  DATEDIFF( CURDATE( ) , 
    TargetSegment.updated ) AS datediff
FROM `users` AS `User` 
RIGHT JOIN perma_links AS `PermaLink` 
    ON ( `PermaLink`.`username` = `User`.`username` ) 
RIGHT JOIN target_segments AS `TargetSegment` 
    ON ( `TargetSegment`.`username` = `User`.`username` ) 
RIGHT JOIN source_segments AS `SourceSegment` 
    ON ( `SourceSegment`.`source_detail_id` = `PermaLink`.`source_detail_id` ) 
LEFT JOIN source_details AS `SourceDetail` 
    ON ( `SourceSegment`.`source_detail_id` = `SourceDetail`.`id` ) 
WHERE `TargetSegment`.`username` = "xxxx"
AND `TargetSegment`.`segment_text` <> ""
AND `Permalink`.`perma_link_id` = `TargetSegment`.`perma_link_id` 
AND `TargetSegment`.`source_segment_id` = `SourceSegment`.`id` 
AND `Permalink`.`source_detail_id` = `SourceDetail`.`id` 
ORDER BY `TargetSegment`.`updated` DESC 
LIMIT 0 , 10 

This SQL is fetching correct results for me.I want to identify from which table each row if from , to be specific which result is due to PermaLink table and which is from TargetSegment table.

is this achievable ?

Answer 1

The rows don't come from one table or the other. Each row comes from all tables in your query. Corresponding data from each table is joined together to form one long row.

User.username                                                -- from User table

Permalink.perma_link_id                                      -- from Permalink
Permalink.locale                                             -- from Permalink
Permalink.title                                              -- from Permalink
DATEDIFF(CURDATE(), Permalink.created) AS dtdiff             -- from Permalink

TargetSegment.segment_text                                   -- from TargetSegment
TargetSegment.source_segment_id                              -- from TargetSegment
TargetSegment.perma_link_id                                  -- from TargetSegment
TargetSegment.created                                        -- from TargetSegment
TargetSegment.updated                                        -- from TargetSegment
DATEDIFF(CURDATE(), TargetSegment.updated) AS datediff       -- from TargetSegment

If the join condition fails due to a missing key in one of the tables and that table is the right table in a LEFT join or the left table in a RIGHT JOIN then all values from that table will be NULL.

Answer 2

I think you want to use aliases, like this:

    SELECT `User`.`username` , Permalink.perma_link_id AS p_perma_link_id, Permalink.locale AS p_locale, 
    Permalink.title AS p_title, DATEDIFF( CURDATE( ) , Permalink.created ) AS dtdiff,
    `TargetSegment`.segment_text, TargetSegment.source_segment_id AS t_source_segment_id, 
    TargetSegment.perma_link_id AS t_perma_link ,TargetSegment.created AS t_created,
    TargetSegment.updated AS t_updated,  DATEDIFF( CURDATE( ) , 
    TargetSegment.updated ) AS datediff
FROM `users` AS `User` 
RIGHT JOIN perma_links AS `PermaLink` 
    ON ( `PermaLink`.`username` = `User`.`username` ) 
RIGHT JOIN target_segments AS `TargetSegment` 
    ON ( `TargetSegment`.`username` = `User`.`username` ) 
RIGHT JOIN source_segments AS `SourceSegment` 
    ON ( `SourceSegment`.`source_detail_id` = `PermaLink`.`source_detail_id` ) 
LEFT JOIN source_details AS `SourceDetail` 
    ON ( `SourceSegment`.`source_detail_id` = `SourceDetail`.`id` ) 
WHERE `TargetSegment`.`username` = "xxxx"
AND `TargetSegment`.`segment_text` <> ""
AND `Permalink`.`perma_link_id` = `TargetSegment`.`perma_link_id` 
AND `TargetSegment`.`source_segment_id` = `SourceSegment`.`id` 
AND `Permalink`.`source_detail_id` = `SourceDetail`.`id` 
ORDER BY `TargetSegment`.`updated` DESC 
LIMIT 0 , 10 

Than you'll see the permalink-columns prefixed with p_ and the other ones with t_