w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How to combine LINQ querys with different where conditions
Do you mean something like: var querySales = from row in bookings.AsEnumerable() where (row.Field<Int32>("t-account") >= tAccSalesFrom && row.Field<Int32>("t-account") <= tAccSalesTo) where (row.Field<Int32>("t-account") >= tAccLabFrom && row.Field<Int32>("t-account") <= tAccLabTo) where (row.Field<Int32>("t-account") >= tAccMatFrom && row.Field<Int32>("t-account") <= tAccMatTo) group row by new { year = row.Field<DateTime>("Date").Year, month = row.Field<DateTime>("Date").Month } into grp

Categories : C#

How to combine two selects or two querys?
Try this one - SELECT TOP 10 NewsItemTitle , [NewsItemUrl] = MAX([NewsItemUrl]) , [NewsItemPublisher] = MAX([NewsItemPublisher]) , COUNT(1) AS CounterNews FROM dbo.[ACTIVITY] WHERE [UserLocation] = 'United States' GROUP BY NewsItemTitle ORDER BY CounterNews DESC Or this - SELECT TOP 10 NewsItemTitle , [NewsItemUrl] , [NewsItemPublisher] , COUNT(1) AS CounterNews FROM dbo.[ACTIVITY] WHERE [UserLocation] = 'United States' GROUP BY NewsItemTitle , [NewsItemUrl] , [NewsItemPublisher] ORDER BY CounterNews DESC

Categories : SQL

PHP/MySQL Multiple Querys
$query = "SELECT column_one, column_two, column_four FROM tbltable;" $results = mysql_query($query) or die(mysql_error()); $querytwo = "SELECT column_one, column_two, column_five FROM tbltable WHERE column_five = 1989"; $results = mysql_query($querytwo) or die(mysql_error()); $querythree = "SELECT COUNT(column_five) FROM tbltable WHERE column_five = 1989"; $results = mysql_query($querythree) or die(mysql_error()); The problem you are encountering is that you are overwriting your results by using the same variable name. Example: $Var = "test"; echo $Var; // Will output "test" $Var = "Another String"; echo $Var; // Will output "Another String" rather than "test"; So append: $Results_a = ...; $Results_b = ...; $Results_c = ...; So you can work with your variables easily,

Categories : PHP

Joining two mysql querys with mysql
You could do it with join. SELECT a.match_id, a.match_date, a.home_team, a.home_score, a.away_score, a.away_team, a.reting, b.home_per, b.draw_per, b.away_per FROM premier_league2 a LEFT JOIN rating b ON a.rating = b.rating WHERE (home_team = %HOME_TEAM% OR away_team = %HOME_TEAM%) AND postponed !=1 AND league =1 AND match_date < %SURRENT_DATE% AND match_date > %NEW_SEASON% ORDER BY match_date DESC LIMIT 1) Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Categories : PHP

Combine CSS mediaqueries conditions
Given that most computer monitors nowadays will have a wider resolution, you could use max-device-width to distinguish mobiles from desktops: @media screen and (max-device-width: 800px) and (orientation:landscape), screen and (max-width: 480px) Note that the queries are separated by comma which is equivalent to a logical "OR" operator

Categories : HTML

How to combine two conditions to make a plot?
As already mention in the comment, you can use interaction. here I use it twice for 2 aes. To get better legend sub title, I use scale_color_discrete. ggplot(tdat, aes(x=interaction(X,variable,drop=TRUE,sep='-'), y=value, color=X)) + geom_point() + scale_color_discrete(name='interaction levels')

Categories : R

How can I combine a SELECT and DELETE statement with the same conditions?
So, how can I make my DELETE statement return the usernames in MySql? Unfortunately you can't do that in MySql with one statement. What you can do though is to create a stored procedure that will return you resultset and delete afterwards. DELIMITER $$ CREATE PROCEDURE mcmmo_select_and_delete() BEGIN SELECT u.user FROM mcmmo_skills AS s, mcmmo_users AS u WHERE s.user_id = u.id AND (s.taming+s.mining+s.woodcutting+s.repair+s.unarmed+s.herbalism+ s.excavation+s.archery+s.swords+s.axes+s.acrobatics+s.fishing) = 0; DELETE FROM u, e, h, s, c USING mcmmo_users u JOIN mcmmo_experience e ON (u.id = e.user_id) JOIN mcmmo_huds h ON (u.id = h.user_id) JOIN mcmmo_skills s ON (u.id = s.user_id) JOIN mcmmo_cooldowns c ON (u.id = c.user_id) WHERE (s.taming+s.mining+s.w

Categories : Mysql

Is mysql datetime not compatible with strtotime()?
We cannot tell what $time holds exactly, but when you feed an ISO-8601 date to strtotime, it works fine: php > echo strtotime("2013-08-01 12:00:00"); 1375351200

Categories : PHP

Most MySQL-like hibernate-compatible in-memory database?
I don't think there is another in-memory Java database that is more compatible to MySQL than H2. If you have a lot of code that only works with MySQL, then you should probably also use MySQL for testing. Just be aware that it will be difficult in the future to switch to another database. Relying too much on features of one product will result in a "vendor lock in". In case of MySQL at least you have the option to switch to MariaDB, so it's not all that bad.

Categories : Java

Create mysql compatible Date from variables in PHP
The least ambiguous form is probably 2013-08-07 ... but other forms are possible. Consult the MySQL manual.

Categories : PHP

Sort SQL output by the order of added WHERE conditions
You technically can't, but if the query isn't TOO bad, you could write some code to do this: $sql = " SELECT * FROM `itemtable` WHERE $itemid1 union all SELECT * FROM `itemtable` WHERE $itemid2 "; If you put some PHP to join the queries together it will work quite easily. When you run union queries, the data will be returned in the order of the queries themselves.

Categories : PHP

Combine two columns in one table to one output
Isn't this what you are looking for? A space in the middle? $sql = "SELECT *,concat(uitvoeringid, ' ', uitvoeringoms) AS single FROM Uitvoering"; Or simply: echo $uitvoeringsid.' '.$uitvoeringoms;

Categories : PHP

How to write modules that are both nodejs compatible and browser compatible?
I really recommend giving another look at Browserify. Other than that, add a Universal Module Definition (UMDJS) exporter in your files. This will allow you to expose your modules in node, browserify, requirejs/amd, or just thrown into a script tag on any site. Can also use a module transpiler -- write in a ES6 module exporter, and have it convert for use in a browser, or a commonjs/node environment.

Categories : Node Js

Number of OR conditions in SQL/MySQL
From the info in your comments just use a sub query. For example: delete tblCustomers where CustomerId in (select CustomerId from tblOtherTable) UPDATE There are only 2 possible approaches given your situation. Export data from MSSQL. Then import that data into a temporary table in your MySQL server. After that import, run a query like the above one. Write a C# app that pulls all the values you want and runs a set of INSERT/UPDATE queries against your MySQL database. The first option is by far the best because it is fairly simple and will run fast. The second option isn't that great because it means you will be executing 2 queries for every record in your MSSQL server. (One to find out if the record is there, followed by either an INSERT or UPDATE statement on the MySQL server.

Categories : Mysql

Using MySQL IGNORE on one column under certain conditions
So it sounds like you're using a scripting language (php seems to be popular with mysql) to store an array of contacts from gmail? If so, this insert statement will insert the record if the account id doesn't exist in the table already -- this uses an Outer Join with a Null check, but you can also use Not In or Not Exists as well: Insert Into YourTable (Id, AccountId, Email, Phone) Select t.Id, t.AccountId, t.Email, t.Phone From (Select 1 Id, 1 AccountId, 'someemail' Email, 'somephone' Phone) t Left Join YourTable t2 On t.AccountId = t2.AccountId Where t2.AccountId Is Null EDIT: Assuming I'm understanding the comments, then just add to the Outer Join: Insert Into YourTable (Id, AccountId, Email, Phone) Select t.Id, t.AccountId, t.Email, t.Phone From (Select 1 Id, 1 AccountId,

Categories : PHP

How does SQL (eg MySQL) construct multiple conditions?
The most parts that you can provide in a WHERE clause, matching from first to last parts of an composited index, the best performance that you will have. (in general you can write in any order, but without any missing on the begining part) Example: Given this index lastname, firstname, age, and a big table, this is a list of best to worst perfomance querys: where lastname = 'gonzalez' and firstname='juan' and age=70 where lastname = 'gonzalez' where age = 70 As mentioned above, the writing order is in general irrelevant as query optimizers can manage it anyway to match any index. So, the important part is to build good indexes: Tipicaly a primary key over an integer, and some others (comosited or not) over fields matching request like in your question. Just a last word: too

Categories : Mysql

mysql join with multiple conditions (not OR but AND)
why not use this instead of many conditions. WHERE skiresort_theme_value.skiresort_theme_id in (1, 2,4) GROUP BY skiresort.sid HAVING COUNT(DISTINCT skiresort_theme_value.skiresort_theme_id) = 3 ORDER BY skiresort.title_en

Categories : Mysql

Update two tables in MySQL with two different WHERE conditions
UPDATE table_1 JOIN table_2 ON table_1.user_id = table_2.user_id SET table_1.value = case when table_2.user_id = %s AND table_1.user_id =%s then 0 else table_1.value end, table_1.value = case when table_1.rank =1 then 555 else table_1.value end, table_2.total_value = case when table_2.user_id = %s AND table_1.user_id =%s then 0 else table_2.total_value end, table_2.total_value = case when table_1.rank =1 then 1555 else table_2.total_value end WHERE ( table_2.user_id = %s AND table_1.user_id =%s ) OR table_1.rank =1

Categories : Mysql

MySQL nested ANDs and several conditions
As far as I can see it now you need to use an INNER JOIN for each question, so each inner join will look like this: INNER JOIN votes AS q1 ON (users.userID = q1.userID) AND q1.questionID = '1' AND q1.answerID ='1' Repeat this for each question and you can check it.

Categories : Mysql

Two mysql counts with two different where conditions in one query
You can use the aggregate function with a CASE expression: select teacherid, sum(case when sub_group = 0 then 1 else 0 end) lectures, sum(case when sub_group > 0 then 1 else 0 end) tutorials from `ttresponsibility` group by teacherid; This will give you 3 columns, the teacherId and then the total lectured and tutorials in separate columns.

Categories : Mysql

MySql Query with join conditions
First of all, you have a mistake on your first query. Here author.famous = 1 OR (author.famous = 1 AND country.id = 123) you're getting only books that their author is famous AND from 123. Maybe you meant author.famous = 1 OR (author.famous = 0 AND country.id = 123), but there's no use for this also. Two conditions are enough. You can just write: SELECT book.id, author.name, country.name FROM book LEFT JOIN author ON author.id = book.authorid LEFT JOIN country ON country.id = author.countryid WHERE (author.famous = 1 OR country.id = 123) For the second query, try this: SELECT book.id, author.name, country.name FROM book LEFT JOIN author ON author.id = book.authorid LEFT JOIN country ON country.id = author.countryid WHERE (author.id is null OR author.famous = 1 OR country.id = 123)

Categories : Mysql

need help to shorten mysql multiple OR conditions
why dont you try this SELECT manager_id, staff_ids FROM manager_staffs WHERE staff_ids IN (1,3,5,23,12,16,19,32,123,.......) you have no choice how to reduce your ORs only if you create other table and select them like query above. structure your table is best to get performance when retrieving data. your table should look like that manager_staff manager_id , staff_id 1 1 1 3 1 5 1 23 ..........

Categories : Mysql

Merge mysql rows on conditions
In MySQL, the MAX() function will discard null values. So you could do something like: SELECT id_rel, id_art, MAX(id_fam), MAX(id_cat), MAX(id_scat), MAX(id_marca), MAX(id_model), MAX(id_year) FROM table_name GROUP BY id_art; This assumes, of course, that the values will only ever be 1 or null. If you start introducing other values, it's [potentially] a little harder.

Categories : Mysql

INSERT mySQL record if conditions are met
Try (sort of a metalanguage for table names): UPDATE score_table SET score = new_score WHERE score > your_current_highscore AND score in (SELECT score FROM table_with_ips WHERE ip = your_ip) AND user_id = your_user_id If score > your_current_highscore is false, then just no entries will be updated. Since you have two parameters (time and percentage) and first one is dominant, you can check score like this: --- score > your_current_hightscore +++ time > your_current_time OR +++ (time = your_current_time AND percentage > your_current_percentage)

Categories : PHP

Where is better to put 'on' conditions in multiple joins? (mysql)
For simple uses MySQL will almost inevitably execute them in the same manner, so it is a manner of preference and readability (which is a great subject of debate). However with more complex queries, particularly aggregate queries with OUTER JOINs that have the potential to become disk and io bound - there may be performance and unseen implications in not using a WHERE clause with OUTER JOIN queries. The difference between a query that runs for 8 minutes, or .8 seconds may ultimately depend on the WHERE clause, particularly as it relates to indexes (How MySQL uses Indexes): The WHERE clause is a core part of providing the query optimizer the information it needs to do it's job and tell the engine how to execute the query in the most efficient way. From How MySQL Optimizes Queries using W

Categories : Mysql

mysql trigger error with 2 conditions
UPDATED: What you need is INSERT INTO ... ON DUPLICATE KEY. CREATE TRIGGER `SystemEventsR_AINS` AFTER INSERT ON SystemEventsR FOR EACH ROW INSERT INTO SystemEventsRcounter (simpledate, syslogtag, records) VALUES (NEW.simpledate, NEW.syslogtag, 1) ON DUPLICATE KEY UPDATE records = records + 1; In order for it to work you need to create a unique composite index on (simpledate, syslogtag) CREATE UNIQUE INDEX idx_u_simpledate_syslogtag ON SystemEventsRcounter (simpledate, syslogtag); Here is SQLFiddle demo. If you wanted it your way then it might look like DELIMITER $$ CREATE TRIGGER `SystemEventsR_AINS` AFTER INSERT ON SystemEventsR FOR EACH ROW BEGIN IF ( SELECT COUNT(*) simpledate FROM SystemEventsRcounter WHERE syslogtag =

Categories : Mysql

mysql subquery multiple conditions
If i understood your requirements correctly you can try this: SELECT route_id FROM bus_stops WHERE stop_name IN ('stop1','stop2') GROUP BY route_id HAVING COUNT(*) = 2 UPDATE: and to get from and to in the order you want: SELECT tmp.route_id, GROUP_CONCAT(IF(tmp.smin = bs.sl_no AND bs.stop_name = 'stop1','from stop1','') SEPARATOR '') as bfrom, GROUP_CONCAT(IF(tmp.smax = bs.sl_no AND bs.stop_name = 'stop2','to stop2','') SEPARATOR '') as bto FROM ( SELECT route_id, MIN(sl_no) as smin, MAX(sl_no) as smax FROM bus_stops WHERE stop_name IN ('stop1','stop2') GROUP BY route_id HAVING COUNT(*) = 2 ) as tmp INNER JOIN bus_stops bs ON bs.route_id = tmp.route_id GROUP BY tmp.r

Categories : Mysql

Many conditions in MySQL query cause crash?
You have an extra AND on the last line of your WHERE clause. Change this line: orders.worker_id = workers.id AND To this: orders.worker_id = workers.id

Categories : Mysql

adding further conditions to mysql loop
what's going wrong now? I see you need to add an equals sign here: if($results['structure'] == "National") { note: double equals for php conditional It also looks like you have an "else" coming out of a "while", that won't work. And you did this "while" loop twice, I'd combine them into a single "While": while($results = mysql_fetch_array($raw_results)){

Categories : PHP

MySQL Conditions from Multiple Rows
Yes, you need to do this with a subquery, try with something like this (if you want the 4th row to access the date in 3rd row): SELECT mo.AgentID, mo.date, @r AS 'LAG(date)', (case when @r<Date then 'YES' when @r is null then 'IS NULL' else 'NO' end) 'Is Bigger', (@r := Date) AS Date FROM ( SELECT m.* FROM ( SELECT @_date = NULL ) variable, data m ORDER BY AgentID ) mo WHERE (CASE WHEN @_date IS NULL OR @_date <> date THEN @r := NULL ELSE NULL END IS NULL) AND (@_date := date) IS NOT NULL You can see a working demo here Or you can try this query if you want that 3rd row has access to date in 4th row SELECT AgentID,date,LEAD_date,concat(Di

Categories : Mysql

MYSQL: Combine AVG and LIKE Statements into One
You should be able to use something like the following: SELECT b.name, AVG(m.rating) AS avgRating FROM beers b LEFT JOIN myBeers m ON b.beerID = m.beerID WHERE b.name LIKE '%Hocus%' GROUP BY b.name You do not need another query in your WHERE clause. This will check that the name of the beer is LIKE the string you are passing in. You just need to decide where you want the wildcard % on the LIKE. I have added it to both the start and end of Hocus but you might only need it on the end. Also, since you are using an aggregate function Avg() you need to use a GROUP BY clause. The GROUP BY should contain the other columns that are included in the select list. You will notice that I removed the select * ad replace it withe select b.name. Also if you want to return beers that do no

Categories : Mysql

Combine Google_Org_Chart with Mysql
You can try something like this: <?php // set database connection parameters $databaseName = '<name of database>'; $username = '<user>'; $password = '<password>'; try { $db = new PDO("mysql:dbname=$databaseName", $username, $password); } catch (PDOException $e) { echo $e->getMessage(); exit(); } $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $query = $db->prepare('SELECT id, nama, parent_id FROM tbl_dummy'); $query->execute(); $results = $query->fetchAll(PDO::FETCH_ASSOC); $flag = true; $table = array(); $table['cols'] = array( array('label' => 'ID', 'type' => 'number'), array('label' => 'Name', 'type' => 'string'), array('label' => 'Parent ID', 'type' => 'number') ); $table['rows'] = array();

Categories : PHP

Merging two querys
It seems like this should do it: var userprofilemodel1 = (from u in db.UserProfiles where u.UserId == 1 || u.UserId == 18 orderby u.FirstName, u.LastName select new UserListViewModel { UserId = u.UserId, UserName = u.UserName, FirstName = u.FirstName, LastName = u.LastName, DocumentCount = u.Documents.Count }).ToPagedList(page, 10); If you want/need to leave it as two separate queries but are trying to concatenate the results, then you need to realize that Concat returns a concatenated list - it does not modify either of the existing lists: var mergedModel = userprofilemodel1.Concat(userprofilemodel2);

Categories : Linq

Combining Querys
You could do a join instead of the 2 queries: var l = (from t in T_list1 join d in T_list2 on t.l2Id equals d.Id where t.Date == DateTime.Today select new { oldDate=t.OldDate, Name=t.name, Email=t.EmailAddress, list2TableId=t.l2Id, CustomerId=t.customerId Date=d.oldDate, CName=d.Name Experience=e.experience, }); I haven't tested the query but it should show you the rough idea. Have a look at JOINs in SQL & Linq to get more information.

Categories : C#

How can I write a MySQL query where two tables have conditions?
Here is one method: select c.id from (select c.*, (select name from contract_locations cl where cl.contract_id = c.id order by arrival_date desc limit 1 ) CurrentLocation from contracts c ) c where CurrentLocation = 'Front Office' This uses a correlated subquery to get the current location. Performance will be much improved by having an index on Contract_Locations(contract_id, arrival_date). Here is another method that may be less obvious. The idea is to see if the most recent date is the most recent date for a given location. This uses a having clause: select contract_id from contract_locations cl group by contract_id having max(arrival_date) = max(case when name = 'Front Office' then ar

Categories : Mysql

mysql query for conditions on related tables
Assuming all your IDs are fixed, the following quick query will work: SELECT HOUSE_ID FROM HOUSE_ROOMS WHERE (ROOM_ID=2 AND SIZE>=450 AND SIZE<=550) OR (ROOM_ID=1 AND SIZE>=350 AND SIZE<=450) GROUP BY HOUSE_ID HAVING COUNT(DISTINCT ROOM_ID)>1

Categories : Mysql

mysql multi table SELECT with conditions
SELECT T2.review_id, T2.rating, T2.content, T3.user_name, T4.tank FROM T2 INNER JOIN T3 ON T2.user_id = T3.user_id INNER JOIN T4 ON T2.user_id = T4.user_id WHERE T2.product_id = $pid

Categories : Mysql

Convert Mysql query to cakephp conditions
You could try to redefine the virtual field before calling Model::find: // Controller code $this->Note->virtualFields = array( 'distance' => "( 3959 * acos( cos( radians($lat) ) * cos( radians( Note.latitude ) ) * cos( radians( Note.longitude ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( Note.latitude ) ) ) )" ); $data = $this->Note->find('all', array('fields' => array('distance'), 'conditions' => array('distance <' => 1))); If it works, you'll probably want to create a model method to do that and you can also do it with pagination also.

Categories : PHP

mysql left outer join with two conditions
A left join would return rows even if no match is found. Since it doesn't make sense to log on someone who is not a user, an inner join would be more appropriate. An inner join is the default type, and you can omit it: select * from OathLogins ol join Users u on u.ID = ol.ID where ol.Provider = 'Sesame Street' and ol.Identifier = 'Bert'

Categories : Mysql

php - MySql select from table with multiple conditions
You have a couple of options SELECT * from cat where main = '0' or main ='1'; or SELECT * from cat where main in ('0','1'); If what you meant by a conflict is in reference to maintaining concurrency between the two queries, this way and wrapping the entire operation in a transaction are the only ways to ensure concurrency. Here is an example of how you can use the queries above to emulate the code you provided but doing it in a single query. $result = $apt->query("SELECT * FROM cat where main='0' or main = '1' order by main"); while($row=$apt->dbarray($result)){ @extract($row); switch ($main){ case 0: // do stuff for when main = 0 break; case 1: // do stuff for when main = 1

Categories : PHP



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.