I have some legacy code I'm going through, and i just found a join with two "on" clauses... select * from table inner join table3 inner join table2 on table3.key = table2.fkey on table.key = table2.otherkey What does this kind of join mean? (It currently works so it's not a syntax error) (Edit: Fixed the code, was missing a join) ...
I have table dates as following "dates" "one" "two" "three" "four" date1 id1 id2 id3 id4 date2 id3 id1 id4 id2 and a second table id2name "ids" "names" id1 name1 id2 name2 id3 name3 id4 name4 I have a mental block trying to write SELECT * FROM dates WHERE `date`='$date' LEFT JOIN `id2name` O...
I getting this error while developing stored procedure "Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict." statement is like this Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1 UNION ALL Select '' AS Place, 'Arizon...
Hi Everybody. I have multiple tables . I have created one stored procedure where I am selecting the table values. like create proc myProc as select col1, col2, col3 from table1 select cola, colb, colc from table2 select colp, colq, colr from table3 select colx, coly, colz from table4 I am using ...
I have a table with year column and this column shouldn't have duplicate values. So I end up with a table with only one 2007 year record for example. So how could I delete those rows that have duplicate year value? Thanks ...
Hi, I have articles and categories in a n:m relation: I looking for a find statement on the Category Model so that I can get all categories witch consist at least one article. Should be easy, but I didn't find a efficient solution, without searching retrieving all the articles. Thanks, Maechi ...
Exmaple: [empid date bookid] ---------- 1 5/6/2004 8 2 5/6/2004 8 1 5/7/2004 8 1 5/8/2004 6 3 5/8/2004 8 2 5/8/2004 7 In this table,I need to get empid 1 as output..since it has bookid 8 more than once.. thanks in advance.. ...
Hi, all! I have Postgresql and query SELECT complex_expression() FROM t WHERE complex_expression() != ''; Is there a way to write complex_expression() only once in query and then refer to it? Thanx in advance ...
I have a sql server table with the following data: PrimaryKey ForeignKey DataType Value 1 1 Actor abc 2 1 Movie efg 3 1 Movie hij 4 2 Actor mno 5 2 Movie pqr 6 2 Movie stu 7 ...
Hi, Im having two tables with attributes like date(datetime),headline(varchar),text(text) Now i want to UNION ALL these two tables and sort by the datetime. When doing this i'm getting the error: Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that requir...
I am used to Oracle and to create a dblink in my schema and then access to a remote database like this : mytable@myremotedb, is there anyway do to the same with PostgreSQL ? Right now I am using dblink like this : SELECT logindate FROM dblink('host=dev.toto.com user=toto pass...
Hi I have a tables of rows that exists in multiple databases. I will need to update the rows from one database into the other database and I have a primary key of int. So I am looking for a way to uniquely identify the server that added a particular row. I could use a uniqueidentifier in a default using the newid() or newseqentialid() b...
We are using sp's to convert a uploaded file into corresponding master values and their combination as the business rule demands. For this we have made 4 sp's . All these sp's are called from a single sp which the user calls from application along with the data from the file. Note: Each sp must be called after the previous one has finis...
We have batch files to compile all the sql scripts in a folder against a database. Problem is that we have a couple of thousand scripts and it makes an output file for each script. It makes it difficult to check for errors and a lot to do cleanup on. Is there any way to output all of the results to one file? Here is the script we use: ...
Hi lads, I have another simple one. How to make a long decode inside a function in oracle? My select looks like this: select something, sum(ofsomethingelse) from a_table where code in ('390','391','392','393','394','395','396','397','398','400','402','406', '407','408','409','410','411','412','413','414','416','418','471','473', '173...
I have three SQL selects, the results of which I need to add together. Two of the three use fairly complex joins. select sum(field_one) from t_a join t_b on (t_a.bid = t_b.id) where t_b.user_id=:id select sum(field_two) from t_c join t_d on (t_c.did = t_d.id) where t_d.user_id=:id select sum(field_three) from t_e where t_e.user_id=:i...
How do I pass a dataset object to a stored procedure? The dataset comprises multiple tables and I'll need to be able to access them from within the SQL. ...
I have an updatable transactional replication set with SQL Server 2008. Everything is working fine. I added a new table to the existing publication thru sp_addarticle followed by sp_addsubscription. After that I ran the snapshot agent. Snapshot has been generated only for newly added table. So the new table was successfully replicated to...
I know there are a lot of other SO entries that seem like this one, but I haven't found one that actually answers my question so hopefully one of you can either answer it or point me to another SO question that is related. Basically, I have the following query that returns Venues that have any CheckIns that contain the searched Keyword ...
Hi, I have two databases at the same server 192.168.1.100 DB1 and DB2 When I'm trying to execute : select h.code,eh.Defaultname From hotels h JOIN [192.168.1.100].[dbo].[DB2].Hotels eh ON h.code = eh.code I get Could not find server '192.168.1.100' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. I don't...