Problem writing sub-query with joins

[alimehmood63]

–Q.29.Write a query that shows 3 columns. The first indicates which dataset (part 1 or 2) the data comes from,
–the second shows company status, and the third is a count of the number of investors.

SELECT (CASE WHEN companies.permalink IS NOT NULL AND investments.company_permalink IS NULL 
			THEN 'company_table'
			
			WHEN  companies.permalink IS NULL AND investments.company_permalink IS NOT NULL 
			THEN 'investment_table' END) AS table_source,
		companies.status,
		COUNT(investments.investor_name)
FROM tutorial.crunchbase_companies companies
FULL JOIN (SELECT *
FROM tutorial.crunchbase_investments_part1 investments_1
UNION ALL
SELECT *
FROM tutorial.crunchbase_investments_part2 investments_2) AS  investments
ON companies.permalink = investments.company_permalink
GROUP BY 1,2
ORDER BY 3 DESC  
			

for the question above i am writing the above query, but i am not getting the desired answer
firstly i am confused that in the question “part 1 and 2” refers to investment 1 and investments 2 table or
company and investment_combined tables ,

please also explain how to write case statement with SELECT CLAUSE

[Arunesh_OneLearn]

part 1 and part2 refers to investments_part1 and investements_part2

[alimehmood63]

okay,but i am not able to access part1 and part 2 separately in CASE STATEMENT as i have appended both the investments table in a subquery in the ‘FROM’ CLAUSE

[Arunesh_OneLearn]

you can first join the investment_part1 with companies. While doing the join add one more column in the select query called data_source with the one single value investment_table1. (now you need to do groupby on the status and data_source after join)

Then just below this syntax you can also do a similar kind of join with investment_part2.

Finally write union all in between these two syntaxes to create final result.

If you have further doubt try to write the code for the 1st step mentioned above and post it below. I will review it here.

[alimehmood63]

i am still not able to do

SELECT (ALTER TABLE crunchbase_investments_part1 ADD data_source VARCHAR),
companies.status,
COUNT(investments_1.investor_name)
FROM tutorial.crunchbase_companies companies
join tutorial.crunchbase_investments_part1 investments_1
ON companies.permalink = investments_1.company_permalink
GROUP BY 1,2

[alimehmood63]

okay i got the answer ,instead of making a subquery now i used ‘UNION ALL’
and followed the advice you gave above by creating a new column , “data_source” , in the SELECT clause using a CASE STATEMENT.

[sjaved]

SELECT CASE WHEN investments_1.company_permalink =companies.permalink THEN 'investments_part_1' END AS data_source,
		companies.status,
		COUNT(investments_1.investor_name)
FROM tutorial.crunchbase_companies companies
join tutorial.crunchbase_investments_part1 investments_1
ON companies.permalink = investments_1.company_permalink
GROUP BY 1,2
   UNION ALL
SELECT CASE WHEN investments_2.company_permalink =companies.permalink THEN 'investments_part_2' END AS data_source,
		companies.status,
		COUNT(investments_2.investor_name)
FROM tutorial.crunchbase_companies companies
join tutorial.crunchbase_investments_part2 investments_2
ON companies.permalink = investments_2.company_permalink
GROUP BY 1,2

Is this correct, Pls explain me?

[Arunesh_OneLearn]

You do not need to use CASE here as only those rows will come up after join where

investments_1.company_permalink =companies.permalink

You can just create a simple column in SELECT statement as below
SELECT 'investment_part1' AS data_source

Other wise the code is correct