[SQL] How to combine two sql query into one?

I have two queries that generates the results that I want. One query returns 3 columns, and the other query returns 4 columns. However, I don't know how to make these two queries to generate one table result. My first query is: Select dt_facility.facility_code, dt_facility.facility_name, count(dt_result.cas_rn) as 'No_of_result' from dt_facility left join dt_result on dt_facility.facility_id = dt_result.facility_id group by dt_facility.facility_code, dt_facility.facility_name;

This query generate the facility code, the name, and the total number of results in the database.

The second query is:

Select dt_facility.facility_code, dt_facility.facility_name, min(dt_sample.sample_date) as 'sample_start_date', max(dt_sample.sample_date) as 'sample_end_date' from dt_facility left join dt_sample on dt_facility.facility_id = dt_sample.facility_id group by dt_facility.facility_code, dt_facility.facility_name;

The 2nd query shows the facility code, name, the last start date, and the earliest start date.

They both generated the results I want; however, I don't know how to make them be together, so I can see a table with columns as facility code, facility name, Total number of result, min date of sample and max date of sample.

Please help.

I tried to do "Cross join", but it doesn't return the results I want:

select * from (Select dt_facility.facility_code, dt_facility.facility_name, count(*) as 'No_of_result' from dt_facility left join dt_result on dt_facility.facility_id = dt_result.facility_id group by dt_result.facility_id, dt_facility.facility_code, dt_facility.facility_name) A CROSS JOIN
( Select dt_facility.facility_code, dt_facility.facility_name, min(dt_sample.sample_date) as 'sample_start_date', max(dt_sample.sample_date) as 'sample_end_date' from dt_facility left join dt_sample on dt_facility.facility_id = dt_sample.facility_id group by dt_facility.facility_code, dt_facility.facility_name) B

What is wrong with my SQL?

Continue reading...