Selecting Rows Where ID Does Not Exist in Another Table

[Applellial]

Hello!

 

I have two tables, sites and directory. The first table (sites) has the following related fields: id, active (BOOL), name, userID. The directory table has the following related fields: id, userID, siteID.

 

The feature I'm creating will allow users to select one of their active sites that is not already in the directory from a dropdown, and add it to the directory. So the SQL statement must select rows from the sites table that have id's that do NOT match a row with the same siteID in the directory[ table. The selected sites must also match a single user ID, where both tables have a userID field (as shown above).

 

How would I go about doing this?

 

Thanks

[fenway]

Sounds like a job for a LEFT JOIN... IS NULL.

[ebmigue]

You could use WHERE NOT EXISTS, like so:

 

SELECT * FROM main_table a WHERE NOT EXISTS( SELECT * FROM some_table b WHERE a.id = b.id)

 

WHERE NOT EXISTS is easier to understand (notice that your problems wordings are somewhat similar to this solution).

 

 

Hope it helps.

[fenway]

Perhaps easier to understand for a person, but not for the parser.

[ebmigue]

Parsers who have problems like persons do should not be called parsers at all.

 

Parsers who understand like persons should not be parsers, but persons.

[fenway]

This is (again) one of those time where you are trying to resist having to actually issue queries the way the SQL parser executes them.

[ebmigue]

This is (again) one of those time where you are trying to resist having to actually issue queries the way the SQL parser executes them.

 

And you have to wonder, "why does he resist?"

 

Because the parser could still be improved. That's Why.

 

From assembly to Java. Notice the "improvement?". More and more, programming languages are turning into natural language.

 

If you have to insist that we have to write 80's style code...wow, that means only that for 30 years parsers have not improved.

 

This is a call to improve the MySQL parser (and optimizer as a consequence) so that we could write queries closer to natural language.

[fenway]

Sure, go ahead -- YOU improve the optimizer.  That's like asking someone who speaks French to go learn English because I only speak English.

 

In the meantime, the rest of us will continue to use the existing MySQL parser providing it with statements the way it prefers.

[ebmigue]

It is their product. It is their first responsibility.

 

Yeah sure, I can contribute. Give me a lifetime. lol

 

And besides, there have more resources and talents.

 

In the meantime, we have to "tweak" our queries, the way they prefer it.

[fenway]

No, their responsibility is to provide a product that works in a consistent fashion with predictable output.  Since you're not paying for it (presumably), you can't demand anything of them.

[jeff5656]

To me (with limited experience) this makes perfect sense to me:

 

SELECT * FROM main_table a WHERE NOT EXISTS( SELECT * FROM some_table b WHERE a.id = b.id)

 

Can you show what the left join null code would look like?  I would like to see the "correct" way (or "parser way" as you guys were saying)..

[ebmigue]

To me (with limited experience) this makes perfect sense to me:

 

SELECT * FROM main_table a WHERE NOT EXISTS( SELECT * FROM some_table b WHERE a.id = b.id)

 

Can you show what the left join null code would look like?  I would like to see the "correct" way (or "parser way" as you guys were saying)..

What you really wanted is the SEMIMINUS relational operator. That operator is neglected by MySQL, and thus the different "workarounds" being suggested here.

 

 

Actually, what you want could not be achieved by a LEFT JOIN (w/o using a subquery)!

And using a subquery, in general, in today's state-of-affairs is generally non-optimal.

 

Someone can suggest to you the LEFT JOIN version of course, but it pays to research it on your own. =)

[ebmigue]

No, their responsibility is to provide a product that works in a consistent fashion with predictable output. 

Now, is their product like that? No, I would claim, with theory as my guide.

 

Since you're not paying for it (presumably), you can't demand anything of them.

Yeah I know. But you have to wonder why people are actually paying it.

 

The point is, I'll start paying them, when the product is already right. Hence, my criticisms.

[ebmigue]

Actually, what you want could not be achieved by a LEFT JOIN (w/o using a subquery)!

 

Ah, I will correct that. This can be achieved using LEFT JOIN and NULL, as fenway suggested above.

 

Sorry for the wrong info.

[fenway]

No, their responsibility is to provide a product that works in a consistent fashion with predictable output. 

Now, is their product like that? No, I would claim, with theory as my guide.

 

Since you're not paying for it (presumably), you can't demand anything of them.

Yeah I know. But you have to wonder why people are actually paying it.

 

The point is, I'll start paying them, when the product is already right. Hence, my criticisms.

Your "theory" simply suggests that perhaps the inputs/outputs could be improved, or entirely changed -- but the current I/O is entirely predictable, in the sense that I know what's going to happen, time and time again.

 

To me (with limited experience) this makes perfect sense to me:

 

SELECT * FROM main_table a WHERE NOT EXISTS( SELECT * FROM some_table b WHERE a.id = b.id)

 

Can you show what the left join null code would look like?  I would like to see the "correct" way (or "parser way" as you guys were saying)..

Sure.

SELECT a.* FROM main_table a LEFT JOIN some_table b ON ( a.id = b.id ) WHERE b.id IS NULL

[ebmigue]

@fenway

 

I have no say to your professional opinions/decisions.

 

If you undermine the importance of theory, of the basis of your technology (for technology is applied science, and science is theory) - if you forget that basis, well, I cannot anymore do anything.

[fenway]

Not importance, practical relevance.

[ebmigue]

Theory is practical and relevant.

[fenway]

Theory is practical and relevant.

Actually, the _results_ of theory have "practical relevance".  The theory itself was just a convenient way to describe a consistent set of rules.

[ebmigue]

And because theory is able to describe a consistent set of rules, it is therefore wise to know theory (and its rules) to have a high degree of assurance that one is in the "right way", so to speak.

 

The derivation of such rules is non-arbitrary; it involves a high degree of examination, logical-mathematical thinking, and is of course performed by the best minds in the industry.

 

That is why knowing their thoughts - even if in a simplified form - is always an advantage.

[fenway]

Yes, but the theory describes an ideal, something to aspire to -- it's just not applicable in its entirely to each and every case.

[ebmigue]

At least there is something to aspire to and serve as guide, rather than go about aimlessly, right?

 

And no. There are theories w/c are applicable in each and every case.

 

Remember that we are considering here relational theory, w/c is based on set-theory (mathematics) and predicate logic (symbolic logic).

 

Such sciences are deductive by nature. Their findings are true, even if we recognize it or not.

 

Whether we are on earth or on a different galaxy, math will tell us that 1 + 1 = 2; logic will tell us that NOT FALSE = TRUE.

 

Relational theory will tell us that {} JOIN {} = {}.

 

And there are many such axioms, theorems w/c theory could help us as we go about on our daily professional lives, and w/c are universally and absolutely true.

 

If we don't accept them as true, well...

[ebmigue]

Of course, it is true that relational theory will not help the programmer of an application and implementer of a DBMS in certain very important matters. Such as the sort of network connection to use; the sort of data-storage to use. Etc., etc.

 

Certainly, that is beyond the scope of relational theory as such. And it will be just natural to appeal to experience and intuition

when confronted w/ such issues.

 

But then again, at least there is that clear dividing line as to where theory ended, and day-to-day practice started.

 

Which is a good thing, too.

 

Why?

 

Because only then we had clearly identified w/c parts of our jobs will remain true and constant (or must remain true and constant),

and w/c might need maintenance, improvement, optimization, etc.

 

W/c is the most desirable status quo? A chaotic and uncertain mixing of the two, or a nice and clear balance between them?

[fenway]

But by their very truth, the axioms are self-evident (after the theory exists), and hence can be considered background noise to the actual task at hand.

[ebmigue]

I don't consider it "background noise."

 

They are facts w/c ensure us that what we are building will make sense.

 

W/o them, all computing becomes guess-work, unreliable, incoherent - much the opposite of what it was originally intended 80 years ago.

 

Yes, w/ or w/out theory, such truths already and will continue to exist; even before humans came to existence (at least that is, the prevalent and most sensical belief to assume.)

 

But: had theory not existed, those truths will remain unrecognised, and will remain unused for practical purposes.

 

The role of theory, w/c is to formalise and discover such truths, is thus very significant.