SQL Query depending on another query

Question

I'm trying to make a query that returns the result of two different querys.

I have this one :

SELECT idtravel, travel, status FROM travel_db.travel
inner join travel_db.status
on travel.status_idstatus=status.idstatus
order by idtravel desc

to return :

idtravel | travel | status
1          London   Completed
2          NY       Planned
3          Lisbon   Completed

Also have this query :

select sum(value) as total
from
( select a.value
from desp_housing a
where travel_idtravel = 1
union all
select t.value
from desp_transport t
where travel_idtravel = 1
union all
select tu.value
from desp_turism tu
where travel_idtravel = 1
) z

which returns :

Value
600

My point is to merge this two querys to have something like this :

idtravel | travel | status    | Value
1          London   Completed   600
2          NY       Planned     1500
3          Lisbon   Completed   150

Can anyone help?

*Edit 13-05-15: After some ideas, i'm one field short from my final result. Here's where i'm at :

select idtravel, travel, sum(value) as total
from travel_db.travel 
inner join
( select a.travel_idtravel, a.value
from desp_housing a
union all
select t.travel_idtravel, t.value
from desp_transport t
union all
select tu.travel_idtravel, tu.value
from desp_turism tu
union all
SELECT idtravel, travel
FROM travel_db.travel
) z
on travel.idtravel=z.travel_idtravel
group by travel_idtravel

to return :

idtravel | travel | value
1          London   600
2          NY       1500
3          Lisbon   150

It's only missing the field status

*Edit 14-05-15: Guys, finaly it's completed.

Here's the final query :

Select idtravel, travel, sum(value) as total, status
From travel_db.travel
inner join
(select a.travel_idtravel, a.value, a.status_idstatus
from desp_housingo a
union all
select t.travel_idtravel, t.value, t.status_idstatus
from desp_transport t
union all
select tu.travel_idtravel, tu.value, t.status_idstatus
from desp_turism tu
union all
select idtravel, travel, status_idstatus
from travel_db.travel
) z
on travel.idtravel=z.travel_idtravel
inner join travel_db.status
on travel.status_idstatus=status.idstatus
group by travel_idtravel

returning :

 idtravel | travel | value  | status
    1          London   600 | Completed
    2          NY       1500| Planned 
    3          Lisbon   150 | Completed

The thing is, because of how the query is made you have to select fields that you are not going to use (a.status_idstatus, t.status_idstatus, t.status_idstatus) to ensure that you have the same number of fields. Maybe can be optimized but for now is working.

burhan · Accepted Answer · 2015-05-13 00:41:28Z

2

How about this?

SELECT idtravel, travel, status, 
       (SUM(desp_housing.value)+SUM(desp_transport.value)+SUM(desp_turism.value)) as Value
FROM travel_db.travel
INNER JOIN travel_db.status
ON travel.status_idstatus=status.idstatus
INNER JOIN desp_housing
ON desp_housing.travel_idtravel=idtravel
INNER JOIN desp_transport
ON desp_transport.travel_idtravel=idtravel
INNER JOIN desp_turism
ON desp_turism.travel_idtravel=idtravel
GROUP BY idtravel
ORDER BY idtravel desc

or you replace INNER JOIN with LEFT JOIN if your secondary columns may not have a value for all travel ids.

edited May 13, 2015 at 0:41

answered May 12, 2015 at 23:16

burhan

9244 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pedro Barata Over a year ago

Tanks, it solves half the problem... Its returning id | travel | status | value 20 Budapeste Planning null 19 Lisbon Completed 5000 so what's doing is put the value...but its adding the first value for the first ID and first cycle (desp_housing) and adding with all the entrys of the other tables...

PM 77-1 · Accepted Answer · 2015-05-12 23:25:24Z

0

Assuming that idtravel field belongs to travel_db.travel and serves as a foreign key to all desp tables.

SELECT travel.idtravel, travel, status, 
       SUM(desp_housing.value + desp_transport_value + desp_tourism.value) 
FROM travel_db.travel 
INNER JOIN travel_db.status ON travel.status_idstatus = status.idstatus
LEFT JOIN desp_housing ON travel.idtravel = disp_housing.travel_idtravel
LEFT JOIN desp_transport ON travel.idtravel = desp_transport.travel_idtravel
LEFT JOIN desp_turism ON travel.idtravel = desp_turism.travel_idtravel
GROUP BY travel.idtravel
ORDER BY reavel.idtravel DESC

answered May 12, 2015 at 23:25

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

1 Comment

Pedro Barata Over a year ago

tanks, same as burhan it solves half the problem... Its returning id | travel | status | value 20 Budapeste Planning null 19 Lisbon Completed 5000 so what's doing is put the value...but its adding the first value for the first ID and first cycle (desp_housing) and adding with all the entrys of the other tables...

Collectives™ on Stack Overflow

SQL Query depending on another query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related