Page 1 of 1

Tricky Query

Posted: Wed Jul 06, 2005 2:29 pm
by John Cartwright

Code: Select all

$sql = 'SELECT *, COUNT(DISTINCT(REQUEST.TROUTD)) as `num`
			FROM REQUEST
			INNER JOIN RESPONSE ON REQUEST.INTRN_SEQ_NUM = RESPONSE.INTRN_SEQ_NUM
			WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5 `num` = \'1\'
			ORDER BY ';
Okay first I know your thinking 2 things, the query has more stuff to it, and there is not GROUP BY yet, which is party of my question. My problem is that I only want to select rows where there is no duplication INNER JOIN RESPONSE ON REQUEST.INTRN_SEQ_NUM = RESPONSE.INTRN_SEQ_NUM
WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5 `num` = \'1\'
ORDER BY ';

Okay first I know your thinking 2 things, the query has more stuff to it, and there is not GROUP BY yet, which is party of my question. My problem is that I only want to select rows where there is no duplication of TROUTD, so if the same TROUTD appears in more than 1 row, exclude all rows with that TROUTD. Secondly, for some reason I am still getting 'Unknown column `num`' error, which I don't understand why. Excuse my arrogance but I've been coding for much too long the past coupl]

Okay first I know your thinking 2 things, the query has more stuff to it, and there is not GROUP BY yet, which is party of my question. My problem is that I only want to select rows where there is no duplication of TROUTD, so if the same TROUTD appears in more than 1 row, exclude all rows with thSEQ_NUM = RESPONSE.INTRN_SEQ_NUM
WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5 `num` = \'1\'
ORDER BY ';

Okay first I know your thinking 2 things, the query has more stuff to it, and there is not GROUP BY yet, which is party of my question. My problem is that I only want to select rows where thereEST.TROUTD)) as `num`
FROM REQUEST
INNER JOIN RESPONSE ON REQUEST.INTRN_SEQ_NUM = RESPONSE.INTRN_SEQ_NUM
WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5 `num` = \'1\'
ORDER BY ';

Okay first I know your thinking 2 things, the query has more stuff to it, and there is not GROUP BY yet, which is party of my question. My problem is that I only want to select rows where there is no duplication of TROUTD, so if the same TROUTD appears in more than 1 row, exclude all rows with that$sql = 'SELECT *, COUNT(DISTINCT(REQUEST.TROUTD)) as `num`
FROM REQUEST
INNER JOIN RESPONSE ON REQUEST.INTRN_SEQ_NUM = RESPONSE.INTRN_SEQ_NUM
WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5 `num` = \'1\'
ORDER BY ';

Okay first I know your thinking 2 things, the query has more stuff to it, and there is not GROUP BY yet, which is party of my question. My problem is that I only want to select rows where there is no duplication of TROUTD, so if the same TROUTD appears in more than 1 row, exclude all rows with that TROUTD. Secondly, for some reason I am still getting 'Unknown column `num`' error, which I don't understand why. Excuse my arrogance but I've been coding for much too long the past couple days. My attempt at getting `num` was to only make it find rows that have been counted as 1, meaning there is only 1 instance.

Posted: Wed Jul 06, 2005 2:39 pm
by timvw
Although, it shouldn't work (according to the standard) but MySQL does it....

Code: Select all

SELECT *
FROM REQUEST
INNER JOIN RESPONSE ON REQUEST.INTRN_SEQ_NUM = RESPONSE.INTRN_SEQ_NUM
WHERE .....
GROUP BY TROUTD
HAVING COUNT(*) < 2

Posted: Wed Jul 06, 2005 2:52 pm
by John Cartwright
still returning duplicate rows :(

Code: Select all

$sql = 'SELECT * FROM REQUEST
			INNER JOIN RESPONSE ON REQUEST.INTRN_SEQ_NUM = RESPONSE.INTRN_SEQ_NUM
			WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5
			GROUP BY REQUEST.TROUTD HAVING COUNT(*) < 2			
			ORDER BY ';
JOIN RESPONSE ON REQUEST.INTRN_SEQ_NUM = RESPONSE.INTRN_SEQ_NUM
WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5
GROUP BY REQUEST.TROUTD HAVING COUNT(*) &lt; 2
ORDER BY ';) < 2
ORDER BY ';= RESPONSE.INTRN_SEQ_NUM
WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5
GROUP BY REQUEST.TROUTD HAVING COUNT(*) &lt; 2
ORDER BY '; $sql = 'SELECT * FROM REQUEST
INNER JOIN RESPONSE ON REQUEST.INTRN_SEQ_NUM = RESPONSE.INTRN_SEQ_NUM
WHERE REQUEST.CLIENT_ID = \''.$_SESSION['client_id'].'\' AND RESPONSE.RESULT_CODE = 5
GROUP BY REQUEST.TROUTD HAVING COUNT(*) < 2
ORDER BY ';

Posted: Thu Jul 07, 2005 12:56 pm
by timvw
The following did the trick ;)

Code: Select all

SELECT *
FROM REQUEST AS R1
INNER JOIN RESPONSE USING ( INTRN_SEQ_NUM )
WHERE RESPONSE.RESULT_CODE =5
AND TROUTD NOT IN (
  SELECT TROUTD
  FROM REQUEST AS R2
  WHERE R1.INTRN_SEQ_NUM <> R2.INTRN_SEQ_NUM
)