Check if PHP varible is contained in SQL field
Hi, Is the following possible? field in DB contains: 1,2,3,4 $a=1; $selqry="select * from table where field={contains $a}"; Thanks, B. ...
sql
SQL query that'll rollback if any statements fails
I would like to write a SQL script that executes multiple individual SQL statements; if any of those statements fail, I'd like to rollback the entire transaction. So, something like: BEGIN TRANSACTION insert into TestTable values (1) insert into TestTable values (2) insert into TestTabe values (3) --if any of the statements fail ROLL...
SQL Azure and VS 2010B2 or SSMSE 2008
Ok, I see that people have asked this question before, but I'm seeing some conflicting statements. Can I, or can I not, connect directly to my SQL Azure database from SSMSE 2008? I see posts from before November that the SSMS 2008 RC would be able to connect directly - so I don't understand why the newest SSMSE cannot connect. Is it j...
MySQL - Select statement, order by number of comments per article.
Hi I'd like to do something like the following: SELECT * FROM tbl_article JOIN tbl_comments ON tbl_article.id = tbl_comments.article_id ORDER BY COUNT(tbl_comments.article_id) Can anyone suggest how I might get this to work? ...
SQL what is faster left 2 or convert(varchar(2),'Chgffgjjx')
If I want to return the first two characters of a string which is the best / fastest method? Also if I have a date and want to group by day I will use convert(varchar(10),getdate()) as our db format is in yyyy/MM/dd, which would give '2010/02/10' if I wanted to group by month I would use varchar(7) instead to give '2010/02' Is there a...
iPhone sqlite desc vs asc???
How come in my code when i use @"select Score from tblMed order by Score desc limit 10" everything works fine, but when I change "desc" to "asc" the scores don't show up??? ...
error with sql function creation
Getting the following error when trying to create this sql function in SQL2k5. Any ideas here? It runs fine outside the function. UPDATE THIS work NOW ,but I have to come to the realization that this needs to be in the form of a view since I need to do an inner join on the product_id so this current form will only work when I am passing...
Google Maps, SQL, XML, Ajax oh my!
Hi All, Quick question regarding Google Maps, AJAX and some back end data. How would I go about creating google map that updates in "real time" with information I have stored in the database? The way I see it working in my head is. Database collects geo tagged entries from users. A script (probably php) retrieves the latest record. ...
Best way to store this data in SQL Table
I am trying to best figure out a way to store this particular case of data in a static database table on the front end the user will be presented with a simple table (say 5x5 by default) but the user can expand or delete rows/ columns at will. The idea is that the first column will be labels that are the same as the headers, similar to...
MySQL - Grouping dayname() for unique columns
I have a stored procedure that takes data from multiple tables. The stored procedure is called from a PHP script and the resulting data is placed in a grid. The issue I'm having is I'm not sure how to get the dayname to be unique so that at maximum there are seven columns, one for each day of the week, as opposed to getting results like...
Do multiple foreign keys make sense?
can it make sense for one table to have multiple foreign keys? Suppose I have three tables, Table A, Table B and Table C. If I think of the tables as objects (and they are mapped to objects in my code), then both Tables A and B have a many to one relationship with Table C. I.e. Table/object A and B can each have many instances of C. ...
How to aggregate data into view with the same ID
I have an existing view that returns data in the following format based on aggregating option names that have the same product_id. The view name is "vProdOptions" option_name product_id XSMALL (2-6) 17854 SMALL (6-10) 17854 MEDIUM (10-14) 17854 LARGE 18232 How do I return this data i...
Which Union ALL approach should be used?
We have 15 audit trail tables that need to be combined to look for inventory adjustments with certain criteria only. Is it best to do a UNION ALL with all tables, and then filter for criteria, OR throw the criteria into the mix on each table before doing a UNION ALL? Each table is only a few thousand records and the final filtered list...
Using multiple UNION ALLs to build a view: is there a better way?
There are some constraints to this question; I don't have the ability to fundamentally alter any database structure. The challenge here is that I have rows in a database that contain information that really should be on its own row. A somewhat simplified example of the column structure: [PersonID] [FirstName] [LastName] [FirstNameGuest...
Crystal/Oracle parameterized query to use variable
I currently have a Database Command object in my Crystal Report that looks something like SELECT * FROM table WHERE field = {?EndDate} I want to change it so it looks more like IF {?EndDate} = DATE '1900-01-01' MyVariable = ADD_MONTHS(LAST_DAY(SYSDATE), -1) ELSE MyVariable = {?EndDate} SELECT * FROM table W...
Sybase: how can I remove non-printable characters from CHAR or VARCHAR fields with SQL?
I'm working with a Sybase database that seems to have non-printable characters in some of the string fields and this is throwing off some of our processing code. At first glance, it seemed to only be newlines and carriage returns, but we also have an ASCII code 27 in there - an ESC character, some accented characters, and some other odd...
Which of these values are NOT in the table?
I have a list of values, and want to know which ones don't already exist in the table. (This is on sql server 2000) Consider the following: CREATE TABLE myTable ( foo VARCHAR(10) ) GO INSERT INTO myTable ( foo ) SELECT 'aaa' UNION ALL SELECT 'bbb' UNION ALL SELECT 'ccc' GO SELECT foo FROM m...
Linq to SQL equivalent of SUM GROUP BY SQL statement
Hi everybody! I'm having a hard time figuring out how to translate this simple SQL statement to (c#) linq to SQL : SELECT table1.vat, SUM(table1.QTY * table2.FLG01 + table1.QTY * table2.FLG04) FROM table1 inner join table2 on table2.key= table1.key where '2010-02-01' <= table1.trndate and table1.trndate <= '2010-02-28' Group by ta...
SQL-query to find articles with at least one of the listed tags
I have 3 tables: articles, tags and article_tag (the one which connects them). I'd like to find all articles that have some (or all) of the tags listed in the query and order them by the number of matched tags. Is it possible? Example in english: Query: Find me all articles that have at least one of these tags "jazz, bass guitar, soul...
Apply native SQL where clause to Nhibernate query for entity
I have this problem. I have a module (module 1) that use Nhibernate to manage entity persistence; this module interacs with an other module (module 2). The "module 2" allows to generate dynamically native SQL where clause. Now I would use it to manage filter operation in "module 1". Which is the bast way to do it? Is possible get the...