Integrity constraint violation: 1052 Column xx in where clause is ambiguous

nikmav

Hi,

I'm having a problem with 2 of 3 queries. I have echoed out the queries and errors below.

---

SELECT s.*, CONCAT(floor (100 * datediff (curdate(), dob) / 36525),'y ',floor (MOD (100 * datediff (curdate(), dob), 36525) / 100 / 30),'m') AS age,
l.location, CONCAT_WS(' ',g.first_name, g.last_name) AS guardian, g.mobile AS guardian_mobile, x.status AS status
FROM swimmers s
INNER JOIN locations l
ON l.location_id = s.location_id
INNER JOIN guardians g
ON g.guardian_id = s.guardian_id
INNER JOIN statuses x
ON x.status_id = s.status_id
ORDER BY swimmer_id ASC
LIMIT 0, 10

ERROR: DataTables warning: table id=swimmers_list - An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'first_name' in where clause is ambiguous

---

SELECT s.*, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age, s2.availability, l.location, x.status
FROM staff s
LEFT JOIN ( SELECT s.staff_id, GROUP_CONCAT(day ORDER BY staff_availability_id separator ' ') AS availability
FROM staff s
INNER JOIN staff_availability s2
ON s.staff_id=s2.staff_id
WHERE s2.time_from IS NOT NULL
AND s2.time_to IS NOT NULL
GROUP BY s.staff_id ) As s2
ON s2.staff_id = s.staff_id
INNER JOIN locations l
ON l.location_id = s.location_id
INNER JOIN statuses x
ON x.status_id = s.status_id
ORDER BY staff_id ASC
LIMIT 0, 10

ERROR: DataTables warning: table id=staff_list - An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'staff_id' in where clause is ambiguous

---

SELECT g.*, x.status AS status
FROM guardians g
INNER JOIN statuses x
ON x.status_id = g.status_id
ORDER BY guardian_id ASC
LIMIT 0, 10

NO ERROR

---

The 2 queries that are getting the error are doing so when I use the searchbox to filter the results. So the table loads fine with all the right data and sorts fine but when you type in the search box then it pops up this message and gets stuck on "Processing...".

The strange thing is that those errors are saying the column in the where clause is ambiguous but there is clearly no WHERE clause in the queries.

Also, when I plug this into phpmyadmin on the same server the query returns the results fine so it seems to be a DataTables issue.

Does anyone have any ideas of why this is happening?

Thanks
Nik

allan

Are you using Editor's PHP libraries or or any of the code provided in the DataTables download?

Allan

nikmav

Hi Allan,

I am using the provided server_processing.php and ssp.class.php. The only difference is the $listquery variable which looks like:

$listquery = "SELECT s.*, CONCAT(floor (100 * datediff (curdate(), dob) / 36525),'y ',floor (mod (100 * datediff (curdate(), dob), 36525) / 100 / 30),'m') AS age, l.location, CONCAT_WS(' ',g.first_name, g.last_name) AS guardian, g.mobile AS guardian_mobile, x.status AS status
FROM swimmers s
INNER JOIN locations l
ON l.location_id = s.location_id
INNER JOIN guardians g
ON g.guardian_id = s.guardian_id
INNER JOIN statuses x
ON x.status_id = s.status_id
$where
$order
$limit";

Basically the echoed queries above with the $where $order $limit intact. All 3 are done in this way.

allan

I don't know what the $listquery variable is. I don't see it in any of my scripts: https://github.com/DataTables/DataTablesSrc/tree/master/examples/server_side/scripts .

An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'staff_id' in where clause is ambiguous

Sounds to me you like you just need to add a table qualifier (probably in the ORDER BY). But that a general SQL issue rather than DataTables specific so you'd be better asking in SO or similar.

Allan

tangerine

I suspect that this has something to do with joins. Also, there are different versions of ssp.class.php but I can't remember where to look - sorry.
Try a forum search for sql joins.