Joining Tables not working as expected

[imperium2335]

Hi,

 

I am trying to get my query to fetch all the records that belong to users that belong to a given branch:

 

$result = mysql_query("SELECT enquiries.*, users.userName
					  FROM enquiries, users
					  WHERE enquiries.enqState = 'New'
					  AND users.branch = '$userBranch'
					  AND enquiries.assignedToT = users.userName
					  ORDER BY enquiries.id DESC
					  ")or die(mysql_error()) ;

 

$userBranch is stored as a session variable, and can be something like 'UK' or 'Spain' etc.

 

For UK branch, there are four users, but the query is only outputting items for two of them

 

[shlumph]

How does the users table relate to the enquiries table? Does users.branch = enquiries.id? You probably want to LEFT JOIN on that relation (whatever relation that may be).

[imperium2335]

Hi,

 

enquiries relates to users through assignedToT, which contains the same value if the enquiry belongs to a given user, e.g. WHERE enquiries.assignedToT = Joe.Bloggs or WHERE users.userName = Joe.Bloggs

 

There will be cases where Joe.Bloggs is assignedToT for many enquiries, but there is only one Joe.Bloggs in the user table.

 

[shlumph]

Ok, so maybe you'd like something like:

SELECT enquiries.*, users.userName
FROM enquiries
LEFT JOIN users ON enquiries.assignedToT = users.userName
WHERE enquiries.enqState = 'New'
AND users.branch = '$userBranch'
ORDER BY enquiries.id DESC

 

Note that the join here isn't necessary because you can always get the userName from the enquiries table, as it's the same as assignedToT.

[imperium2335]

Thanks, I'll give it a try tomorrow.