I Have Three Columns, I Want to Combine Them All in One Main_Table

i have three columns, i want to combine them all in one main_table

Create tables as follows,

 create table subject(subject_id int primary key,sub_name varchar(20))
 create table teacher(teacher_id int primary key,teacher_name varchar(20))
 create table class(class_id int primary key,class_sec varchar(20))

 create table timetable(t_id int primary key,subject_id int references 
 subject(subject_id)
 ,teacher_id int references teacher(teacher_id),class_id int references 
 class(class_id))

Inserting sample values

 insert into subject values(1,'Tamil')
 insert into teacher values(1,'Pugal')
 insert into class values(1,'12th A')
 insert into timetable values(1,1,1,1)

Using Inner join to connect tables,

 select s.sub_name,t.teacher_name,c.class_sec from timetable t1 
 inner join subject s
 on s.subject_id = t1.subject_id inner join teacher t
 on t.teacher_id = t1.teacher_id inner join class c
 on c.class_id   = t1.class_id

Try this...And revert me if any clarifications needed..

SQL statement - dynamically merging columns from different tables into a single result set

One can also take the UNIONs from Hemal Panya's answer out of the view and into your query...

SELECT record_no, groupid, name, value
FROM main_table INNER JOIN ref_table1 on main_table.record_no = ref_table1.record_no
WHERE main_table.ref_table = 'TABLE1'

UNION ALL

SELECT record_no, groupid, name, value
FROM main_table INNER JOIN ref_table2 on main_table.record_no = ref_table2.record_no
WHERE main_table.ref_table = 'TABLE2'

UNION ALL

etc, etc...

This is probably much faster than tryign to use CASE...

SELECT
  main_table.record_no,
  main_table.groupid,
  CASE main_table.ref_table WHEN 'Table1' THEN Table1.name
                            WHEN 'Table2' THEN Table2.name
                            etc, etc
  END,
  CASE main_table.ref_table WHEN 'Table1' THEN Table1.value
                            WHEN 'Table2' THEN Table2.value
                            etc, etc
  END
FROM
  main_table
LEFT JOIN
  Table1
    ON  main_table.record_no = Table1.record_no
    AND main_table.ref_table = 'Table1'
LEFT JOIN
  Table2
    ON  main_table.record_no = Table2.record_no
    AND main_table.ref_table = 'Table2'
etc, etc

I would, however, recommend against any of these options. It feels as though your schema is designed against the natural behaviour of SQL. You may either need a new structure, or be better suited to a different environment. Without knowing more details, however, it's impossible to advise on a natural relational structure that would meet your needs without needing this kind of 'conditional join'.

On that basis, I'd be intrigued to know why it is that you are hesitent to use a view to unify your disperate data. It appears, in a vacuum, to be th emost sensible option...

Magento JoinLeft

I could be wrong, but I think that because customer/customer is an EAV model rather than a flat model, the table alias it uses is e rather than main_table:

$collection->getSelect()->joinLeft(
    array('salesrep' => 'custom_column'),
    'e.entity_id = salesrep.user_id',
    array('user_id' => 'salesrep.linked_customer_id')
);

You will be able to find out by debugging the select statement, e.g.:

echo (string) $collection->getSelect();

Creating an oracle view to join values from one or more table

In the subquery doing inner join and getting the processed status count and using that value in the outer select

Using case based aggregation to get the results

select A.Component_Type,
       COUNT(A.component_Type) as TOTAL_COMP,
       SUM( case when COMP_STATUS = 'ISSUED' THEN 1 ELSE 0 END ) AS ISSUED_QT,
       MAX(T.COMPLETED_PROCESS_QTY) as COMPLETED_PROCESS_QTY
from tableA A
join ( select A.Component_Type, 
              SUM( case when  PROCESS_STATUS =100 THEN 1 ELSE 0 END) COMPLETED_PROCESS_QTY
       from tableA A
       join TableB B
       on A.name = B.NAME
       group by A.component_Type
       ) T
ON A.Component_Type= T.Component_Type
group by A.component_Type, T.Component_Type
order by A.component_Type desc

Joining multiple times on the same table for different criteria vs. once on aggregated table

As with most performance questions, you need to test the different versions on your data on your system. But, I think the aggregation query you want is:

Select m.ID, value1, value2, value3, value4
from main_table m left join
     (select ID,
             MAX(case X when 'this' then value end) value1,
             MAX(case X when 'that' then value end) value2,
             MAX(case X when 'third' then value end) value3,
             MAX(case X when 'other' then value end) value4
      from other_table
      group by ID
     ) A
     on A.ID = m.ID;

The advantage of aggregation is that adding more values does not much change the performance. Adding new joins can affect performance, so at some point, the aggregation will probably outperform the joins.

---

Related Topics