using join if there is a record

Question

I've a query like this:

select tractions.id,tractions.expired,tractions.succ,tractions.cradit_end_date,
                      tractions.user_id,users.family,tractions.coupon_code,users.username,traction_details.title,
                traction_details_sub.title as sub_title,tractions.coupon_parent,tractions.coupon_id,tractions.coupon_property_id
                from tractions,traction_details,traction_details_sub,users
                  WHERE
                  tractions.app='mobile'
                  AND tractions.succ = 1
                  AND tractions.user_id=$user_id
                  AND tractions.id = traction_details.tr_id
                  AND tractions.id = traction_details_sub.tr_id
                  AND tractions.user_id = users.id

now,some records in tractions have not any tr_id in traction_details_sub table.

how to check if traction_details_sub table have tr_id then join these tables ?

Possible duplicate of http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins?rq=1 — Maritim, Jan 24 '16 at 11:15

Sergey Kalinichenko · Accepted Answer · 2016-01-24T11:23:27.500

Switch to ANSI joins (they have been nearly universally available for a long time) and use an outer join for tables that may not have records:

SELECT
    t.id
,   t.expired
,   t.succ
,   t.cradit_end_date
,   t.user_id
,   u.family
,   t.coupon_code
,   u.username
,   d.title
,   s.title as sub_title
,   t.coupon_parent
,   t.coupon_id
,   t.coupon_property_id
FROM tractions t
LEFT OUTER JOIN traction_details d ON t.id = d.tr_id
LEFT OUTER JOIN traction_details_sub s ON t.id = s.tr_id
JOIN users u ON t.user_id = u.id
WHERE t.app='mobile' AND t.succ = 1 AND t.user_id=$user_id

Note how this syntax moves tables away from the FROM clause into separate joins, with LEFT OUTER JOIN designating tables with optional rows. Also note how the last three conditions of your WHERE clause became ON conditions of the corresponding joins. Finally, note the use of table aliases (i.e. t for tractions, d for traction_details, s for traction_details_sub, and u for users) to shorten the query.

score 0 · Answer 2 · answered Jan 24 '16 at 11:18

0

First select all tr_id from traction_details and after repeat you sql query add condition IN tractions.id (all id available)

answered Jan 24 '16 at 11:18

Dmitriy Kupriyanov

479
8
16

score 0 · Answer 3 · answered Jan 24 '16 at 11:27

Use left join for traction_details_sub table. If there is no matching record, then you will get tds.title as null.

select t.id,t.expired,t.succ,t.cradit_end_date,
t.user_id,u.family,t.coupon_code,u.username,td.title,
tds.title as sub_title,t.coupon_parent,t.coupon_id,t.coupon_property_id
from 
tractions t 
    inner join 
traction_details td
    on t.id = td.tr_id

    left join 
traction_details_sub
    on t.id = tds.tr_id

    inner join 
users u 
    on t.user_id = u.id
WHERE
    t.app='mobile'
    AND t.succ = 1
    AND t.user_id=$user_id

using join if there is a record

3 Answers3