mysql-query

MySQL: Using of coma separated ids in a table column and group_concat

In a table, there is a column that contains ids separated by comas ex: "159,167" I want to do the equivalent of a join between this table and the table that contains those ids. ex: TableA TableB id desc id desc tableAids stock 1 test1 1 abc 1,2,3 1 2 test2 2 def 1,2 0 3 te...

Time diff calculations where date and time are in seperate columns

I've got a query where I'm trying to get the hours in duration (eg 6.5 hours) between two different times. In my database, time and date are held in different fields so I can efficiently query on just a startDate, or endDate as I never query specifically on time. My query looks like this SELECT COUNT(*), IFNULL(SUM(TIMEDIFF(endTime,...

MySQL query does not return any data

Hi, I need to retrieve data from a specific time period. The query works fine until I specify the time period. Is there something wrong with the way I specify time period? I know there are many entries within that time-frame. This query returns empty: SELECT stop_times.stop_id, STR_TO_DATE(stop_times.arrival_time, '%H:%i:%s') as stop...

Query to find all bars that sell three different beers at the same price

Query to find "All bars that sell three different beers at the same price?" My Tables are Sells(bar,beer,price) - bar - foreign Key.. Bars(name,addr) - name primary key. I thought of something like this but that dosent seem to work ... Select A.bar As bar , B.bar as bar From Sells AS A, Sells AS B Where A.bar = B.bar and A.b...

SELECT USING COUNT in mysql

Hi, I have a very large database with about 120 Million records in one table.I have clean up the data in this table first before I divide it into several tables(possibly normalizing it). The columns of this table is as follows: "id(Primary Key), userId, Url, Tag " . This is basically a subset of the dataset from delicious website. As I s...

Merging MySQL row entries into a single row

I've got two tables, one for listings and another representing a list of tags for the listings table. In the listings table the tag ids are stored in a field called tags as 1-2-3-. This has worked out very well for me (regular expressions and joins to separate and display the data), but I now need to pull the titles of those tags into a...

How to create script with Insert statement for the records selected in MySql ??

Hello, I need to create a script file with the Insert statements for the records I select in the mysql table. For Instance, when i do Select * from emp where empid = 5, and say i get 10 records as result. Now I should create a script file with the insert statements of that 10 records to emp table. I am using C# as code behind... How?...

mysql query trouble

Hi , in my database i have phone numbers with country code , which look somthing like 0044-123456 0044-123456 0014-123456 0014-123456 0024-123456 0024-123456 0034-123456 0044-123456 0044-123456 0024-123456 0034-123456 084-123456 084-123456 i want to total up the numbers by country, something like this output 0044 (2) 0024 (2) 0034 ...

give preference to print array values in php

Hi , I have country table, i fetch all values and moved into array , these value i like to populate into combo/dropdown list , here i want to do some magic things, that is , for my site most of the users coming from uk,us,Australia,Romain and few, So i like to populate by my preference , is there any array will do this magic w...

Stored Procedure to create Insert statements in MySql ??

I need a storedprocedure to get the records of a Table and return the value as Insert Statements for the selected records. For Instance, The stored procedure should have three Input parameters... 1- Table Name 2- Column Name 3- Column Value If 1- Table Name = "EMP" 2- Column Name = "EMPID" 3- Column Value = "15" Then the outp...

how to query with child relations to same table and order this correctly

Hi, Take this table: id name sub_id --------------------------- 1 A (null) 2 B (null) 3 A2 1 4 A3 1 The sub_id column is a relation to his own table, to column ID. subid --- 0:1 --- id Now I have the problem to make a correctly SELECT query to show that the child ro...

convert SQL Server StoredPorcedure to MySql

I need to covert the following SP of SQL Server To MySql. I am new to MySql.. Help needed. CREATE PROC InsertGenerator (@tableName varchar(100)) as --Declare a cursor to retrieve column specific information --for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_schema.co...

Mysql count columns

I have a table for image gallery with four columns like: foid | uid | pic1 | pic2 | pic3 | date | ----------------------------------------------- 104 | 5 | 1.jpg | 2.jpg | 3.jpg | 2010-01-01 105 | 14 | 8.jpg | | | 2009-04-08 106 | 48 | x.jpg | y.jpg | | 2010-08-09 Mysql query for the user's galleries lo...

How to select data with a certain minimum total?

I have one table bbc(country, region, area, population, gdp). I want to select the regions with a total population of at least 100 million. How can I do it with SQL? ...

MySQL: Insert row on table2 if row in table1 exists

I'm trying to set up a MySQL query that will insert a row into table2 if a row in table1 exist already, otherwise it will just insert the row into table1. I need to find a way to adapt the following query into inserting a row into table2 with the existing row's id. INSERT INTO table1 (host, path) VALUES ('youtube.com', '/watch') IF ...

Problem with Executing Mysql stored procedure

The stored procedure builds without any problem. The purpose of this is to take backup of selected tables to a script file. when the SP returns a value {Insert statements}. I am using the below MySql stored procedure, created by SQLWAYS [Tool to convert MsSql to MySql]. The actual MsSql SP is from http://www.codeproject.com/KB/database/...

compare previous ENCRYPT() call with new ENCRYPT() call mysql

I'm storing a string in mysql with the ENCRYPT() function. I want to fetch a row where that string is matched, but the new ENCRYPT() call gives me a different value, so they never match. This is expected as i'm not (and can't) give it a consistent salt: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_encrypt ...

Add column from another table matching results from first MySQL query

This is my query for available rooms in choosen period: SELECT rooms.room_id FROM rooms WHERE rooms.room_id NOT IN ( SELECT reservations.room_id FROM reservations WHERE ( reservations.arrivaldate >= $arrival_datetime AND reservations.departuredate <= $departure_datetime ) OR ( ...

How to return the value from MySql Stored Proc ??

I am using the below storedproc to generate the Insert statements of a specified table It is build-ed without any errors. Now i want to return the result set in "V_string" as output of the SP DELIMITER $$ DROP PROCEDURE IF EXISTS `demo`.`InsertGenerator` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertGenerator`() SWL_return...

MySQL - Accessing all relational data in one query

I have a relation mysql database setup. and I want to pull all the data for one item from the main row and all the relational data (ie multiple associated rows) in one query. What's the easiest/best way to do this aside from a multiple JOIN statement in one query? The tables and query currently used are below. Relational data --------...