comparing results from two tables?

[wmguk]

Hey,

 

I have a db and several tables, now the main table is Clients. the second table is Variables.

 

In the variables table there are lists of business levels (level) and in the list of clients they use that sector list.

 

what I need to do is check the clients table to see what levels are in use, and then if they are in use dont allow a deletion...

 

my current script is:

 

$sql_level = "SELECT DISTINCT level FROM clients" ;
$result_level = mysql_query($sql_level);

while($row = mysql_fetch_array($result_level)) 
{ 
$level = $row['level'];

$sql_level2 = "SELECT * FROM variables WHERE title = $level" ;
$result_level2 = mysql_query($sql_level2);

while($row = mysql_fetch_array($result_level2)) 
{ 
$level_name = $row['title'];
}
}

 

however if I echo the results I get nothing...

 

I tried this:

 

$sql_level = "SELECT DISTINCT level FROM clients" ;
$result_level = mysql_query($sql_level);

while($row = mysql_fetch_array($result_level)) 
{ 
$level = $row['level'];
}

echo $level;

 

however still nothing.... but there are things in the DB!

Any ideas?

[waynew]

Could you give us a view of what your tables look like?

 

You're wanting to check what sectors are in use?

[wmguk]

Sorry, I'm trying to see what levels are in use.

 

CLIENTS :

client_id  | company | address | postcode | active | date | comments  | cltype | sector | level | nextactivity

1          | TEST      |  Test    |  Test      |    1    |010101|    test      |    1    | industrial  | active  |  010201

 

VARIABLES:

ref | title | sector | level |

_______________________

1  |  active  |  0  |  1  |

 

I just need to check if any of the variables are in use in the clients table, so that if they are my delete script cannot work...

[sasa]

SELECT a.title, IF(COUNT(b.client_id)>0, 'In use', 'Not in use') AS in_use FROM variables AS a
LEFT JOIN clients AS b ON a.title=b.level
GROUP BY a.title