mysql condition (join) help

[garethhall]

Hi Guys

 

I have 2 tables

Table 1 called 'comp' (Holds company details) and has fields like

compID,

compName,

compPhone,

compAddress

 

Table 2 called compRel (Holds company relational data) has fields

compId,

subCompId

 

 

The sql I need to get all the right company id's is

SELECT 
   r.compId,
   r.subCompId
FROM
  compRel AS r
WHERE
  r.compId = 37
AND 
  r.subCompId = 37

And like I said this is all correct so far and outputs data like so

compId                subCompId

1                              37

37                            41

37                            48

37                            53

37                            66

 

 

So need to join the compName from the comp Table on to the id's but the hard part is that the first line needs to join comp.compName on the compRel.compId and rest need to join comp.compName on compRel.subcompId

 

I can't get this to work

 

My sql looks like this now

SELECT
  r.compId,
  r.subCompId,
  IF(r.subCompId = 37, r.compId, r.subCompId) AS rID,
  comp.compID,
  comp.compName
FROM
  compRel AS r
RIGHT JOIN
  comp
ON
  rID = comp.compID
WHERE
  r.compID = 37
AND
  r.subCompId = 37

 

So now I get a error "Unknown column rID"

 

The thing is if I change ON condition to r.subCompId = comp.compID and I look at my result rID is outputting the correct id's so why is rID not working?

[requinix]

Use another JOIN. Really. You can JOIN the same table in twice. Use one of them for the parent/left side and the other for the child/right side.

SELECT
p.compID AS `parent`,
p.compName AS `parent_name`,
c.compID AS `child`,
c.compName AS `child_name`
FROM compRel
JOIN comp p ON compRel.compId = p.compID
JOIN comp c ON compRel.subCompId = c.compID
WHERE compRel.compId = 37 OR compRel.subCompId = 37

 

The reason yours doesn't work is because the aliasing is one of the last things MySQL does when it processes a SELECT query, and the WHERE clause is one of the first things. It doesn't know what "rID" is yet.

[garethhall]

Thank you for the reply but I really need the compName in only on column.

 

However I did solve the problem using UNION

 

For interest sake I have include the completed statemant

SELECT 
*
FROM 
compRel 
RIGHT JOIN 
comp 
ON 
compRel.subCompId = comp.compID
RIGHT JOIN 
compSettings 
ON 
comp.compID = compSettings.compId
WHERE 
compRel.compId = 37

UNION

SELECT 
*
FROM 
compRel 
RIGHT JOIN 
comp 
ON 
compRel.compId = comp.compID
RIGHT JOIN 
compSettings 
ON 
comp.compID = compSettings.compId
WHERE 
compRel.subCompId = 37
ORDER BY compName ASC