sql - How to find rows with duplicate columns in a subquery? -


i working data in eve static dump , ms sql server express.

the dump contains table mapdenormalize, having columns named solarsystemid , typeid, both of ints (and neither of key). i'm trying find values of solarsystemid appear more once, various combinations of typeid.

i have query 

-- systems have plasma or temperate planet select distinct d.solarsystemid, d.typeid mapdenormalize d    join invtypes t on d.typeid = t.typeid      t.typename in ('planet (plasma)', 'planet (temperate)')      order solarsystemid

which returns 1 row each solarsystemid has plasma planet , 1 row each temperate planet. trying figure out how use subquery find solarsystemids have both kinds of planets, have come empty-handed.

i started out thinking like

select solarsystemid ( above query ) count(solarsystemid) > 1

but doesn't parse. correct approach?

select d.solarsystemid, count(d.solarsystemid) counts mapdenormalize d       join invtypes t       on d.typeid = t.typeid  t.typename in ('planet (plasma)', 'planet (temperate)') group d.solarsystemid having count(d.solarsystemid) > 1;

the sqlfiddle


Comments

Post a Comment

Popular posts from this blog

iphone - Three second countdown in cocos2d -

i trying have 3 second countdown display when level beat in game. did: -(void) gamesegmentbeat { [self pauseschedulerandactions]; // overlay game opaque background ccsprite *opaquebg = [ccsprite spritewithfile:@"background1.png"]; opaquebg.position = screencenter; [self addchild:opaquebg z:10000]; // these 3 labels 3 seconds cclabelttf *three = [cclabelttf labelwithstring:@"3" fontname:@"helvetica" fontsize:100]; three.position = ccp(screencenter.x, screencenter.y); cclabelttf *two = [cclabelttf labelwithstring:@"2" fontname:@"helvetica" fontsize:100]; two.position = ccp(screencenter.x, screencenter.y); cclabelttf *one = [cclabelttf labelwithstring:@"1" fontname:@"helvetica" fontsize:100]; one.position = ccp(screencenter.x, screencenter.y); // secondspast specified in update method secondspast = 0; if (secondspast == 1) { [self addchild:thr...
Read more

hyperlink - how to do url routing in php -

sorry, don't know should exact technical term problem statement title of thread might misleading/confusing. that's reason why not able search problem solution on google. okay, i'm developing simple social network users. want each user have own profile link. for example, if username of user 'xyz' profile link should ' http://example.com/user/xyz now how create "virtual" link? edit: okay, learned it's mod_rewrite , got similar question how dynamically rewrite url facebook routes php delicate matter. mod_rewrite can in beginning, after becoming mess , f*** rather help, when "big" software needs scalable. remembering "routes" not part of specific application, nomenclature "server" interprets request "client" , returns specific value "client" configuration of request. ie, "router" server. what recommend study http protocol, rest , on. this surely in many things, knowi...
Read more

c - Avoiding Extra Malloc in Linked List (node->next = NULL) -

in linked list, i'm trying avoid malloc ing node without adding bunch of if statements , such. have following: polynomial create() { polynomial head = null; polynomial temp = null; int numterms, coefficient, exponent; int counter = 0; printf("enter number of terms in polynomial: "); scanf ("%d", &numterms); printf("\n"); if (numterms == 0) { head = malloc(sizeof(term)); head->coef = 0; head->exp = 0; head->next = null; return head; } while (numterms != counter) { // ask input printf("enter coefficient , exponent of term %d: ", (counter + 1)); scanf("%d %d", &coefficient, &exponent); printf("\n"); // create term if (temp == null) temp = malloc(sizeof(term)); temp->coef = coefficient; temp->exp = exponent; temp->next = null; //if((numterms - 1) != counter) tem...
Read more