(no subject)

[den]

bloody SQL assignment is killing me.

"List all data from the Person table who are niether staff nor patients."

fucked if I know.

Why does

WHERE person.person_id = staff.person_id
AND person.person_id = admission.patient_id

work and

WHERE person.person_id <> staff.person_id
AND person.person_id <> admission.patient_id

not?

Comments

[moonrose.livejournal.com]

select * from Person where type [or whatever the column is] not in ('staff', 'patient')?

[moonrose.livejournal.com]

if it isn't MSSQL though, you prolly need a semicolon at the end, and the syntax might be a bit different. bloody MS ;)

[dewhitton.livejournal.com]

it's bloody access

[mmmetta.livejournal.com]

dunno about access, but my guess would be because it's comparing strings. if you compared numbers you'd be able to get away with <>, i think. if its valid syntax anyway.

[moonrose.livejournal.com]

the strings thing is my guess too. i've been lucky and managed to avoid Access for the most part. :/

[mmmetta.livejournal.com]

mysql likes semi colons at the end, i have no idea what microsoft wants with its sql... it's bound to be different in some special unique and entirely wrong way :-)

[mmmetta.livejournal.com]

select * from persons where type not in ("staff","patients");

that's more or less what it should be, but 'type' would be the field you're using for the staff/patient flag. lemme know if that works, it's just off the top of my head.

[moonrose.livejournal.com]

great minds think alike ;)

[mmmetta.livejournal.com]

heh, that one was a no-brainer. i'm just gonna have to hide later on when den gets to the more complex stuff. i didn't go much past first semester database stuff, and since then it's been real simple stuff for me ;-)

[dewhitton.livejournal.com]

Access is asking for a type parameter entry.

[dewhitton.livejournal.com]

we haven't covered TYPE in the course, so I suppose technically I shouldn't use it.

jeez I hate programming of any sorts

[mmmetta.livejournal.com]

it's always a toss-up as to whether or not to use stuff they haven't taught you... i usually go for the 'right' answer (the one i feel is the best way to do it) rather than putting up with what they teach you.

i guess that's why i never really stick with my comp sci courses, they just frustrate me too much.

[writingstatic.livejournal.com]

that's a theoretical question, right? how the hell should *i* know?

[dewhitton.livejournal.com]

Thanks moonrose and mmmetta. I owe youse tim tams.

[charles.livejournal.com]

To answer the question.

Imagine the world's most fucking insane DBA came up with the following tables:

Now, try the first select: "Select first_name, last_name from table_1, table_2 where table_1.id = table_2.id"

Starting at the first row. We have table_1.id = 1, and first_name = 'Charles'. Looking across to the second table, we find all the rows where table_1.id = table_2.id. There's only one of those rows, and that joins me across to my last name, 'Miller'. So the results table will be:

Now for the second query. "Select first_name, last_name from table_1, table_2 where table_1.id <> table_2.id"

First, we look at the first row. The first_name is 'Charles', and the id is 1. Now we look at the second table, and find our join is telling us "find all the rows where table_1.id is NOT the same as table_2.id." The first row doesn't match this condition, but the other two do. So the result of this query would be:

[dewhitton.livejournal.com]

Kilkennys for you!

Try this

[berin.livejournal.com]

Try:
It's not elegant, but it should work.

Select *
From person
where person.person_id not in (select staff.person_id from staff)
and person.person_id not in (select admission.patient_id from admission)

In other words, I want everything in set 1 that does not have a matching ID in set 2 or set 3. [Subquery solution]

Re: Try this

[dewhitton.livejournal.com]

that's very close to the solution I worked out with charles on irc

Re: Try this

[berin.livejournal.com]

Not too bad for 2 AM and slightly inebriated. I've been trained too well, as I can not let a question like that slip by without an answer.

my brain hurts

[weyrdbird.livejournal.com]

Hooray for New MATH!
New Hoo Hoo Math!
It won't do you a bit of good to try and review math!
It's so simple, so very simple, that only a child can do it!!
-Tom Lehrer/New Math (after a long explanation of a hundreds multiplication table doone is base 8 with additions and distractions).

I can't even begin to decipher either problem. Just reading the abbreviations made my temples throb.I'll stick to HTML for dumbos.:D