sql

Fine-tuning my MySQL relations database and how to use JOIN on it?

I am very new to mysql and databases, so I need help here... How should I use JOIN to fetch a record from the tables below, when the only given variable is the ad_id? category and category options are filled in manually i.e. the users may not alter them. So they are only reference tables, but I am not sure this is how I should do it......

Selecting the latest per group of items

i have 2 tables products and cost PRODUCT ProdCode - PK ProdName COST Effectivedate - PK RetailCOst Prodcode i tried this query: SELECT a.ProdCOde AS id, MAX(EffectiveDate) AS edate, RetailCOst AS retail FROM cost a INNER JOIN product b USING (ProdCode) WHERE EffectiveDate <= '2009-10-01' GROUP BY a.ProdCode; uhm yah its show...

Show only Previous Month's data in SQL

I have been told to create a query that will show the top 10 most used applications in our company, and on the 11th row should group all other rows into it and call the record "Other" and sum all the sessions together. How do I modify this code to only show monthly records? Code Snippet SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) ...

How to specify the name of a database to get the name of its tables using sql server 2005 syntax

I need to write a querry in SQL SERVER 2005 to get the name of the tables of a specified database name. So i need the syntax to specify the name of the database using SQL SERVER 2005. Does anyone have any idea ? Thanks in advance for your help. ...

Filtering duplicities by another table.

Hello, I have two (example) tables here A) data_table: +----------+-------+ | location | value | +----------+-------+ | 43 | 38 | | 44 | 31 | | 3 | 31 | | 11 | 38 | | 47 | 35 | | 49 | 31 | | 50 | 31 | | 55 | 16 | | 56 | 16 | | 59 | 35 | ...

SQL : Select a dynamic number of rows as columns

I need to select static colums + a dynamic number of rows as columns in SQL TABLE 1 ------- HotelID BlockID BlockName TABLE 2 ------- BlockDate (unknown number of these) NumberOfRooms Desired Result Row ------------------ HotelID | BlockID | BlockName | 02/10/10 | 02/11/10 | 02/12/10 | ...N Where the date columns are the unknown num...

How can I efficiently compare my data with a remote database?

I need to update my contacts database in SQL Server with changes made in a remote database (also SQL Server, on a different server on the same local network). I can't make any changes to the remote database, which is a commercial product. I'm connected to the remote database using a linked server. Both tables contain around 200K rows. M...

Split Address column into separate columns in SQL view

I have an Address column in a table that I need to split into multiple columns in a view in SQL Server 2005. I need to split the column on the line feed character, chr(10), and there could be from 1 to 4 lines (0 to 3 line feeds) in the column. Below are a couple of examples of what I need to do. What is the simplest way to make this h...

MySQL Select Like

I've got a field in my table for tags on some content, separated by spaces, and at the moment I'm using: SELECT * FROM content WHERE tags LIKE '%$selectedtag%'" But if the selected tag is 'elephant', it will select content tagged with 'bigelephant' and 'elephantblah' etc... How do I get it to just select what I want precisely? Thanks...

Character set in MySQL; Can't insert special characters, why?

I can't insert special characters. Whenever I try to, all characters BEFORE the special character gets inserted, but after the special character nothing is inserted into the field. $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql'); mysql_select_db($dbname,$conn); mysql_set_charset('utf-8', $conn); mys...

Grouping using LINQ

I'm having a heck of a time with transforming a simple SQL Query into a LINQ query(using vb btw) Here is my SQL: SELECT USRDEFND5 FROM int_gp_employee GROUP BY USRDEFND5 The xml looks like this: <int_gp_employee> <row> .... <usrdefnd5>Some GUID</usrdefnd5> </row> </int_gp_employee> I've tried a number of di...

How do I make a SQL table from the result of another select?

I'm asking for MySQL specifically, but the answer might work for a lot of SQL databases. If I have a select statement like this: select * from users where age > 5; How do I assign that to a new table? ...

Another mysql JOIN question...

I have these tables: classified: classified_id (PK) price headline cat_id // THIS IS ANYTHING FROM 1 TO 30 DEPENDING ON CATEGORY. IT IS SO THAT I CAN LINK WHICH CATEGORY TO USE IN THE CATEGORY TABLE BELOW text etc... category: cat_id (PK) cat_name category_options: option_id (PK) cat_id (FK) // FOREIGN KEY FROM CATEGORY TABLE... optio...

mysql @@identity vs sql-server last_insert_id()

Am I correct in understanding that mysql's LAST_INSERT_ID() function doesn't reset between insert attempts (as @@identity does in sql-server)... that if the immediately preceding insert operation fails, LAST_INSERT_ID() will return the pk of whatever the pk of that connection's last insert to any table with an auto-incrementing primary k...

MySQL headache, should I or should I not?

I have a classifieds website. I am using SOLR for indexing and storing data. Then I also have a MySQL db with some more information about the classified which I dont store or index. Now, I have a pretty normalized db with 4 tables. Whenever ads are searched on the website, SOLR does the searching and returns an array of ID_numbers whic...

My MySQL query won't work

I have the following query which searched an episode guide database for user inputted data: $query = "SELECT * FROM epguide WHERE EpisodeTitle LIKE '%$trimmed%' OR Synopsis LIKE '%$trimmed%' OR Notes LIKE '%$trimmed%' ORDER BY o"; This works OK, but when I add 'Series = '...

Getting the absolute position of a node using sql

I have an xml document of the bible as <bookcoll> <book> <bktshort>Matthew</bktshort> <chapter><chtitle>Chapter 1</chtitle> <v>The book of the generation of Jesus Christ, the son of David, the son of Abraham. </v> <v>Abraham begat Isaac; and Isaac begat Jacob; and Jacob begat Judas and his brethren; </v> .. </chapter> <chapter><chtitl...

How should i modify this SQL select?

SELECT TOP (5) mydb.Te.TeamGR AS TeamInGR, Te_1.TeamGR, SUBSTRING(mydb.Data.AkrivesSkor, 1, 1) AS GoalsIn, SUBSTRING(mydb.Data.AkrivesSkor, 3, 1) AS GoalsOut FROM mydb.Te INNER JOIN mydb.Data ON mydb.Te.TeamID = mydb.Data.TeamInID INNER JOIN mydb.Diorganoseis ...

Is it possible to create a temp table on a linked server?

I'm doing some fairly complex queries against a remote linked server, and it would be useful to be able to store some information in temp tables and then perform joins against it - all with the remote data. Creating the temp tables locally and joining against them over the wire is prohibitively slow. Is it possible to force the temp tab...

SQL function to sort by most popular content

I don't know if this is possible with SQL: I have two tables, one of content, each with an integer ID, and a table of comments each with an "On" field denoting the content it is on. I'd like to receive the content in order of how many comments have it in their "On" field, and was hoping SQL could do it. ...