locked
Help in mysql query RRS feed

  • Question

  • User-414050457 posted


    Hello friend


    I have three tables named

    tb_fst_form

    id    district   identification

    1     Amritsar     yes

    2     Amritsar     yes

    3     Bathinda     Yes

    4     Barnala      Yes

    tb_second_form

    id    district   identification

    1     Gurdaspur     yes

    2     Amritsar      yes

    3     Bathinda      Yes

    4     Barnala       Yes


    tb_third_form

    id    district   identification

    1     Tarntaran     yes
     
    2     Amritsar      yes

    3     Bathinda      Yes

    4     Barnala       Yes




    Now i need an mysql query to fetch combine records from three tables districtwise


    Like


    District      fst_table_count      second_table_count      third_table_count


    Tarntaran        0                         0                             1

    Amritsar         2                          1                             1

    Barnala          1                          1                             1

    Bathinda        1                          1                             1

    Gurdaspur      0                          1                             0

    Please help me to create the mysql query

    Thanks

    Bharat Bhushan

    Wednesday, January 15, 2014 12:37 AM

Answers

  • User1401801381 posted

    can you give final one query that is working on your sql server as a new answer.

    Sure, here it is :

    -- this is the creation of temp tables with data to simulate your real tables
    CREATE table #temp1
    (
     id int primary key identity,
     district nvarchar(max)
    )
    
    insert INTO #temp1 VALUES('Amritsar')
    insert INTO #temp1 VALUES('Amritsar')
    insert INTO #temp1 VALUES('Bathinda')
    insert INTO #temp1 VALUES('Barnala')
    
    CREATE table #temp2
    (
     id int primary key identity,
     district nvarchar(max)
    )
    
    insert INTO #temp2 VALUES('Gurdaspur')
    insert INTO #temp2 VALUES('Amritsar')
    insert INTO #temp2 VALUES('Bathinda')
    insert INTO #temp2 VALUES('Barnala')
    
    CREATE table #temp3
    (
     id int primary key identity,
     district nvarchar(max)
    )
    insert INTO #temp3 VALUES('Tarntaran')
    insert INTO #temp3 VALUES('Amritsar')
    insert INTO #temp3 VALUES('Bathinda')
    insert INTO #temp3 VALUES('Barnala')
    
    -- this is the actual query
    
    select
    src.district
    , sum(
    CASE
        WHEN src.tableName = 'table1' then src.number
    	else 0
    	END) as fst_table_count
    ,sum(
    CASE
    		WHEN src.tableName = 'table2' then src.number
    		else 0
    	END) as second_table_count
    ,sum(
    	CASE
    		WHEN src.tableName = 'table3' then src.number
    		else 0
    	END
    	) as third_table_count
    from
    (
    select district, 'table1' as tableName, COUNT(*) as number from #temp1 group BY district  -- you have to replace #temp1 by your actual table
    UNION select district, 'table2'as tableName, COUNT(*) as number from #temp2 group BY district -- you have to replace #temp2 by your actual table
    union select district, 'table3'as tableName, COUNT(*) as number from #temp3 group BY district -- you have to replace #temp3 by your actual table
    ) as src
    GROUP BY src.district
    
    -- this is just to remove the temp tables 
    
    
    drop TABLE #temp1
    drop TABLE #temp2
    drop TABLE #temp3

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 16, 2014 7:22 AM

All replies

  • User1401801381 posted

    here is a solution using PIVOT

    CREATE table #temp1
    (
     id int primary key identity,
     district nvarchar(max)
    )
    
    insert INTO #temp1 VALUES('Amritsar')
    insert INTO #temp1 VALUES('Amritsar')
    insert INTO #temp1 VALUES('Bathinda')
    insert INTO #temp1 VALUES('Barnala')
    
    CREATE table #temp2
    (
     id int primary key identity,
     district nvarchar(max)
    )
    
    insert INTO #temp2 VALUES('Gurdaspur')
    insert INTO #temp2 VALUES('Amritsar')
    insert INTO #temp2 VALUES('Bathinda')
    insert INTO #temp2 VALUES('Barnala')
    
    CREATE table #temp3
    (
     id int primary key identity,
     district nvarchar(max)
    )
    insert INTO #temp3 VALUES('Tarntaran')
    insert INTO #temp3 VALUES('Amritsar')
    insert INTO #temp3 VALUES('Bathinda')
    insert INTO #temp3 VALUES('Barnala')
    
    SELECT district
    , ISNULL(table1,0) as fst_table_count      
    , ISNULL(table2,0)as second_table_count      
    , ISNULL(table3,0) as third_table_count
     from
    (
    select district, 'table1' as tableName, COUNT(*) as number from #temp1 group BY district
    UNION select district, 'table2'as tableName, COUNT(*) as number from #temp2 group BY district
    union select district, 'table3'as tableName, COUNT(*) as number from #temp3 group BY district
    ) src
    PIVOT
    (
    SUM(number)
    FOR tableName IN(table1, table2,table3)
    )as pivotresult
    
    
    
    drop TABLE #temp1
    drop TABLE #temp2
    drop TABLE #temp3
    

    Wednesday, January 15, 2014 7:26 AM
  • User1401801381 posted

    I realize now that you ask for mysql

    here is a solution without a pivot

    (just replace the query with pivot from my previous answer)

    select
    src.district
    , sum(
    CASE 
        WHEN src.tableName = 'table1' then src.number
    	else 0
    	END) as fst_table_count
    ,sum(
    CASE 
    		WHEN src.tableName = 'table2' then src.number
    		else 0
    	END) as second_table_count
    ,sum(
    	CASE 
    		WHEN src.tableName = 'table3' then src.number
    		else 0
    	END
    	) as third_table_count       
    from
    (
    select district, 'table1' as tableName, COUNT(*) as number from #temp1 group BY district
    UNION select district, 'table2'as tableName, COUNT(*) as number from #temp2 group BY district
    union select district, 'table3'as tableName, COUNT(*) as number from #temp3 group BY district
    ) as src
    GROUP BY src.district

    Wednesday, January 15, 2014 7:54 AM
  • User-414050457 posted

    I realize now that you ask for mysql

    here is a solution without a pivot

    (just replace the query with pivot from my previous answer)

    select
    src.district
    , sum(
    CASE
        WHEN src.tableName = 'table1' then src.number
    	else 0
    	END) as fst_table_count
    ,sum(
    CASE
    		WHEN src.tableName = 'table2' then src.number
    		else 0
    	END) as second_table_count
    ,sum(
    	CASE
    		WHEN src.tableName = 'table3' then src.number
    		else 0
    	END
    	) as third_table_count
    from
    (
    select district, 'table1' as tableName, COUNT(*) as number from #temp1 group BY district
    UNION select district, 'table2'as tableName, COUNT(*) as number from #temp2 group BY district
    union select district, 'table3'as tableName, COUNT(*) as number from #temp3 group BY district
    ) as src
    GROUP BY src.district

    Hi sam Thanks for the reply,

    But this query is not working and in your query you are insert the values in the temporary tables using insert query(statically) but in my tables  there are more values so i can't use the insert query to add record in  temp table using one by one insert query. So please help me to solve this issue. I am facing the below error in your query

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION select district, 'table2'as tableName, COUNT(*) as number from #temp2 group

    Thursday, January 16, 2014 12:47 AM
  • User1401801381 posted

    this query is not working and in your query you are insert the values in the temporary tables using insert query(statically) but in my tables  there are more values so i can't use the insert query to add record in  temp table using one by one insert query. S

    yes, I used the temp table for the sample, but it should work the same as long as you have only three tables (replace the temp tables by rour real tables)

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION select district, 'table2'as tableName, COUNT(*) as number from #temp2 group

    I tested my query on Sql Server, so I suppose there is a little difference with the use of 'UNION' for mysql, unfortunately I cannot test on mysql 

    here is a reference page : http://dev.mysql.com/doc/refman/5.0/en/union.html , it says to use parentheses to enclose the select clauses

    Thursday, January 16, 2014 3:07 AM
  • User-414050457 posted

    Hi Sam

             Actually you have two answerd for this thread and i am confusing that how can i combine these queries so now can you give final one query that is working on your sql server as a new answer.

    Thanks

    Bharat Bhushan

    Thursday, January 16, 2014 4:59 AM
  • User1401801381 posted

    can you give final one query that is working on your sql server as a new answer.

    Sure, here it is :

    -- this is the creation of temp tables with data to simulate your real tables
    CREATE table #temp1
    (
     id int primary key identity,
     district nvarchar(max)
    )
    
    insert INTO #temp1 VALUES('Amritsar')
    insert INTO #temp1 VALUES('Amritsar')
    insert INTO #temp1 VALUES('Bathinda')
    insert INTO #temp1 VALUES('Barnala')
    
    CREATE table #temp2
    (
     id int primary key identity,
     district nvarchar(max)
    )
    
    insert INTO #temp2 VALUES('Gurdaspur')
    insert INTO #temp2 VALUES('Amritsar')
    insert INTO #temp2 VALUES('Bathinda')
    insert INTO #temp2 VALUES('Barnala')
    
    CREATE table #temp3
    (
     id int primary key identity,
     district nvarchar(max)
    )
    insert INTO #temp3 VALUES('Tarntaran')
    insert INTO #temp3 VALUES('Amritsar')
    insert INTO #temp3 VALUES('Bathinda')
    insert INTO #temp3 VALUES('Barnala')
    
    -- this is the actual query
    
    select
    src.district
    , sum(
    CASE
        WHEN src.tableName = 'table1' then src.number
    	else 0
    	END) as fst_table_count
    ,sum(
    CASE
    		WHEN src.tableName = 'table2' then src.number
    		else 0
    	END) as second_table_count
    ,sum(
    	CASE
    		WHEN src.tableName = 'table3' then src.number
    		else 0
    	END
    	) as third_table_count
    from
    (
    select district, 'table1' as tableName, COUNT(*) as number from #temp1 group BY district  -- you have to replace #temp1 by your actual table
    UNION select district, 'table2'as tableName, COUNT(*) as number from #temp2 group BY district -- you have to replace #temp2 by your actual table
    union select district, 'table3'as tableName, COUNT(*) as number from #temp3 group BY district -- you have to replace #temp3 by your actual table
    ) as src
    GROUP BY src.district
    
    -- this is just to remove the temp tables 
    
    
    drop TABLE #temp1
    drop TABLE #temp2
    drop TABLE #temp3

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 16, 2014 7:22 AM