[SOLVED] Pass Field Value from one Query to another in Join

[GSMACK]

Any help with this would be greatly appreciated.  I have no control over the tables and cannot change them. 

 

I have a projects table with following revelant fields

 

projects

--------

id

name

deptid  (FK to dept table)

busid

attribute

 

 

dept

----

id

name

 

 

All projects entered are assigned a department and linked via dept.id.  If a new project is replicated from an existing project the existing project's id number is entered in the attribute field of the new project created, creating a link back to the orginal project.  The intent here is to track what projects carried over to other departments.

 

I need to creat a pivot table report now that shows

 

1) All Projects filtered by deptid and busid via a passed variable

 

AND

 

2) ALL departmens and if the project was replicated in that department, return that project ID of the replicated project, if not return NULL. 

 

So, I figure I could join the two tables via two inline SQL Statements

 

Query 1 - Returns Project info filtered by variables

Join

Query 2 - Return ALL department names and use an inline SQL statement to return a project id if attribute field = the projects.id from query. 

 

So basically for every project in query 1, I want to output every location and lookup if a project was created in that location with the attribute = to projects.id of the 1st query.

 

Here's my SQL

 

 

Select
     org_projects.proj_ID,
     org_projects.proj_NAME,
     org_projects.proj_DEPT,
     loc_projects.dept_name,
     loc_projects.dept_proj_ID
FROM
     (SELECT
          projects.id as proj_ID
          projects.name as proj_NAME
          dept.name as proj_DEPT
        FROM
          projects,dept
        WHERE
          projects.deptid = dept.id
          projects.deptid = variable1
          projects.busid = variable2
     ) org_projects

JOIN
     (SELECT
          dept.name as dept_name
          dept.id as dept_id
          (Select projects.id 
              FROM projects 
              WHERE  projects.deptid = dept.id
                     /* attribute is varchar, proj_id is integer */
                    AND CAST(projects.attribute AS CHAR) = CAST(org_projects.proj_ID AS CHAR)
          )AS dept_proj_ID

       FROM
          dept 
     ) loc_projects
ORDER BY
proj_ID

 

The problem is passing the projects.id value from Query 1 to the 2nd queries inline SQL statement. For every Project in the 1st Query I want to ouptut every location in the 2nd query. Returns a SQL Syntax error.  If I replace org_projects.proj_ID with a number I know that's correct it works perfectly.

 

So, anyway that's the big question... is there anyway to pass the the field proj_ID value from Query 1 to Query 2

 

 

[GSMACK]

OK, nevermind, I figured it out.  I was trying to be way too difficult.  Once I broke it down and took a look at it I just needed to combine the two separate queries and remove the Join condition projects.deptid = dept.id, thus returning all depts.

 

SELECT
          projects.id as proj_ID
          projects.name as proj_NAME
          dept.name as proj_DEPT
          (Select projects.id 
              FROM projects 
              WHERE  projects.deptid = dept.id
                     /* attribute is varchar, proj_id is integer */
                    AND CAST(projects.attribute AS CHAR) = CAST( projects.id AS CHAR)
          )AS dept_proj_ID
FROM
         projects,dept
WHERE
          /*projects.deptid = dept.id */
          projects.deptid = variable1
          projects.busid = variable2