Jump to content

Search the Community

Showing results for tags 'left join'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (PhpStorm, VS Code, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Age


Donation Link

Found 5 results

  1. Hello guys. Please do me a favor and help me solve this slow query Tables: CREATE TABLE `products` ( `ProductId` int(11) NOT NULL AUTO_INCREMENT, `CategoryId` int(11) NOT NULL, `BrandId` int(11) DEFAULT NULL, `PCode` varchar(100) COLLATE utf8_persian_ci NOT NULL, `PName` varchar(255) COLLATE utf8_persian_ci NOT NULL, `UnitId` int(11) DEFAULT NULL, `PNameEn` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL, `PShortName` varchar(100) COLLATE utf8_persian_ci DEFAULT NULL, `PDescription` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL, `Weight` int(11) DEFAULT NULL, `PImage` varchar(100) COLLATE utf8_persian_ci NOT NULL DEFAULT 'noimage.png', `PIcon` varchar(100) COLLATE utf8_persian_ci DEFAULT 'noicon.png', `PGallery` varchar(250) COLLATE utf8_persian_ci DEFAULT NULL, `PDetail` text COLLATE utf8_persian_ci, `PTags` text COLLATE utf8_persian_ci, `PSpecial` tinyint(4) NOT NULL DEFAULT '0', `PView` int(11) NOT NULL DEFAULT '0', `Status` tinyint(2) DEFAULT '1' COMMENT '0: inactive. 1: active. 5: deleted.', `BasePrice` int(11) DEFAULT NULL, `PCreateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `PUpdateDate` datetime NOT NULL, `PUrl` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL, `ProviderName` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL, PRIMARY KEY (`ProductId`), UNIQUE KEY `PCode` (`PCode`), KEY `CategoryId` (`CategoryId`), KEY `BrandId` (`BrandId`), KEY `PName` (`PName`) ) ENGINE=MyISAM AUTO_INCREMENT=341028 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `units` ( `UnitId` int(11) NOT NULL AUTO_INCREMENT, `UnitName` varchar(25) COLLATE utf8_persian_ci NOT NULL, PRIMARY KEY (`UnitId`) ) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `products_sub` ( `ProductSubId` int(11) NOT NULL AUTO_INCREMENT, `ProviderId` int(11) NOT NULL, `PCode` varchar(100) COLLATE utf8_persian_ci NOT NULL, `Price` int(11) NOT NULL, `ProviderPrice` int(11) NOT NULL, `PQuantity` int(10) NOT NULL, `PDeliveryTime` time NOT NULL, `PEditTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`ProductSubId`), KEY `ProviderId` (`ProviderId`), KEY `PCode` (`PCode`) ) ENGINE=MyISAM AUTO_INCREMENT=340349 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `provider` ( `ProviderId` int(11) NOT NULL AUTO_INCREMENT, `UserId` int(11) NOT NULL, `ProviderName` varchar(25) COLLATE utf8_persian_ci NOT NULL, `ProviderPhone` varchar(11) COLLATE utf8_persian_ci NOT NULL, `ProviderCell` varchar(11) COLLATE utf8_persian_ci NOT NULL, `ProviderAddress` varchar(250) COLLATE utf8_persian_ci NOT NULL, `ProviderDetail` text COLLATE utf8_persian_ci NOT NULL, `Email` varchar(50) COLLATE utf8_persian_ci NOT NULL, `Status` tinyint(1) NOT NULL, `RequestTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `Permission` varchar(250) COLLATE utf8_persian_ci NOT NULL, PRIMARY KEY (`ProviderId`), UNIQUE KEY `UserId` (`UserId`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `order_products` ( `OrderProductId` int(11) NOT NULL AUTO_INCREMENT, `OrderId` int(11) NOT NULL, `ProviderId` int(11) NOT NULL, `PCode` varchar(50) COLLATE utf8_persian_ci NOT NULL, `PName` varchar(50) COLLATE utf8_persian_ci NOT NULL, `PUnit` varchar(50) COLLATE utf8_persian_ci NOT NULL, `Token` int(11) NOT NULL DEFAULT '999', `ProductPrice` int(11) NOT NULL, `ProductWeight` int(11) NOT NULL, `ProductPurchase` int(11) NOT NULL, PRIMARY KEY (`OrderProductId`), KEY `ProductId` (`PCode`), KEY `OrderId` (`OrderId`), KEY `ProviderId` (`ProviderId`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci CREATE TABLE `brands` ( `BrandId` int(11) NOT NULL AUTO_INCREMENT, `BrandName` varchar(25) NOT NULL, `BrandEn` varchar(25) DEFAULT NULL, PRIMARY KEY (`BrandId`), UNIQUE KEY `BrandName` (`BrandName`) ) ENGINE=MyISAM AUTO_INCREMENT=1988 DEFAULT CHARSET=utf8 My Query: SELECT `products`.`ProductId`, `products`.`PName`, `products`.`Status`, `products`.`PSpecial`, `products`.`PImage`, `products`.`BasePrice`, `products`.`CategoryId`, `units`.`UnitName`, `products_sub`.`ProductSubId`, `products_sub`.`ProviderId`, `products_sub`.`PQuantity`, `products_sub`.`ProviderPrice`, `products_sub`.`Price` AS `customerPrice`, `provider`.`ProviderName`, SUM(`order_products`.`ProductPurchase`) AS `sale` FROM `products` LEFT JOIN `brands` ON `products`.`BrandId`=`brands`.`BrandId` INNER JOIN `units` ON `products`.`UnitId`=`units`.`UnitId` LEFT JOIN `products_sub` ON `products`.`PCode`=`products_sub`.`PCode` AND `products_sub`.`ProductSubId` = ( SELECT `ProductSubId` FROM `products_sub` WHERE `PCode`=`products`.`PCode` AND `PQuantity` > 0 ORDER BY `ProviderPrice` LIMIT 1 ) LEFT JOIN `provider` ON `products_sub`.`ProviderId`=`provider`.`ProviderId` LEFT JOIN `order_products` ON `products`.`PCode`=`order_products`.`PCode` WHERE `products`.`Status`>0 AND `products`.`Status`!=5 AND `products`.`CategoryId` IN (1133,1134,1137,1174,1175,1176,1138,1177,1178,1179,1139,1140,1141,1180,1181,1182,1183,1184,1142,1143,1144,1145,1185,1186,1187,1188,1189,1190,1191,1146,1147,1148,1149,1150,1192,1193,1194,1855,1856,1857,1135,1151,1152,1153,1154,1155,1156,1157,1158,1136,1159,1160,1161,1162,1163,1164,1165,1166,1195,1196,1197,1198,1199,1167,1168,1200,1201,1202,1203,1204,1205,1169,1206,1207,1208,1209,1170,1171,1172,1173,1210,1211,1858,1859,1212,1213,1214,1232,1233,1234,1215,1216,1235,1236,1237,1238,1217,1239,1240,1241,1242,1218,1219,1220,1243,1244,1245,1246,1247,1248,1221,1249,1250,1251,1222,1223,1224,1252,1253,1254,1225,1255,1256,1257,1258,1259,1260,1261,1262,1263,1226,1227,1228,1229,1230,1231,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1463,1464,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1465,1466,1467,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1468,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1469,1470,1471,1472,1473,1474,1508,1509,1286,1287,1290,1299,1395,1396,1300,1301,1397,1398,1399,1302,1303,1304,1305,1400,1401,1402,1306,1307,1308,1309,1403,1404,1310,1405,1406,1407,1408,1311,1312,1313,1314,1315,1409,1410,1291,1316,1317,1318,1319,1320,1292,1321,1322,1323,1324,1288,1293,1325,1411,1412,1326,1327,1413,1414,1328,1329,1330,1331,1415,1416,1417,1418,1332,1419,1420,1421,1422,1333,1334,1335,1423,1424,1336,1425,1426,1427,1428,1429,1430,1431,1337,1338,1432,1433,1434,1339,1340,1341,1342,1435,1436,1437,1343,1438,1439,1440,1441,1294,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1295,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1860,1861,1862,1289,1296,1364,1442,1443,1444,1365,1445,1446,1447,1448,1366,1367,1368,1369,1449,1450,1451,1452,1453,1370,1371,1372,1373,1454,1455,1456,1457,1458,1459,1374,1375,1460,1376,1377,1378,1379,1461,1462,1297,1380,1381,1382,1383,1384,1385,1386,1298,1387,1388,1389,1390,1391,1392,1393,1394,1863,1864,1865,1132) GROUP BY `products`.`PCode` ORDER BY `products`.`Status`, `products_sub`.`PQuantity` > 0 DESC, `products`.`PSpecial` DESC, `customerPrice` ASC, `products`.`BasePrice` ASC LIMIT 36 OFFSET 0 This query is extremely slow When is remove the ORDER BY section, It will run very fast. As i researched, its better to use sub-queries. but didn't figure out how to use them with left join. Any help is appreciated. thanks.
  2. Hi people, I'm having a problem with my SQL query which is not doing what I would like. I have this query: SELECT a. id, a.kataloski_broj, SUM(a.quantity) AS zbroj, entry_type_desc, posting_date, item_category, product_group, b.nabavna_cijena as neto_nabavna, b.neto_VPC, b.preporucena_VPC, c.nabavna_cijena as rabat_nabavna, c.VPC, d.VPC as NAV_VPC, d.PVPC as NAV_PVPC FROM prodaja_zaliha a LEFT JOIN kalkulacija_stavke b ON a.kataloski_broj = b.kataloski_broj LEFT JOIN kalkulacija_stavke_rabat c ON a.kataloski_broj = c.kataloski_broj LEFT JOIN katalog_pribora_item d ON a.kataloski_broj = d.kataloski_broj_NAV WHERE a.entry_type_desc = 'Prodaja' AND YEAR(a.posting_date) = '2015' AND a.kataloski_broj = 'M1200' GROUP BY a.id ASC The problem is that it returns SUM(a.quantity) AS zbroj in 1432 quantity, while there is actually 182 quantity in DB. What am I doing wrong?
  3. Hello all! I'm makeing car booking module and have one problem. Explaining... There is tree joined tables "books", "car" and "cat". When i joining this three table it retrieves ids from table "car", but i want ids from "books" what to do? Can you help? Thanks <?php if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); } $query = " SELECT * FROM books LEFT JOIN car ON books.car_id = car.id LEFT JOIN cat ON books.cat_id = cat.id WHERE books.owner = '$owner'"; $result = mysqli_query($db,$query) or die("Error: ".mysqli_error($db)); $myrow = mysqli_fetch_array($result, MYSQLI_BOTH); if(mysqli_num_rows($result) > 0){ if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); } do { printf ('<tr> <td>'.$myrow["id"].'</td> <td>'.$myrow["model"].'</td> <td>'.$myrow["cat_name"].'</td> <td>'.$myrow["name"].'</td> <td>'.$myrow["surname"].'</td> <td>'.$myrow["country"].'</td> <td>'.$myrow["phone"].'</td> <td>'.$myrow["notes"].'</td> <td>'.$myrow["start_date"].'</td> <td>'.$myrow["end_date"].'</td> <td>'.$myrow["owner"].'</td> <td>'.$myrow["site"].'</td> <td><a href="delete.php?id='.$myrow["id"].'">Delete</a></td> </tr>'); } while ($myrow = mysqli_fetch_array($result)); } else{ echo '<p align="center" style="margin-top:100px;">There are no records...</p>'; } ?>
  4. hi there, i have the following query, which is supposed to return the valid sales lead counts and average the costs and count the leads during the past 30 days. SELECT SC.text_ServiceDescription, R.text_RegionDescription, GROUP_CONCAT(DISTINCT S.text_SupplierName SEPARATOR ', ') AS text_SupplierNames, IFNULL(SW.smallint_SuppliersWanted,0) AS bigint_SuppliersWantedAmount, COUNT(DISTINCT S.bigint_SupplierID) AS bigint_PremiumCustomersCount, ROUND(AVG(T.bigint_TransactionAmount),2) AS bigint_AvgCostPerLead, ROUND(COUNT(DISTINCT LS.bigint_LeadID, LS.smallint_LeadOrdinal),2) AS bigint_AvgNumLeadsGen FROM 4_servicesuppliers SS LEFT JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) LEFT JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID OR SS.bigint_RegionID = R.bigint_ParentRegionID) LEFT JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) LEFT JOIN 11_supplierswanted SW ON (SS.bigint_ServiceID = SW.bigint_ServiceID AND R.bigint_RegionID = SW.bigint_RegionID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) LEFT JOIN 25_serviceleads SL ON (SC.bigint_ServiceID = SL.bigint_ServiceID AND SL.text_LeadAttributes LIKE CONCAT("%",SA1.text_AttributeDescription," = ",SA1.text_AttributeValue,"%")) LEFT JOIN 27_leadssent LS ON (SL.bigint_LeadID = LS.bigint_LeadID) LEFT JOIN 8_transactions T ON (SL.bigint_LeadID = T.bigint_LeadID AND LS.smallint_LeadOrdinal = T.smallint_LeadOrdinal) WHERE S.smallint_SupplierStatus = 0 AND IFNULL(SW.smallint_SuppliersWanted,0) > 0 AND SL.timestamp_LeadCreated >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) AND SL.text_LeadAttributes LIKE CONCAT("%",SA1.text_AttributeDescription," = ",SA1.text_AttributeValue,"%") AND LS.text_Duplicates = "" GROUP BY SS.bigint_ServiceID, SW.bigint_RegionID ORDER BY SS.bigint_ServiceID ASC, R.bigint_RegionID ASC; however - this query takes forever to execute, even longer than the system timeout in phpmyadmin! also via php... in php i have a microtimer attached which times the query above, at 1568.6739211082 seconds. how can i optimize the query above, especially with regard to the 25_serviceleads, 27_leadssent and 8_transactions JOIN's? i have uploaded the sql digest for the tables above to this message, to recreate this issue (removing the email addresses of course - as we do not condone spam ) to: http://performatix.co/Untapped_Potential_Income.zip please respond if you authentically know your mysql (especially the joins!) - you are welcome to assist. i have not been able to do the course myself yet - what i do know - is all as result of a lifelong hobby. sincerely, Pierre "Greywacke" du Toit.
  5. Query: SELECT UMTS_napetude_cell.parent, UMTS_napetude_nodeb.idnap FROM UMTS_napetude_cell LEFT JOIN UMTS_napetude_nodeb ON UMTS_napetude_cell.parent IS NOT LIKE CONCAT('%',UMTS_napetude_nodeb.idnap,'%') WHERE UMTS_napetude_cell.parent IS NOT NULL Error: Thanks in advanced.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.