Tag: mysql

Best and optimal way to join max value from other table

I want to find the optimal way to run this query, here the details, lets say the following are my tables with their data: — User Table DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; — Entries INSERT INTO `users` […]

select a column value that matches with a value of another column

I have a MySQL table called ‘Employee’. It has seven columns, but only two column values are related to my question. The two column names are FullName and Name. Here are some sample values of the two columns in the table for better understanding. FullName Name ———- MichealPhilips | Philips Louisfarak | louis Waynebruce | […]

Wrong sql query generated by hibernate

I have order and item entity classes in my program. In order class there is a Set of Item. When I insert an order the order details must be inserted to Order table. And items in the Set must be inserted to Items table which has a foreign key to order_id in Order table. Therefore […]

ORDER BY two columns if duplicates

I have a simple query: create table #Test ( ID INT, name VARCHAR(100), num INT ) INSERT INTO #Test VALUES(1,’bob’, 98) INSERT INTO #Test VALUES(2,’bob’, 44) INSERT INTO #Test VALUES(3,’sam’, 60) INSERT INTO #Test VALUES(4,’deacon’, 14) INSERT INTO #Test VALUES(5,’toby’, 99) INSERT INTO #Test VALUES(6,’toby’, 12) SELECT * FROM #Test ORDER BY num DESC, name […]

Hierarchical Query in MySQl vs SQLServer vs Oracle

I am trying to implement the Connect By query in oracle/mysql/sqlserver to understand the difference in working. Trying to understand how it works. So I have a simple table which looks likes: empno ename mno 1 KS null 2 AB 2 3 BC 1 4 TR 3 5 QE 2 6 PL 3 7 LK […]

Is N prefix allowed in MySQL

I am converting views from MSSQL to MySQL. I came across a statement like SELECT CODE, DESCRIPTION FROM PARAMETERMASTER WHERE (CODETYPE = N’CASTTYPE’) Does MySQL support N prefix? Is it necessary to keep “N” prefix in MySQL also?

Error while executing SSIS package to data migration from SQL 2012 server to MySql

I’m trying to migrate data from MS Sql server 2012 to MySql server (5.1) using SSIS packages Here is my data flow task which retrieves data using query from SQL Server and pumps to Mysql (one of its columns contains blob type) and properties Tried with ODBC connection manager and ADO.net(odbc driver) manager for destination. […]

IOS app , searchbox and SQL database

I have a SQL webhosting database with a table described as “Description”. Now the database speaks with my app completely fine but I seem to have a issue regarding multiple keywords in a search. As of right now the search will only find the first word typed in the searchbox inside the table ‘Description’ in […]

Entity Framework – trying to insert into a table with a foreign key, EF attempts to insert into the original table with the primary key

Hi, there’s my Database diagram. I’m trying to insert rows into Order and OrderItem tables. Insertion into Order table goes fine. However, when I’m trying to insert into OrderItem table I get an exception. Cannot insert into Product table, ProductId already exists. Why does this happen? Insert code: db.Orders.Add(newOrder); db.SaveChanges(); newOrder.OrderItems = products.Convert(newOrder); foreach (var […]

using JOIN and subquery in mysql

I posted a question about 2 weeks ago about ‘one to many’ relation between SQL tables. Now I have a bit of a different scenario. Basically, there are two tables – coffee_users and coffee_product_registrations. The latter is connected to coffee_users table with ‘uid’ column. So basically coffee_users.uid = coffee_product_registrations.uid A single user can have multiple […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.