Stories of a short, fat bastard.

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

From:

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

From:

it's bloody access

From:

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.

From:

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

From:

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 :-)

From:

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.

From:

great minds think alike ;)

From:

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 ;-)

From:

Access is asking for a type parameter entry.

From:

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

jeez I hate programming of any sorts

From:

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.

From:

writingstatic.livejournal.com

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

From:

Thanks moonrose and mmmetta. I owe youse tim tams.

From:

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:

From:

Kilkennys for you!

From:

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]

From: