0

This is working:

SELECT *
FROM ((((defect
JOIN project_testcase ON 
defect.Test_Id=project_testcase.Test_Id)
JOIN testcase ON 
defect.Test_Id=testcase.Test_Id)
JOIN project_pm ON 
project_testcase.Project_Id=project_pm.Project_Id)
JOIN employee ON 
employee.Emp_id=project_pm.Emp_id)

However, this does not work:

SELECT *
FROM ((((defect
JOIN project_testcase ON 
defect.Test_Id=project_testcase.Test_Id)
JOIN testcase ON 
defect.Test_Id=testcase.Test_Id)
JOIN project_pm ON 
project_testcase.Project_Id=project_pm.Project_Id)
JOIN employee ON 
employee.Emp_id=project_pm.Emp_id)
WHERE Project_Id LIKE '%$categ%'

As I have used JOIN tables and joined using Project_Id. Is that the error?

Improve this question
5
  • 4
    First thing to do is format / indent your code properly. You might even find the problem that way. Commented Apr 18, 2016 at 14:36
  • 1
    What does this have to do with PHP? Is it MySQL or SQL Server? Commented Apr 18, 2016 at 14:37
  • 1
    Is the error message or unexpected behaviour produced by the 2nd query classified? Commented Apr 18, 2016 at 14:39
  • I think Matt's answer should help you with any error you might get. It also looks like $categ is a variable, in which case you should concatenate it's value, now you just use its name ('%' + $categ + '%' ). If you can, use prepared statements, which is much better still. Commented Apr 18, 2016 at 14:44
  • 4
    When you post questions on forums it helps considerably if you post the error messages you receive instead of leaving people to guess. Commented Apr 18, 2016 at 14:45

4 Answers 4

Reset to default
1

The first thing I would do to troubleshoot this is to paste it into an SQL formatter. This will help find syntax errors and could help you see a logic error. I would recommend freeformatter.com.

Second you can get rid of the parenthesis.

The Fix

You need to specify what table to get the Project_Id in the WHERE because it is in multiple tables, but for clarity I would always specify what table it comes from.

select
   *   
from
   defect   
join
   project_testcase         
      on   defect.Test_Id=project_testcase.Test_Id       
join
   testcase      
      on   defect.Test_Id=testcase.Test_Id    
join
   project_pm   
      on   project_testcase.Project_Id=project_pm.Project_Id    
join
   employee   
      on   employee.Emp_id=project_pm.Emp_id    
where
   project_testcase.Project_Id like '%$categ%'
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

If my answer helped fix your issue, could you make it as the accepted answer @AbhinavChanana
0

Project_Id in the where clause is ambiguous, you need to define it as you have done on your joins ie WHERE project_pm.Project_Id like '%$categ%'

Improve this answer

Comments

0

In the where clause of the 2nd query you need to tell the rdbms exactly which Project_Id field you want to filter on, since multiple tables contain the Project_Id field, e.g. project_pm.Project_Id.

Improve this answer

Comments

0

Starting by removing all those unnecessary parenthesis, formatting and aliases reveals a fairly simple query underneath. 

select * --This should really be the columns you actually need instead of all of them
from defect d
join project_testcase ptc on d.Test_Id = ptc.Test_Id
join testcase t on d.Test_Id = t.Test_Id
join project_pm p on ptc.Project_Id = p.Project_Id
join employee e on e.Emp_id = p.Emp_id
where p.Project_Id like '%$categ%'

Of course this begs the question of why do you have text like that in a column named Project_Id? That does not look like a project id to me. And since you are using a leading wildcard you have a nonSARGable query so you have eliminated the ability of index seeks on that column.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.