Extracting a table from a mysql.sql dump file
I have a products table in a mysql.sql file. I wish to extract that one table and place it in it's own file. How would you go about doing this? ...
sql
SQL SUM with CASE and DISTINCT?
Continuing on my last question... Let me try to explain my schema. I have three tables we'll call users (with columns id and name), parties (with columns id, partydate, and user_id) and questions (with columns id, createdate, and user_id). My requirement is to show for every user the number of parties within the last year and questio...
MySQL: Split comma separated list into multiple rows
I have an unnormalized table with a column containing a comma separated list that is a foreign key to another table: +----------+-------------+ +--------------+-------+ | part_id | material | | material_id | name | +----------+-------------+ +--------------+-------+ | 339 | 1.2mm;1.6mm | | 1 | 1.2mm | | ...
Does AdventureWorks ASP.Net web project exist?
I know nopCommerce and dashCommerce quite well, but I so far have never found any open source shopping cart (including HumanResources and Vendors) bigger than the nopCommerce. I'm wondering whether AdventureWorks database comes together with a web project or other UI projects as an open source. Or it just exists as a sample database on...
Search patterns from SQL consecutive rows within a column
I have a table of text like below, is there any one simple SQL query can find ID 3 (or 3 to 5) from keyword pattern like "a master degree". Thanks? ID Words 1 He 2 has 3 a 4 master 5 degree 6 in 7 University 8 of 9 South 10 India ...
and/or conditions in a SQL query
I have to select all the records where FIELD1 is value11 or value12 and FIELD2 is value21, value22, value23, ... or value29. All 2*9=18 pairs of admissible values for FIELD1 and FIELD2 are possible. Which is the most compact form to write down my SQL query? ...
How to write a SQL statement to use "LIKE" for 9 different values?
I have to select all the records where: where FIELD2 like '%value21%' or FIELD2 like '%value22%' or FIELD2 like '%value23%' -- repeat up to or FIELD2 like '%value29%' Here, value21, ..., value29 are parameters which the user can put in a form before submitting the query. They are (not subsequent) numerical codes, and FIELD2 is a dat...
Is there a better way to write this query?
So let's say I'm trying to get a list of all my users who belong to a certain user group. Easy: SELECT * FROM users, usergroups WHERE usergroups.user_group_id = 1 NOW, let's say I want to get a list of all users who belong to a certain user group AND who have an email that ends in .edu. SELECT * FROM users, usergroups WHERE use...
MySQL FullText search without FULLTEXT index
Hi, Within one of our systems we have an option that switches the search system between a LIKE search, or a FullText search with either IN BOOLEAN MODE or WITH QUERY EXPANSION For some reason, the FULLTEXT index was missing but when the system was set to be IN BOOLEAN MODE the query still worked, in the case of WITH QUERY EXPANSION the ...
Why is this SQL generating a temporary table and running so slow?
Hi, I have the following SQL generated from my Rails app, it is trying to get a list of all auto models that have live adverts in a marketplace app & from mysql: SELECT `models`.* FROM `models` INNER JOIN `autos` ON autos.model_id = models.id INNER JOIN `ads` ON `ads`.id = `autos`.ad_id WHERE (ads.ad_status_id = 4 AND pub_start_...
save list order in database?
I have a list with values from the db, and with jquery ui I can change the sorting order of the list witch is great! But now I wonder how I can save the changed order to the db? With a do until, but how? This is what I got so far. sql = "SELECT * FROM links ORDER BY u.avd_id DESC;" set rs = conn.Execute (sql) if saveList="saveList"...
SQL - field concatenation, based on variable
Hi, I have a need to build a string from Last Name, First Name, Middle Initial according to the following rules: If the Last Name is unique, just return the Last Name If the Last Name isn't unique, but the first letter of the First Name is unique, return Last Name + first letter of First Name If the Last Name and first letter of the F...
SQL: Counting Problem relating to two tables
I have a table with columns : Sku, Count in another table there are sku's with different images, so in this table one sku is repeated several times with multiple file extensions, what i need to do is to count the number of images that a sku has in the second table and then update the count column in the first table with that number. I w...
SQL Find Holes in Linear Time-Based Table
Hi, I have a linear datetime based table in MySQL and SQL server for a time-based data collection system. There is required to be a new entry in this table that will be timestamped exactly every 10 seconds. Sometimes, the data collection source goes down for a while and comes back up, at which point the newest data is inserted into the...
How to call a scalar-valued from with a stored procedure
Hi, say I have a scalar-valued function with takes two Guids as parameters: dbo.CarDistribution The two Guids area ModelId and AreaId. The function returns another Guid. Could someone please explain to me how I call this function within a stored procedure and set the result returned to a variable. I tried calling it with: CarDistri...
SQL And NULL Values in where clause
So I have a simple query that returns a listing of products SELECT Model, CategoryID FROM Products WHERE (Model = '010-00749-01') This returns 010-00749-01 00000000-0000-0000-0000-000000000000 010-00749-01 NULL Whitch is correct, so I wanted only get the products whose CategoryID is not '00000000-0000-0000-0...
PostgreSQL HAVING clause
I posted on here last night looking for help with some homework. I'm down to my last question. Here's the relevant piece of the schema I am working with: CREATE TABLE votesOnPoll( user_id int, poll_id int, option_id int, voteDate date, CONSTRAINT votesOnPoll_pk PRIMARY KEY (user_id, poll_id), CONSTRAINT votesOnPoll_user_f...
sql nested select invalid column
Hey all, I've been struggling with a select statement for a bit now and I was hoping to get some help. I currently have a list IPs in a temporary table and I want to calculate how many times this IP acts as a server and how many times it acts as a client. No matter how I manipluate this select statement I always get the same error ...
How to use OR in MySQL query and ignore second condition if first is true?
I am using a query like this: SELECT * FROM table1 WHERE countries LIKE '%US%' OR countries LIKE '%ALL%'; This returns all the entries that have "US" or "ALL" in countries column, but I want it to return ONLY those rows that contains "US", and if non is found then use the next condition and return all that contains "ALL". Is it possi...
How to generate a new Guid in stored procedure?
Hi, I currently have a stored procedure in which I want to insert new rows into a table. insert into cars (id, Make, Model) values('A new Guid', "Ford", "Mustang") So the primary key 'id' is a Guid. I know how to create a new Guid in C# code but within the stored procedure I'm unsure how to generate the new Guids for the primary key v...