Need to find teacher basic total sum of (1,2,3,4)

128
April 01, 2018, at 9:29 PM

TEACHER_TB

NUMBER  VARCHAR VARCHAR VARCHAR DATE    NUMBER  NUMBER
TEACHER_ID  FIRST NAME  LAST NAME   GENDER  DOB SALARY_ID   SUBJECT_ID
1   Bob Sandreas    M   12-May-60   1   2
2   Michella    Farah   F   24-Dec-80   3   4
3   Montessa    Santos  F   10-Feb-73   3   8
4   John    Reyes   M   28-Feb-88   2   6
5   Lara    Saliba  F   15-Aug-73   2   10
6   Rizvi   Ibrahim M   9-May-65    1   3
7   Raphael Legaspi M   5-Sep-91    2   5
8   Alaa    Badawy  M   27-Jan-85   2   4
9   Jahangir    Alam    M   28-Jul-57   1   9
10  Peter   Parker  M   5-Jun-92    1   6
primary key                 foreign key salary_tb(salary_id)    foreign key subject_tb(subject_id)

SALARY_TB

NUMBER  VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR
SALARY_ID   BASIC   HOUSING TRANSPORTATION  TELEPHONE   PPF TDS
1   Y   Y   Y   Y   Y   Y
2   Y   Y   N   N   Y   Y
3   Y   Y   N   N   N   Y
primary key                     

SALARY_COMPONENTS_TB

NUMBER  VARCHAR VARCHAR VARCHAR         
COMPONENT_ID    SALARY_COMPONENT    COMPONENT_TYPE  AMOUNT          
1   BASIC   EARNING 40000           
2   HOUSING EARNING 20000           
3   TRANSPORTATION  EARNING 10000           
4   TELEPHONE   EARNING 5000            
5   PPF DEDUCTION   5%          
6   TDS DEDUCTION   10%         

1st question

There are 3 tables (TEACHER_TB,SALARY_TB,SALARY_COMPONENTS_TB) where top 2 table are linked but there is no connections with SALARY_COMPONENTS_TB (no table is connected with SALARY_COMPONENTS_TB ), so I need to find out teacher salary SALARY_COMPONENT(sum).

2nd question

Can we link two column without any relation between two table?

Answer 1

"Can we link two column without any relation between two table?"

Of course. We can join anything to anything. It's just that some joins make sense and others don't. The purpose of foreign keys is to enforce a relationship. The presence of foreign keys also serves as an indicator of a meaningful join.

" there is no connections with SALARY_COMPONENTS_TB "

You need to join SALARY_COMPONENTS_TB to SALARY_TB on the basis of columns in SALARY_TB and rows in SALARY_COMPONENTS_TB. This is a pivot. There are various different ways of doing this is MySQL but hard-coding the values is the simplest (if not the most maintainable, but see note below).

Calculating the deductions is messy because the pertinent amount values are stored as non-numeric strings. '10%' maybe human-friendly but it would be better to store a value which is suited to arithmetic, which would be 0.10.

Anyway here is a query for you:

select t.teacher_id
        , t.salary_id
        , (sal.basic+sal.housing+sal.transportation+sal.telephone) as salary_gross
        , (sal.ppf + sal.tds) as salary_deduct
        , (sal.basic+sal.housing+sal.transportation+sal.telephone) 
                 - ( (sal.basic+sal.housing+sal.transportation+sal.telephone) 
                                              * (sal.ppf + sal.tds) ) as salary_net
from teacher_tb as t
join (
    select s.SALARY_ID
              , case when (basic='Y') then scb.amount else 0 end as basic
              , case when (housing='Y') then sch.amount else 0 end as housing
              , case when (transportation='Y') then sct.amount else 0 end as transportation
              , case when (telephone='Y') then scp.amount  else 0 end as telephone
              , case when (ppf='Y') then round(substring(ppf.amount,1, length(ppf.amount)-1)/100,3) else 0 end as ppf
              , case when (tds='Y') then round(substring(tds.amount,1, length(tds.amount)-1)/100,3) else 0 end as tds
    from salary_tb as s
        cross join           salary_components_tb scb
        cross join      salary_components_tb sch
        cross join      salary_components_tb sct
        cross join      salary_components_tb scp
        cross join      salary_components_tb ppf
        cross join      salary_components_tb tds
    where scb.salary_component = 'BASIC'
    and sch.salary_component = 'HOUSING'
    and sct.salary_component = 'TRANSPORTATION'
    and scp.salary_component = 'TELEPHONE'
    and ppf.salary_component = 'PPF'
    and tds.salary_component = 'TDS'
    ) as sal 
    on t.salary_id = sal.salary_id
;

There is a demo on SQL Fiddle here.

Often people don't like being told they're working with a bad data model and they always have a good reason for not changing it. But the purpose of StackOverflow is to help people become better programmers. So, having a good data model matters, not just because relational integrity is crucial but because it makes it easier to write nice clean SQL.

In your case the SALARY_COMPONENTS_TB is worse than pointless. Firstly it forces you to code a pivot table every time you need to use it in a query. That makes the query hard to understand and more time-consuming to maintain. But laso there is no data integrity. You can put anything in that table or delete anything; there's no guarantee you'll have a row for (say) salary_component = 'HOUSING', no guarantee that amount will contain a predictable value you can use in arithmetic.

So what would be a better data model? Replacing the flag columns on SALARY_TB with numeric columns that store the actual values. Much easier to work with and because they could be (should be) numeric columns you'd have value you could use in arithmetic without convoluted string manipulation and datatype casting. So much better.

Rent Charter Buses Company
READ ALSO
Creating an index in MySQL without creating a temp file on disk

Creating an index in MySQL without creating a temp file on disk

I'm trying to create indexes on large MySQL tables (50-100GB per table)

168
PHP won't update +1 value on mysql

PHP won't update +1 value on mysql

This works fineIt sets Neachitate row into 1

186
500 (Internal Server Error) occured when submitting form [duplicate]

500 (Internal Server Error) occured when submitting form [duplicate]

This question already has an answer here:

139