There will be times when you will be asked to write a complex report that involves more than 3 tables, has obscure fields that do not know where they are in the database. You will posed with the problem of finding these fiedlds first and then include this field relevant to a primary field such as customer name for example. The process main include join tables, left, right join, writing subqueries and some other technique. Here will I briefly describe how can you accomplish such complex report in SQL.
Step 1: Define the Report
You are given a report. The report contains some 10 fields form differt tables in the database. All you know is you have to pull this data form the database? Where it is? You do not know.
Step 2: Hunt down the fields
I will not discuss it here. You basically have to find which table and which field stores your required information.
Step 3: Prepare a block diagram of the report
Now that you have hunted down the fields or some fields at least (you may want to start with the two most crucial fields first and include others while you write the report), your are thinking of how this column in one table can be related to this other column in another table. The relationship may not be straight forward. It may required 2 or more hopes to join the two tables. An easy tool that I found for this is Microsoft Excel. Draw each table in Excel, show only the required fields and map it to the next table that makes sense. Map the next table to another table and son on until you reach your desired table. The best way to start would be to draw it on paper first and once it make sense, transfer it to Excel using color coding as below.
Step 4: Write the query, starting from the farthest end
You will need the following steps to write the query. Do not make it perfect in the first attempt, just something that works and pulls the main fields.
- Write Select column_names from the farthest table. Include ID columns only. DO not use * or pull all columns
- Right the first join, do not include extra parameters in select statement. Continue on
- At this point you have joined the first hop between operation table and commission_sch_cal_relate (the two on the farthest left side)
- Join the Commission_Schedule table in the same manner
- In the third hop, you noticed that you can’t join SalesEntityRelate to commission_Schedule because you are join part of commission_schedule based on relate_class_name (you are not joining the full table), plus you are joing SalesEntityRelate which has not been reference before. Essentially you are joing two new tables at the same time which you can do in one step
- The solution is self join Relate_ID and Relate_class_name, write a sub query that create this table and then join it to Commission_Schedule. Now you have a this temp table that you can reference to join to SalesEntityRelate
- Joing this temp table to SalesEntity Relate based on Relate_ID=Sales_Entity_Relate_ID
- You will experience the same situation when joining SalesEntityRelate to Contrct. Again use a subquery and a self join to create temp table that you can use as reference in the next join for your ease
- The next join are straight forward. You do not need any more self joins because the same table is not reference in two different ways.
- I used RIGHT JOIN 3 times in the left most tables. This is because for some contracts, the there was no broker fee, if there is no fee, there was no commission. Since there is no commission, operation value_no is not entered for those records. Since I want to include all brokers, I have include right join because even if the value is not found, the broker name should still be preset in the report
- Add all the columsn that you need and your report will be ready. Debug as necessary
Finally the query that was used in the above example
select C.CONTRACT_ID,C.CONTRACT_NM CustomerName, Contact1.FIRST_NM + ‘ ‘ + Contact1.LAST_NM ContactName,
RC.RV_MEANING_TX [Usage Request],
SE.FIRST_NAME_TX + ‘ ‘ + SE.LAST_NAME_TX Broker,
convert (datetime,convert(varchar(8),C.CONTRACT_START_DT)) StartDate,
convert (datetime,convert(varchar(8),C.CONTRACT_END_DT)) EndDate
from operation Op
RIGHT JOIN COMMISSION_SCHED_CALC_RELATE CR /* 1 and 2 */
ON Op.COMM_CALC_ID = CR.COMM_CALC_ID
RIGHT JOIN COMMISSION_SCHEDULE CS /* 2 and 3 */
ON CS.COMM_SCHEDULE_ID = CR.COMM_SCHEDULE_ID
JOIN ( select COMM_SCHEDULE_ID,RELATE_ID,RELATE_CLASS_NM from COMMISSION_SCHEDULE where RELATE_CLASS_NM=’cSalesEntityRelate’) CS2 /* self join */
ON CS2.COMM_SCHEDULE_ID = CS.COMM_SCHEDULE_ID
RIGHT JOIN SALES_ENTITY_RELATE SER /* 3 and 4 */
ON SER.SALES_ENTITY_RELATE_ID = CS2.RELATE_ID
JOIN (select SALES_ENTITY_RELATE_ID, RELATE_ID, RELATE_CLASS_NM from SALES_ENTITY_RELATE where RELATE_CLASS_NM = ‘cContract’) SER2 /* self join */
ON SER2.SALES_ENTITY_RELATE_ID = SER.SALES_ENTITY_RELATE_ID
JOIN CONTRACT C /* 4 and 5 */
on Ser2.RELATE_ID = C.CONTRACT_ID
JOIN (select RV_CD,RV_MEANING_TX from REFERENCE_CODE where RV_DOMAIN = ‘INVOICE CREATE USAGE REQ’) RC /* read contract code */
ON RC.RV_CD = C.INV_CREATE_USAGE_REQ_CD
JOIN CUSTOMER_PROSPECT CP
ON CP.CUSTPROSPECT_ID = C.CUSTPROSPECT_ID
JOIN (select * from CONTACT where CLASS_NM=’cCustomerProspect’) Contact1
ON Contact1.RELATE_ID = CP.CUSTPROSPECT_ID
JOIN SALES_ENTITY SE
On SE.SALES_ENTITY_ID = SER.SALES_ENTITY_ID