Multipel joins in single query

PHP
1

Hi! I am getting message #1052 - Column ‘userlevel’ in where clause is ambiguous while running following query:

<?PHP
$query=mysql_query(“SELECT mssgs.start_date, mssgs.task, mssgs.status, mssgs.timestamp, mssgs.due_on, mssgs.id, users.fname FROM mssgs
INNER JOIN users ON mssgs.uid=users.uid AND mssgs.userlevel=users.userlevel WHERE status=0 AND userlevel=1 OR userlevel=3”);
if(mysql_num_rows($query) > 0)
{

while($row=mysql_fetch_array($query))
{
if($row!=0)
{
$id=$row[‘id’];
$userlevel=$row[‘userlevel’];
if($userlevel==1)
{
$userlevel=“Manager”;
}
elseif($userlevel==2)
{
$userlevel=“Administrator”;
}
elseif($userlevel==3)
{
$userlevel=“Programmer”;
}
elseif($userlevel==12)
{
$userlevel=“Business Dev. Officer”;
}
elseif($userlevel==31)
{
$userlevel=“Marketing Executive”;
}
echo “<tr align=‘center’>
<td class=‘style3’>”; echo $row[‘fname’]; echo “</td>
<td class=‘style3’>”; echo $userlevel; echo “</td>
<td class=‘style3’>”; echo $row[‘task’]; echo “</td>
<td class=‘style3’>”; echo $row[‘start_date’]; echo “</td>
<td class=‘style3’>”; echo $row[‘due_on’]; echo “</td>
</tr>”;
}
}
echo “</table>”;
}
?>

2

You have a conflict with a duplicate row title, I’m guessing its “id”.
mssgs.id and users.uid

So you should do
SELECT mssgs.start_date, mssgs.task, mssgs.status, mssgs.timestamp, mssgs.due_on, mssgs.id, users.uid AS userID, users.fname FROM mssgs
INNER JOIN users ON mssgs.uid=userID AND mssgs.userlevel=users.userlevel WHERE status=0 AND userlevel=1 OR userlevel=3

Or something along those lines, wherever a conflict is happening

3

It’s a conflict between two column names. As the message says, you are using more than 1 table in your query that contain a column named ‘userlevel’.
Here you’re handling that situation correctly

AND mssgs.userlevel=users.userlevel

Here you are not

userlevel=1 OR userlevel=3
4

in addition, check your WHERE clause to see if it’s working the way you really want

this is what you wrote –

… WHERE status=0 AND userlevel=1 OR userlevel=3

which (disregarding the ambiguous name for a second) is interpreted like this –

… WHERE ( status=0 AND userlevel=1 ) OR userlevel=3

and i’m guessing you really want it to be interpreted like this –

… WHERE status=0 AND ( userlevel=1 OR userlevel=3 )

see the difference?

if it’s the latter you want, you can recode it like this –

… WHERE status=0 AND userlevel IN (1,3)

5

Thanks a lot for your valuable replies.
I think I need to oil my skills for dealing with joins!!
The problem is resolved for me now but the new problem is, if I run the following query:

"SELECT task.task_id, task.task_to, task.task_from, task.timestamp, task.due_on, task.status, task.task, user.fname FROM task 
INNER JOIN user ON task.userlevel=user.userlevel WHERE task.status=1"

I dont get output even when there is data in the database which has status 1.
Simply no data found message is displayed.
:rolleyes:

6

Please try to write your queries in a readable format (you don’t write all your code on a single line, do you? :wink: )


SELECT 
    task.task_id
  , task.task_to 
  , task.task_from
  , task.timestamp
  , task.due_on
  , task.status
  , task.task
  , user.fname 
FROM task 
INNER JOIN user 
ON task.userlevel = user.userlevel 
WHERE task.status = 1

there is data in the database which has status 1.

There is data in the ‘task’ table that has status 1 you wanted to say, right?
But do those rows have a userlevel that is present in the ‘user’ table?

In this query you are using an INNER JOIN, and in the ON clause you are specifying how the two tables must be joined: those rows from ‘task’ and ‘user’ that have the same value in the userleve fields are joined. All others are excluded.
For example:

‘taks’ table

task_id  userlevel
1        10
2        20
3        30
4        10

‘user’ table

fname   userlevel
Tom     10
Jane    30
Sue     40

Joining these two table on userlevel (lik you do in your query), will give the following result:


task.task_id   task.userlevel   user.fname   user.userlevel
1              10               Tom          10
3              30               Jane         30
4              10               Tom          10

Task 2 is excluded, because there’s no row in ‘user’ with userlevel 20.
User Sue is excluded, because there’s no row in ‘task’ with userlevel 40.