sql

[TSQL] how to write a efficient to get the difference from two table

hi i am using SQL Server 2008, want to create a view to list the different between two tables. for example t1 id name ---- ------ 1 John 2 peter 3 mary 4 joe 5 sun t2 id name --- ---- 1 john 2 joe how to create a view to list all the name in t1 but not in t2. i tried to do that, and alwa...

duplicated foreign key constraints - reasons for or against

I've just come across a table in production which has 4 foreign key constraints. Two of the constraints are exact duplicates of the other two. ie fk1(a_id) references a(id) fk2(a_id) references a(id) fk3(b_id) references b(id) fk4(b_id) references b(id) I have never seen this before ... it strikes me as being quite wrong and my gut ...

collapsing two columns in one in sql?

Hello all! Say I have a table like that: |x1|y1| |0 |1 | |2 |4 | |3 |2 | I know I can do something like that: SELECT a.x1 AS .., a.y1 AS .., b.x1 AS .., b.y1 AS .., c.x1 AS .., c.y1 AS .., d.x1 AS .., d.y1 AS .. FROM xytable a, xytable b, xytable c, xytable d WHERE ... In my result table, however, I wo...

MDF (SQL) database file is over 88 GB and i have no idea why!

hi, With some reason my sql database file exploded to become over 88 GB and i can't see a reason why. i run few scripts to search for large tables, found nothing to be too big. i also tried to run shrink database (which did nothing) and shrink files (which return an error). has anyone has any idea what else i can do? Thanks. ...

Need group by and counting query

Suppose I have a table with threadid and itemid in it. There can be multiple entries of the itemid with the same threadid value... The query: SELECT DISTINCT THREADID, ITEMID FROM THREADS WHERE THREADID IN (SELECT DISTINCT THREADID FROM THREADS WHERE ITEMID = 10151) yields: THREADID ITEMID ----------- -------...

SQL: Create new database options explanation..

When creating a new database in SQL SERVER, there are many options. Can any of you guys please help me in understanding all these options? Any explanation/links/help well appreciated. ...

Which is the best for this query, "Inner join" or "Where"

I have this query, our friend "OMG Ponies" help me to make it :) :- SELECT t.id AS taskid, STUFF( ( SELECT ',' + x.tID FROM ( SELECT CAST(id AS VARCHAR(200)) AS tid FROM CRSTask c WHERE c.ParentTask = 7562 -...

Linq-to-SQL: how to handle database changes

I have several installations of my Linq-to-Sql app running in the field. Now I've created a new version, which adds a new column to a certain table. I've added this column in the dbml file. But when updating the installation, I want to preserve the existing database. How to handle this? Linq-to-SQL doesn't seem to like this inconsistency...

MySql Error 1064

Sql: create table autori( id_autor integer primary key auto_increment, nume varchar(50) not null, prenume varchar(50) not null )Engine=InnoDB; create table domenii( id_domeniu integer primary key auto_increment, nume_domeniu varchar(50) not null, descriere varchar(1000) not null )Engine=InnoDB; Php: $sqlpath = "fil...

Doctrine ORM - Self join without any real relation

I'm trying to find related objects to one object by matching the objects tags. I've constructed a mysql query which will return the objects that match the most by counting the matching tags. I'm new to doctrine (1.2) so I'm wondering if someone could help me to get on the right track modifying my schema and creating a DQL query? The bi...

PostgreSQL: Using subquery abbreviation ('AS') in the WHERE clause

Hi, Consider the following query in PostgreSQL: SELECT a, b, (A VERY LONG AND COMPLICATED SUBQUERY) AS c, (ANOTHER VERY LONG AND COMPLICATED SUBQUERY) AS d FROM table I want to have c and d in the WHERE clause, like: WHERE c AND d; But, as far as I know, I can only do: WHERE A VERY LONG AND COMPLICATED SUBQUERY) AND ...

Fluent Nhibernate and MVC

I am trying to do a MVC project where i want to use fluent nhibernate. gonna use sql. unfortunately i am confused about how to even start it. i found no tutorials in the net. i need help in organizing my project.thanks in advance ...

Is it realistic to edit an ecommerce db with Excel?

Hi, I'm building an ecommerce website and the client insists that he'll be able to edit products with Excel spreadsheet. It is a bit complex ecommerce website. We have physical product - computer service - computer installation product with variations - shirt with different sizes group product - a shirt and a tie. Is it realistic ...

Could somebody explain SOLR requestHandlers and responseWriters in detail?

Firstly, many parts of the solr wiki is not very useful for somebody who just learned how to index and search fields. It seems it is written for experts! It uses terms that is RELATIVE to solr, so it's very difficult to understand it without reading it several times over. NOTE: I have a classifieds website where the most recent ads alwa...

Linq no-noes - the catch all sql-like select?

Hi, One of the things that you have beaten into you as a junior developer is that you never, ever do a "SELECT *" on a data set, as it is unreliable for several reasons. Since moving over to Linq (firstly Linq to SQL and then the Entity Framework), I have wondered if the Linq equivalent is equally frowned upon? Eg var MyResult = fro...

SQL proc Diagram generating Software of a Programm Flow.

I have a couple of very long procs in Oracle 2000+ lines with lots of calls. And I'd like to generate programm flow Diagram (algorithm) for better understanding of the process for further Refactoring. It's not the code I wrote so I dont know the logic enough. What would you advice to do in this case? I tried to draw a text-like flow bu...

How to clear all cached items in Oracle

I'm tuning SQL queries on an Oracle database. I want to ensure that all cached items are cleared before running each query in order to prevent misleading performance results. I clear out the shared pool (to get rid of cached SQL/explain plans) and buffer cache (to get rid of cached data) by running the following commands: alter system f...

Linq to SQL query taking forever

Ok, first I thought I had a problem with how I was querying things. But apparently the problem lies in how linq translates my query to sql. Here's my linq: var items = (from p in ctx.bam_Prestatie_AllInstances join q in ctx.bam_Zending_AllRelationships on p.ActivityID equals q.ReferenceData join r in ctx.bam_Z...

MySQL query works in phpmyadmin but no rows returned in PHP result set

I have the following MySQL query: SELECT SQL_CALC_FOUND_ROWS p.* FROM product AS p LEFT JOIN productCategory AS c ON FIND_IN_SET(c.id, REPLACE(TRIM(p.categories), ' ',',')) WHERE ( c.id IS NULL OR c.status = 'D' OR p.categories IS NULL OR TRIM(p.categories) = '' ) AND p.deprecated = 'N' LIMIT 0,30 This query is to pick...

How to fetch users by rating with mysql query? (Top Rated)

There is a query I've just made SELECT * FROM users INNER JOIN ratings ON ratings.rateable=users.id ORDER BY SUM(ratings.rating)/COUNT(ratings.rating) But, it doesn't work, I just get one person in result, although there are 3 people in ratings table! I'm using php 5. I think sum(), count() doesn't work at all! Please,...