Problem SQL Query

Question

hi I have some problem in sql query. I have 2 table with data like-- Table1 id Project_id Emp_id Task_Name status 282 70 424344 abcd 1 244 60 4234 xyz 2 Table2 id Emp_id Project_id module 1 42 7060 282244 2 43 6070 282 This is my two table but i want a Query which will show me the record id project_id Task_Name status one condition is there. if Table1 id and Project_id is present in the Table2 then status 1 will show ON . if id and Project_id not present then status2 will show OFF in Table2

Suthish Nair · Answer

You need to break up the table design and save each records row by row instead of comma separated.

ranjan sahoo · Answer

the problem is that common attribute. in table1 project_id is 70 and 60 but this common attribute present in table2 project_id but different manner like 7060. so there is problem for comma()

Suthish Nair · Answer

If your thread got resolved please Accept the thread as Answer that helped you.

Suthish Nair · Answer

did u tried Jignesh query?

Jignesh Trivedi · Answer

hi The LEFT JOIN returns all rows from the left table (table1) with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. The RIGHT JOIN returns all rows from the right table (table2) with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. The FULL OUTER JOIN returns all rows from the left table (table1) and from the right table (table2). Please refer http://www.programmerinterview.com/index.php/database-sql/difference-between-a-left-outer-join-and-right-outer-join/ hope this will help you.

ranjan sahoo · Answer

join not possible because of multiple number like in Table2 7060 and in Table1 60

Jignesh Trivedi · Answer

Hi I think in this case Left outer join can help you to resolved your problem. try.. Select t1.id t1.project_id t1.Task_Name t1.status Case when t2.project_id is null then 'OFF' else 'ON end as status1 from table1 t1 left outer join table2 t2 on t1.project_id = t2.project_id and t1.emp_id = t2.emp_id hope this will help you.