How to Relate 3 Tables Depending on Event

How to relate 3 tables depending on event

In this can, you cannot set up a foreign key because you have multiple parent. In order to do fast search or to avoid full table scan define an index on column person on table event and join the table using LEFT JOIN. eg,

SELECT  ....,
        COALESCE(b.name, c.name) AS personname
FROM    event a
        LEFT JOIN civil b
            ON a.person = b.civil_id
        LEFT JOIN worker c
            ON a.person = c.worker_ID

Adding INDEX

ALTER TABLE event ADD INDEX (person)

How to join 3 tables properly to get a result if user is attending in the event

It looks like the event creator is getting mixed into the query while you are trying to grab the attendees. It also seems like you don't need to join with the users table, since none of the fields are used.

SELECT events.*, user_events.user_id AS attending FROM events
LEFT JOIN user_events ON user_events.event_id = events.event_id;

Edit:
If you want only the events for a single user

SELECT events.*, user_events.user_id AS attending FROM events
LEFT JOIN user_events ON user_events.event_id = events.event_id
WHERE user_events.user_id = ?1;
AND events.event_id = ?2;

Where ?1 is the id of the user you want to check and ?2 is the id of the event you want to see if the user is attending

Join 3 tables; select from third table row which contains specific string by latest date

If you want the latest "sell back" event across the whole table, then you can join, order by and limit:

select top (1)
    c.customer_id, 
    c.customer_name
    p.problem_id,
    pe.event_reason,
    pe.event_date
from customer c
inner join problem p on p.customer_id = c.customer_id
inner join problem_event pe on pe.problem_id = p.problem_id
where pe.event_reason like '%sell back%'
order by pe.event_date

There are other ways to understand your question. Say you want the latest "sell back" event per customer, then it is a greatest-n-per-group problem. You can use row_number():

select *
from (
    select
        c.customer_id, 
        c.customer_name
        p.problem_id,
        pe.event_reason,
        pe.event_date,
        row_number() over(partition by c.customer_id order by pe.event_date desc) rn
    from customer c
    inner join problem p on p.customer_id = c.customer_id
    inner join problem_event pe on pe.problem_id = p.problem_id
    where pe.event_reason like '%sell back%'
) t
where rn = 1
order by c.customer_id

The exercise of returning the latest "sell back" event per problem, which is the last possible interpretation of your question, is left to the reader!

Sql Query or Join for 3 tables

SELECT        [User Table].User_id, [Events Table].*
FROM            [Commit Table] INNER JOIN
                         [User Table] ON [Commit Table].User_id = [User Table].User_id INNER JOIN
                         [Events Table] ON [Commit Table].Event_id = [Events Table].Event_id

Complex IF statement with 3 tables

Use a subselect with conditionals to switch the fighter_id you're looking for to column_a if it's in column_b, that way, it simplifies your operations and joins in the outer query:

SELECT
    (
        CASE
            WHEN a.winner = a.f_a THEN 'Win'
            WHEN a.winner = a.f_b THEN 'Loss'
            WHEN a.winner IS NULL THEN a.method
        END
    ) AS result,
    b.name AS opponent,
    a.method AS method,
    c.event_name AS event,
    c.event_date AS date
FROM
    (
        SELECT 
            IF(fighter_b = $fighter_id, fighter_b, fighter_a) AS f_a,
            IF(fighter_b = $fighter_id, fighter_a, fighter_b) AS f_b,
            winner,
            method,
            event
        FROM 
            fights
        WHERE
            $fighter_id IN (fighter_a, fighter_b)
    ) a
INNER JOIN
    fighters b ON a.f_b = b.fighter_id
INNER JOIN
    events c ON a.event = c.event_id
ORDER BY
    c.event_date DESC

Also, if the winner field is null, then just echo the method field. That way, when you want to add yet another type of method where winner is null to your system, you don't have to keep tacking on more conditional checks to your CASE statement.

joining two tables with a third table

Junction tables are the classic solution for modeling a many-to-many relationship in an RDBMS.

You do not need to join three tables to get cars for event1 - two tables is enough:

select *
from Cars c
join CarEvent e on e.CarId = c.CarId
where e.EventId = 1

If you need the event name, you need to join the event table as well:

select *
from Cars c
join CarEvent ce on ce.CarId = c.CarId
join Event e on e.EventId = ce.EventId
where e.EventName = 'Event1'

mysql join 3 tables related in pairs

Use:

SELECT 
  SUM(CASE WHEN sd.ticket_name IN ('adult', 'child') THEN sd.quantity 
           ELSE 0 END) AS total_visitors,
  SUM(sd.quantity * t.price)  AS total_sales
FROM sales_main sm
JOIN sales_detail sd
  ON sd.main_id = sm.id
JOIN ticket t
  ON t.event_id = sm.event_id
 AND t.ticket_name = sd.ticket_name
WHERE sm.event_id = '555';

Conditional aggregation could also be based on type:

SUM(CASE WHEN t.ticket_type ='human' THEN sd.quantity ELSE 0 END)

Join 3 tables with different column names

This what you probably need

select s.id,
s.shirt_id,
case 
 when s.is_team = 0 then i.member_id 
 else
 t.team_name 
END
as entity
from shirts s
left join Individuals i on i.id = s.entity_id AND s.is_team = 0
left join Teams t on t.id = s.entity_id AND s.is_team = 1

http://www.sqlfiddle.com/#!2/cbb687/5

---

Related Topics