sql

performing select count across two tables sql

I have a table relationship which links one person to many relatives. so the tables are 1. Client. 2. Client_relative. I want to display all the rows of the Persons table, while displaying a count of how many relatives each person has. I have this query: SELECT c.clientid, c.fname, c.lname, count(cr.relativeid) as relativecount FROM {c...

MySQL Self-Join

Hi All, I have a table (this cannot be changed) like the following: POST_ID | PARENT_ID | POST_NAME 1 | 0 | Services 4 | 1 | Development 5 | 4 | Magento 2 | 0 | Contact The field, 'parent_id' references post_id to form a self-referential foreign key. Is it possible to write a s...

How to filter mysql duplicated id rows and retrieve the one with latest timestamp?

I have a mysql table with entries like this: id name value date 1 results1 1000000 2010-06-02 01:31:12 2 results2 600000 2010-09-03 05:42:54 1 results1 1200000 2010-09-06 02:14:36 How can I SELECT all and filter multiple rows that have the same id, selecting only the one with latest date? The "date" column d...

SQL Server - How to lock a table until a stored procedure finishes

I want to do this: create procedure A as lock table a -- do some stuff unrelated to a to prepare to update a -- update a unlock table a return table b Is something like that possible? Ultimately I want my SQL server reporting services report to call procedure A, and then only show table a after the procedure has finished....

mysql select minimum that is not a duplicate

I'm trying to write a query that will display the minimum value (lowest score) for each hole eliminating any duplicates. In other words, if the minimum score is 3 on hole_num 1 and there are two or more scores with 3, none of the rows corresponding to hole_num 1 should be returned. However, if there is only one value of 3 on hole_num 1 ...

MySQL Stored Procedure dynamic change name of table.

Hello, I wanna change dynamicaly name of table in sql query. For example I have next stored procedure: CREATE PROCEDURE NewProc(IN tableName varchar(64),IN message text) BEGIN INSERT INTO tableName VALUES (message); END; I need to change tableName in runtime, Can I to do it or not? Thanks. ...

Select Users who have never passed an exam

Suppose that I have a table called exam. Each row contains: examDate userId passed So, we might have the following results: Jan 1 2010, 7, false Jan 2 2010, 8, true Jan 3 2010, 7, true Jan 3 2010, 9, false I want to run a report which includes all users who have NEVER passed the exam. In the above example, only user...

SQL comparing sets, part II: How to join sets of sets

This question reminded me of a couple related problems with whole-set comparison. Given: a collection of sets, and a probe set Three questions: How do you find all sets in collection that match probe, element for element? How do you find all sets in collection that match a collection of probes, without the use of explicit looping ...

MySQL -- Better way to do this query?

This query will be done in a cached autocomplete text box, possibly by thousands of users at the same time. What I have below works, bit I feel there may be a better way to do what I am doing. Any advice? UPDATED -- it can be 'something%': SELECT a.`object_id`, a.`type`, IF( b.`name` IS NOT NULL, b.`name`, IF( c.`name` IS...

How come queries aren't being added to Django's db.connection.queries in tests?

I'm trying to capture the queries which my code submits to the database by examining the contents of django.db.connection.queries. For some reason though, after all the automatically produced setup queries are logged, no further queries are logged from my own code. The following test case demonstrates the behavior. from django.test im...

SQL Server 2008 Field Function - Return 'ok' or 'no' depending on relation between two other fields

I am fairly new to the more advanced database features, such as functions, but I was curious how you would do this in MSSQL (or if it is possible even): If I have a table and the structure is something like this: t_test USR_VALUE MULTIPLIER TOLERANCE VALUE_OK 100 .8 85 OK 100 .9 ...

t-sql query : retrieve latest row approaches

Based on the following table Table_A ID Rev Description ----------------------------------- 1 1 Some text. 1 2 Some text. Adding more. 1 3 Some text. Ading more & more. The above will keep adding a new row when user updates the description. I want to take the row with MAX(Rev) [i.e. the latest description]. To...

invalid sql statement in servlet

In login page i make validation that user is allowed to enter system , i make methode which validate user: boolean isValidUser(Connection con,String userName,String pass ){} it works correctly in desktop Application, but when i tried it in servlet it makes exception that table or view doesn't exist ??? but the table is aleady exist...

MySQL substring performance

Hi, Does anybody know what the performance of using substring in MySQL is like? I have some insert and update calls where I have to truncate all the fields (up to around 15) using substring statements. These will get call pretty regularly so I'm a little worried about performance. ...

SQL Sub Queries

I have the following query: SELECT id, subject, date FROM mail WHERE date > some_date I need to cross reference the id returned by the above query with another table to get the user's username. How can I do it in one query? Right now I am doing this: foreach (id in result) { query = String.Format("SELECT username from USERS where ...

What do column flags mean in MySQL Workbench?

In MySQL Workbench table editor there are 7 column flags available: PK, NN, UQ, BIN, UN, ZF, AI. PK obviously stands for Primary Key. What about others? ...

Find N element in sequence using SQL

Given the following table: Sequence Tag ----- ---- 1 a 2 a 3 a 88 a 100 a 1 b 7 b 88 b 101 b I would like a query that returns the 4th in each sequence of tags (ordered by Tag, Sequence asc): Tag 4thInSequence ----- -------- ...

Import data from excel into multiple tables

Hey all, I'm building an offline C# application that will import data off spread sheets and store them in a SQL Database that I have created (Inside the Project). Through some research I have been able to use some code that can import a static table, into a Database that is exactly the same layout as the columns in the worksheet What I...

Search using LIKE Clause Mysql

I'm tring to convert a Mysql query to using a LIKE clause and I can't make it work. $query = "SELECT id,name FROM `hin` WHERE name = '".$q."'"; What I've tried in some variations. $query = "SELECT id,name FROM `hin` WHERE name LIKE %'".$q."'%"; I need the query to select row only on string match. Intend is to use variable as needle...

Using DAY(), WEEK(), and YEAR() at one query

i using MySQL Query for my task. And I interested using Date and time function. can i use DAY(), WEEK(), and YEAR() at one query? SELECT Object FROM table WHERE DAY(date) BETWEEN 1 AND 7 GROUP BY WEEK(date, 1), YEAR(date) i want do this bcoz i'm worry if sometimes my program have an error because of the date setting and not rec...