oracle - sql join and minus -

-

i seem having problem getting query work. know i'm close. here's copy of er diagram

i think close achieving want code, invalid identifier when trying run it. think because practice being changed somehow after joining, getting invalid identifier on row 5?

select   staffid, staff_firstname, staff_surname, practice.practice_name, practice.practice_city staff join practice on staff.practiceid = practice.practiceid minus select   staffid, staff_firstname, staff_surname, practice.practice_name, practice.practice_city staff    role = 'gp';

basically i'm trying use minus construct find practices not employ gp , include information such city , practice_address.

i can use minus construct find out how many staff not have role of gp so:

select   staffid, staff_firstname, staff_surname staff minus select   staffid, staff_firstname, staff_surname staff    role = 'gp';

where results:

   staffid staff_firs staff_surn __________ __________ __________          8 nyssa      thornton          9 mona       bradshaw         10 gloria     pena

i'm struggling use join minus construct information gp's practice address , city etc.

any appreciated!

the second select, after minus, referring columns practice table - doesn't join it:

select staffid, staff_firstname, staff_surname,   practice.practice_name, practice.practice_city staff join practice on staff.practiceid = practice.practiceid minus select staffid, staff_firstname, staff_surname,   practice.practice_name, practice.practice_city staff join practice on staff.practiceid = practice.practiceid    role = 'gp';

that isn't going give want though, remove rows staff gps, not trace of practices have gps - non-gp staff @ practices still shown.

if don't want remaining staff details need include columns practice table in select lists, , minus give want (and gordon linoff has shown 2 alternatives minus in case). if want remaining staff details can use not-exists clause rather minus - like:

select s.staffid, s.staff_firstname, s.staff_surname,   p.practice_name, p.practice_city staff s join practice p on s.practiceid = p.practiceid not exists (   select 1   staff s2   s2.practice_id = p.practice_id   , s2.role = 'gp );

this similar gordon's second query has join staff details. again, if don't want those, use gordon's simpler query.

you use aggregate check, or analytic function if you've learned abput those, save having hit tables twice.


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more