I have a table foo and a table bar, where each foo might have a bar (and a bar might belong to multiple foos). Now I need to select all foos with a bar. My sql looks like this SELECT * FROM foo f WHERE [...] AND ($param IS NULL OR (SELECT ((COUNT(*))>0) FROM bar b WHER...
I'm creating a paging class and need to pass in two parameters to my MySQL stored procedure for the LIMIT clause. I'm passing them in as INTs and trying something like this SELECT * FROM `MyTable` LIMIT MyFirstParamInt, MySecondParamInt it gives me an error when I try and save the sproc though. Is there a way to do this that I'm jus...
I have some questions on Composite Primary Keys and the cardinality of the columns. I searched the web, but did not find any definitive answer, so I am trying again. The questions are: Context: Large (50M - 500M rows) OLAP Prep tables, not NOSQL, not Columnar. MySQL and DB2 1) Does the order of keys in a PK matter? 2) If the cardinali...
I have a Data table and an Archive table. A webapp pours data into the Data table, and a cron job pulls the data out every hour and archives it in the Archive table (this keeps the Data table small and quick to work with). As I see it there are two psudo-SQL queries to run: INSERT Archive SELECT * FROM Data; & DELETE FROM Data; H...
I have 2 tables. For simplicities sake 'u' has the following columns userid divisionid 'd' has the following: divisionid name I did not create this table, otherwise I would not have this problem. u.DIVISION can be NULL. d.DIVISION cannot. Running the following creates the appropriate data, but it also filters out every single userid t...
I am building a database similar to the one described here where I have products of different type, each type with its own attributes. I report a short version for convenience product_type ============ product_type_id INT product_type_name VARCHAR product ======= product_id INT product_name VARCHAR product_type_id INT -> Foreign key t...
I took over some old php application with MySQL as database. Inside the database, there are tables including content with localized strings (therefore containing special chars) Currently there is a PHP application accessing that database. My job is to create an ASP.net (C# codebehind) application that accesses that strings as well. That...
I am using mysql stored-procedure.My SP return dataset ,how to bind the value into my crystal report. ...
I have a table 'games' for a football league as follows: date home_team_id away_team_id home_score away_score - 1 2 6 21 - 3 1 7 19 I can't figure out how to dynamically generate a list of team ID's ordered by Wins (then...
Hey all I am trying to alter a table with adding a new column setting it as auto increment and with a key. The table already has one key and this one will be an addition. The error I get is the following. error : Multiple primary key defined My code is: alter table user add column id int (11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIR...
I'm trying to update a table to remove all but the first instance of a group. Basically, I have a table with vehicle data related to an insurance policy. Each policy should only have one power_unit. Everything else should be a towed unit. Unfortunately, a bug has been duplicating power units, and now I need to clean this up. There a...
I can't get any MySQL user other than root to work; even anonymous fails. I can log in anonymously or using other user I create, but any command entered under that user causes error 2006: mysql> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 25 Current da...
Sorry if this is a repeat. I looked around some and didn't find what I was after so here goes. SELECT * FROM trees WHERE trees.`title` LIKE '%elm%' This works fine, but not if the tree is named Elm or ELM ect... How do I make SQL case insensitive for this wild-card search? Again, apologies if this is repeated. Oh, using MySql 5 on ...
Why does lean_users show NULL in the ref column? This causes my query to use a temporary table and a filesort later (when I've added more joins)... 14:45:21 (60) > EXPLAIN select * from users u inner join lean_users lu on u.id = lu.user_id; +----+-------------+-------+--------+---------------+---------+---------+----------------+------...
in mysql. thanks. ...
Hello, I have an addition SQL question, hopefully someone here can give me a hand. I have the following mysql table: ID Type Result 1 vinyl blue, red, green 1 leather purple, orange 2 leather yellow and i am seeking the following output: ID Row One Row Two 1 vinyl blue, red, green leather purple, o...
I'm having a problem saving large text files to MySQL database. If the text file size is around 5KB it successfully saves. If file is 148KB then I get this error from Hibernate: org.hibernate.exception.DataException: Could not execute JDBC batch update These is the SQL shows by Hibernate: Hibernate: insert into file_table (ID,FILE) ...
I am working on a restructuring of a legacy database and its associated applications. To keep old and new working in parallel I have created a development database and copied the tables of interest to it, e.g. create database devdb; drop table if exists devdb.tab1; CREATE TABLE devdb.tab1 like working.tab1; insert into devdb.tab1 selec...
So I'm writing a fairly involved query with a half dozen joins, a dependent subquery for [greatest-n-per-group] purposes, grouping, etc. It is syntactically valid, but I've clearly made at least one mistake because it returns nothing. In the past I've debugged valid queries that return nothing by removing joins, executing subqueries on...
hey guys i record number of queries of my website and in page the below script runs , 40 extra queries added to page . how can I change this sql connection into a propper and light one function tree_set($index) { //global $menu; Remove this. $q=mysql_query("select id,name,parent from cats where parent='$inde...