Get results of rows that aren't in other results

[imperium2335]

Hi, I have the following query:

 

SELECT detail_sheets.id FROM detail_sheets WHERE NOT EXISTS (SELECT detail_sheets.id FROM detail_sheets, enquiries, jobs WHERE enquiries.detail_sheetRef = detail_sheets.id AND jobs.enquiryRef = enquiries.id AND detail_sheets.status = 'Customer')

 

Where I'm trying to return customer ids (detail_sheets) where they have not had a 'job' raised against them. The subquery should select all the customers who have had a job.

 

But I am getting 0 results returned on something I know for certain there should be results.

 

Can someone tell me where I'm going wrong with this query?

[AyKay47]

at first glance your syntax looks correct here, do you receive any Mysql errors when debugging with mysql_error?

[fenway]

You've aliased the same table twice.

[ebmigue]

Try:

 

SELECT DISTINCT detail_sheets.id FROM detail_sheets
WHERE NOT EXISTS (
    SELECT DISTINCT detail_sheets_.id 
    FROM 
          detail_sheets AS detail_sheets_
         ,enquiries
         ,jobs
   WHERE 
             enquiries.detail_sheetRef = detail_sheets_.id 
     AND jobs.enquiryRef = enquiries.id 
     AND detail_sheets_.status = 'Customer'
     AND detail_sheets_.id = detail_sheets.id
)

 

Hope it helps.

[fenway]

I'll tell you why I don't like DISTINCT -- it hides the fact that there's an opportunity for index usage.

[ebmigue]

Then MySQL is not user friendly.

[ebmigue]

To clarify:

 

DISTINCT is NOT "responsible" if "the fact that there's an opportunity for index usage" is hidden.

 

DISTINCT is merely a syntactic construct.

 

As such, it has no life of its own, so to speak.

 

As such, it is further processed by the parser, and then the parse tree is then processed by the DBMS optimizer.

 

Hence, "the fact that there's an opportunity for index usage" is hidden is thus due to the optimizer's behavior.

 

One can go on claim to avoid using DISTINCT for such reasons.

 

Sure.

 

But then there is the possibility of duplicate records/tuples, and all the problems with it.

[fenway]

Then MySQL is not user friendly.

In certain circumstances, I entirely agree -- the parser is far too forgiving and lets novice users walk down the primrose path without so much as a warning.

 

DISTINCT is mean to remove duplicate rows -- but that obscures the intent of the query in ways that GROUP BY does not.

 

I'm not sure why you keep feeling the need to couch all of your statements with theoretical arguments -- either you agree or you disagree.  I don't care if some theory agrees with me or not -- this isn't a peer-reviewed scientific journal, and no one needs to provide written evidence of their opinions & experience.

[ebmigue]

Is my method of explaining my answers inherently problematic and offensive to certain persons, and is extremely unprofessional - in short, violating the ToS?

 

If not, I will go on with it, if you don't mind.

 

If so, please explain to me rationally and reasonably through PMs.

[AyKay47]

Is my method of explaining my answers inherently problematic and offensive to certain persons, and is extremely unprofessional - in short, violating the ToS?

 

If not, I will go on with it, if you don't mind.

 

If so, please explain to me rationally and reasonably through PMs.

stop talking like a literature major.

Then MySQL is not user friendly.

In certain circumstances, I entirely agree -- the parser is far too forgiving and lets novice users walk down the primrose path without so much as a warning.

 

DISTINCT is mean to remove duplicate rows -- but that obscures the intent of the query in ways that GROUP BY does not.

 

I'm not sure why you keep feeling the need to couch all of your statements with theoretical arguments -- either you agree or you disagree.  I don't care if some theory agrees with me or not -- this isn't a peer-reviewed scientific journal, and no one needs to provide written evidence of their opinions & experience.

 

[ebmigue]

Last time I checked, most beginners will like explanations of the suggestions given to them.

 

And now...I just get the feeling that doing so is discouraged.

 

[fenway]

Is my method of explaining my answers inherently problematic and offensive to certain persons, and is extremely unprofessional - in short, violating the ToS?

I never said any such thing.

 

If not, I will go on with it, if you don't mind.

 

If so, please explain to me rationally and reasonably through PMs.

What's frustrating is that I'm not having a discussion with you -- I'm having a discussion with your "theories" -- and that's just down-right unpleasant.

 

Besides, I've tried everything in my power to explain to you my point of view "rationally" -- but since you dismiss all of my claims outright, since my experience isn't sufficient for you to consider, I have nothing left to say.

 

I won't derive any benefit from trying to get you to see how things work in the real-world -- I don't think I'll succeed -- so I'll save for efforts for the rest of the users here, who might actually choose to listen.

 

Last time I checked, most beginners will like explanations of the suggestions given to them.

 

And now...I just get the feeling that doing so is discouraged.

Of, I entirely agree -- explanations + solutions are preferred to solutions alone; but not to theoretical hand-waving, smoke & mirrors.

 

If you're feeling discouraged, I can't help you -- that's not my field of expertise -- I'm just trying to suggest that there are various ways of being helpful, and confusion isn't one of them.

 

At any rate, I'm exhausted by my ongoing attempts at rational explanations with you -- I will no longer contribute to any thread in which you have posted . Feel free to lecture the general public about theory to your heart's content.  I'm done.

[ebmigue]

I'm just trying to suggest that there are various ways of being helpful, and confusion isn't one of them.

 

On the contrary! I am using theory to clarify my solutions, and not confuse users.

 

 

At any rate, I'm exhausted by my ongoing attempts at rational explanations with you -- I will no longer contribute to any thread in which you have posted . Feel free to lecture the general public about theory to your heart's content.  I'm done.

 

If you noticed, I am dragged into "defending/ranting/theorizing" only because my suggestions are immediately deemed as "inefficient/non-parser friendly" - and all such implementation-related considerations.

 

Avoid that, and this endless exchange will stop.

 

I just want to help people, and if I had to argue w/ your posts, then so be it.

[premiso]

Now that the kitty's have decided to agree to disagree the pissing match should be over. Let's get back to the SQL at hand:

 

SELECT ds.id FROM detail_sheets ds
WHERE ds.id NOT IN (
    SELECT ds2.id          
    FROM 
          detail_sheets AS ds2            
          JOIN enquiries AS eq ON eq.detail_sheetRef = ds2.id
          JOIN jobs AS jb ON jb.enquieryReg = eq.id
   WHERE 
          ds2.status = 'Customer'
)

 

I just felt like optimizing it a bit for ya. I am not sure why the DISTINCT was used or even brought up, the original question did not suggest it was returning duplicate rows. If it is, then a GROUP BY id  should be used instead. Hopefully this helps ya out imperium2335. Let me know if it doesn't work or you need extra help.

 

EDIT: Changed NOT EXISTS to ds.id NOT IN, perhaps this may kick it into gear.

[.josh]

ebmique, the problem is that theory is great as a tool to help you get your bearings straight, but it isn't what makes the world turn.  What should work (theory) does not always equal what does work (reality). 

 

We know you're just trying to help, but most people on forums like these aren't here to learn principles per se, but to figure out how to solve their real working problems.

 

Spend less time in the classroom or with your nose in books and more time in the real, working world, where the real, working problems actually exist, and you will see where fenway is coming from. 

[kickstart]

Hi

 

Is the subselect necessary?

 

Possibly just use left outer joins and check for one of the jobs columns being null

 

Something like this

 

SELECT detail_sheets.id 
FROM detail_sheets
LEFT OUTER JOIN enquiries ON enquiries.detail_sheetRef = detail_sheets.id 
LEFT OUTER JOIN jobs ON jobs.enquiryRef = enquiries.id 
WHERE detail_sheets.status = 'Customer'
AND jobs.id IS NULL

 

All the best

 

Keith

[ebmigue]

ebmique, the problem is that theory is great as a tool to help you get your bearings straight, but it isn't what makes the world turn.  What should work (theory) does not always equal what does work (reality). 

W/o Codd's theory about relations (i.e., the Relational Model), IBM could not have created the first DBMS. No first DBMS, no second, third, fourth DBMSes. No MySQL, no PostgreSQL, no Oracle, no SQL, etc. And this products now made our world turn. Did theory had something to do about it? Absolutely yes.

 

The point is, we ought to know what should work (theory). If it does not equal to what we actually have, to the current technology - still, we must be aware of it lest we limit the possibilities of what we already have. Again all this is because of practicality - to save time, money, and effort.

 

We know you're just trying to help, but most people on forums like these aren't here to learn principles per se, but to figure out how to solve their real working problems.

I know. Like I said, wouldn't it be nice if I too would provide the theoretical basis of my solution? If the OP is not concerned w/ it, he _can_ ignore it. I certainly will not force anyone - novice or experienced - to read them.

 

You see, most questions here -- especially from the novice -- are like "I am dividing 1 by 0. It gives me an error. Please help...?". So, what is the best thing to do? Rather than tell him about compiler settings and details, why not tell him about the axioms in arithmetic? If there is an opportunity, wouldn't it be nice to "get matter straight, and get to the bottom of things?". It would save us some time and effort - a lot of it.

 

The important thing is that s/he is informed, that there is now available "new material". All for knowledge and practicality's sake.

 

 

Spend less time in the classroom or with your nose in books and more time in the real, working world, where the real, working problems actually exist, and you will see where fenway is coming from.

I had read books because I am "in the real, working world, where the real, working problems actually exist," and I am from "where fenway is coming from."

 

We just differ in methods.

 

I understand it, really. I, too, had ignored theories for years.

 

And yet when I "discovered" it, I realised the time time and effort I've wasted. There I was struggling w/ problems that could have been avoided had I applied what was in books for so many years.

 

The point is this: I had no problem w/ the nature of most of the suggestions, w/c is DBMS-specific and has special emphasis on performance. I am fully aware that performance is a great feature and is a real requirement. Make no mistake about it.

 

But it is not only performance that one should be concerned about. Conceptual integrity, precision are also very real requirements. So, sure, you guys can worry about performance, and I'd be posting the conceptual/theoretical basis of the solutions to their problems/posts.

[.josh]

Yes I've already read your "Without theory there would be no..." Argument, and I anybody else is arguing against that; not saying theory doesn't serve a purpose.  But most people do not need to know theories and underlying principles behind everything, nor is it particularly useful from a practical point of view. 

 

Take OOP for example...the whole point of that way of thinking is to abstract away details and just know that something does what it says on the box.  Builders don't ponder the composition of materials or tools, or focus on thy whys of this or that.  They focus on learning but rather focus on the materials they have, what they are for, and get to building.  Around here you mostly interact with builders, not architects or scientists who develop the base tools. 

[.josh]

My point is...when someone says "I am cold, how do I make a fire?". Sure, someone out there had to sit there and think about things like friction and energy transferance and all that jazz, but responding with the whys and hows of the fire warming them etc.. isn't useful; telling them to rub two sticks together is.

 

You can easily spot the difference between someone wanting a solution to a problem vs. Someone looking for better ways to do things, by what they ask.

 

"I need a query that will give me xyz" == "I need a solution to a problem"

 

"I have this query that gives me xyz, but what can I do to improve it?" == gimme theory 

[ebmigue]

...but rather focus on the materials they have, what they are for, and get to building.

How will the builders know what _are_ the "materials they have", "what they _are_ for.."?

 

Who will tell them? The vendors of DBMSes?  But then who defined the basic components of a DBMS - namely, tables, views, and functions?

 

Theory defined those things.

 

The vendors implemented it. (But the story didn't end there. Some vendors actually mistakenly implement it. Thus confusion is now reigning. Some vendors implemented a hammer like a saw, and vice-versa.)

 

That is the point: at least get a basic acquaintance of theory, then and only then, one can really see what are the potentials and the proper use of one's tools.

 

If one does not, well there is the possibility that a hammer will be used like a saw and vice-versa. Imagine the resulting work of such a builder, who uses his hammer as a saw and the saw like a hammer.

 

You can easily spot the difference between someone wanting a solution to a problem vs. Someone looking for better ways to do things, by what they ask.

Why of course, yes.

 

"I need a query that will give me xyz" == "I need a solution to a problem"

AFAIK, I did have some posts where I didn't "inject useless theories".

BUT: there are problems/questions/posts where the _direct cause_ is lack of knowledge of relational theory.

(say a user who wonders why joining a non-empty table and an empty table results to an empty table, and is very "frustrated" already.).

 

That is where I will "inject theories."

 

"I have this query that gives me xyz, but what can I do to improve it?" == gimme theory 

if "improve" is performance-wise, theory has little to say because by then the problem is product-specific.

 

if "improve" is precision-wise / db-design-wise, theory has a lot to say.

[.josh]

No, you are still missing the point.  You cannot mix "proper" and "possibility" like that.  "Proper" means that possibilities are already thought of, the design has been made, boundaries have been set, a role has been filled.  "Possibility" means those things have not been done yet, or could be done more.  That does not mean that an established tool cannot be thought to be used for something else.  It just means that "I need to put these two pieces of wood together with a nail.  The hammer will do that for me.  I don't need to know or even think about what else I can use this hammer for, because I'm trying to nail two pieces of board together, not saw something in half, nor do I need to know that I could somehow repurpose this hammer to be a saw, because that is not what I am doing now."

 

 

[ebmigue]

And yet I am saying that the "proper" things are still "possibilities."

 

And yet I am saying that if it is not clearly stated that a hammer is a hammer and a saw is a saw, there is that chance they will be misused.

 

But we can do that, if we know theory, because by then we can avoid pitfalls and how to use the relational DBMS.

 

Sure it's a bit demanding -- time and effort (maybe even money) -- but who says that it must be right now?

 

I am merely suggesting things, putting some ideas.

 

Whether or not they will be heeded to - I have no control.

[AyKay47]

is this argument really still going on? there needs to be a combination of theory and reality in all aspects of life..nuff said

[premiso]

ebmigue, you have already proven yourself an idiot. No need to further convince us.

 

Thanks!

[ebmigue]

is this argument really still going on? there needs to be a combination of theory and reality in all aspects of life..nuff said

That's what I had been stating all along, and I agree that 'nuff is said.

 

But what is happening is that most replies to questions are only directed on performance

and is product-specific, when in reality theory alone could put the nail in the coffin, so to speak.

 

And the problem is, when I try to point that out, I am met w/ hostile comments, such as this:

 

ebmigue, you have already proven yourself an idiot. No need to further convince us.

 

Thanks!

 

W/c is very unhealthy.