I need to select rows based on ‘DISTINCT’ products.otherproduct1id. Currently, I am getting duplicates in result set.
function getProductGroupsByCategories($categories) {
for($j = 0; $j < count($categories); $j++ ) {
if($j == 0) {
$que = "mallcats.mallcatid ='". $categories$j] ."' ";
}
else {
$que .= " OR mallcats.mallcatid ='". $categories$j] ."'";
}
}
//DISTINCT ON (products.otherproduct1id)
$query = "SELECT mallcats.mallcatid, mallcatlocations.mallcatlocationsid, products.otherproduct1id, products.productname
FROM mallcatlocations INNER JOIN productgroupbycat ON
mallcatlocations.mallcatlocationsid = productgroupbycat.mallcatlocationid INNER JOIN
products ON (productgroupbycat.productid = products.otherproduct1id) AND (productgroupbycat.productid = products.productid)
INNER JOIN mallcats ON mallcatlocations.mallcatid = mallcats.mallcatid
WHERE " . $que;
$res = mssql_query($query,$this->dbCon);
return $res;
}
Re: Serious SQL HELP!
At the expense of sounding totally naive, but don't u have the Distinct statement commented out?
I don't see distinct anywhere else in the script
Re: Serious SQL HELP!
The function was originally used in PHP-PGSQL integration. We now are migrating to SQL server for testing. The commented part was taken out from a working PGSQL- SQL query like this one:
$query = "SELECT DISTINCT ON (products.otherproduct1id) mallcats.mallcatid, mallcatlocations.mallcatlocationsid, products.otherproduct1id, products.productname
FROM mallcatlocations INNER JOIN productgroupbycat ON
mallcatlocations.mallcatlocationsid = productgroupbycat.mallcatlocationid INNER JOIN
products ON (productgroupbycat.productid = products.otherproduct1id) AND (productgroupbycat.productid = products.productid)
INNER JOIN mallcats ON mallcatlocations.mallcatid = mallcats.mallcatid
WHERE " . $que;
The above query works like a charm. MSSQL - SQL lingo does not support it, and this one is becoming a mind bender since i am not too savvy when it comes to complex SQL's
Re: Serious SQL HELP!
SQL Server does indeed support SELECT DISTINCT, in fact that’s a part of the SQL Standard. You should be safe with it.
Oh, I see…you have this “DISTINCT ON” business going…that may not be standard.
Try this alterantive, which should be faster:
http://www.sql-server-performance.com/nb_select_distinct.asp
Bottom line is, this looks like a job for subselects.