CODEWITHSUNDEEP
mysql
GRY
GRY

Reputation: 724

SQL join 2 tables to 1 table

I am at the task of joining 3 tables: Task, Unit, and Building.

The task table has a column for a unit and a column for a building.
Any single task is assigned to only a building OR a unit, never both. Thus one column in every record is always null. There are 6100 records in the task table.

when I use this JOIN: 

select * from task t
join building b on b.id = t.building_id;

I get 628 rows. This is the correct total of building tasks.

When I use this JOIN

select * from active_task at
inner join unit_template ut on ut.id = at.unit_template_id

I get 5472 rows. This is the correct number of unit tasks. If I add them up 5472+628 =6100 this is the correct # of rows in the task table.

When I run this Query:

select * from task t
inner join unit ut on ut.id = t.unit_id
inner join building bt on bt.id = t.building_id

I get zero rows. I need my query to retrieve 6100 rows. Any help would be appreciated.

basic ER

Upvotes: 2

Views: 13325

Answers (3)

nuqqsa
nuqqsa

Reputation: 4521

If you want all the matches given by both queries why not unifying:

SELECT * from task t JOIN building b ON b.id = t.building_id
UNION
SELECT * from active_task at JOIN unit_template ut ON ut.id = at.unit_template_id

As long as the two task tables have the same number of fields that should be enough (otherwise filter the desired columns in the select statements).

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425823

SELECT  *
FROM    task t
LEFT JOIN
        unit ut
ON      ut.id = t.unit_id
LEFT JOIN
        building bt
ON      bt.id = t.building_id
        AND t.unit_id IS NULL

Upvotes: 2

Kyra
Kyra

Reputation: 5417

Try a left join:

select * from task t
left join unit ut on ut.id = t.unit_id
left join building bt on bt.id = t.building_id

Upvotes: 2

Related Questions