relationship with join and select

[Jaynesh]

Hello

 

I am very confused and frustrated here. What relationship does the columns in SELECT have with the actual JOIN query.

 

Ive got these three tables in my SELECT

dbUsers.*, dbPosts.*, dbKarma.*

 

but when I add a fourth table

dbUsers.*, dbPosts.*, dbKarma.*, dbPosts_share.*

 

the whole query stops outputting results.

What relationship does the SELECT have with the JOIN query?

Why would this effect the query?

As far as I know, SELECT is for the actual output in the loop?

[thebusiness]

I would like to know this as well, I am having the same issue, once it joins it stops giving results.

[ebmigue]

I suggest that you learn Relational Algebra (or predicate calculus). Then learn SQL. Then learn the "commonality" between the two.

 

That is the only way for you to truly understand what you are doing.

 

Of course, giving you "a quick tip" is good, but I'm thinking "long-term."

 

Hope it helps.

[fenway]

I was going to lock this topic, because once again Jaynesh has posted duplicate topics, but since there are replies already, I'll leave it for now.

 

@ebmigue: programmers don't need to learn calculus or algebra to use SQL.  In fact, no one does outside of academia. 

 

That's like pretending that I can't turn on a computer without understanding the quantum mechanical aspects of a transistor.

[ebmigue]

@ebmigue: programmers don't need to learn calculus or algebra to use SQL.  In fact, no one does outside of academia. 

 

That's like pretending that I can't turn on a computer without understanding the quantum mechanical aspects of a transistor.

 

I guess we could that as "One of your opinions in w/c I disagree."

[ebmigue]

Ah, I didn't mean algebra or calculus (the "conventional" ones).

 

I meant that the OP should learn relational algebra (and/or relational calculus).

 

By "learn", I do not mean the extensive kind. A passing acquaintance might be enough.

 

Surely, again, if he wants to speak a language, he must be acquainted w/ the concepts w/c the language was intended in the first place.

 

Hope it helps.

[TeNDoLLA]

I have to agree with fenway. I've been programming years in PHP and mysql, and I am doing just fine in my job with them. And I have absolutely no idea what ebmigue is talking about (or maybe I do? Atleast I don't recognize a thing from the terms.. all this algebra and calculus bshit). Yeah I agree you need to know these things if you are for example a teacher in university or something like that. But don't think you MUST know otherwise them if you just want to be a programmer. Maybe some bits here and there, but not a whole theories about everything.

 

And I am sure the original poster is quite new to MySQL if he has problems with a simple JOIN in SELECT. I really don't think your post about all these theories will help a bit. And I am also pretty sure the OP will not look into them. A simple example code with explanations would probably be best help. Maybe he's goal is not to learn everything about everything and all about the background details. Maybe he just wants to make his one query work for whatever task.

[ebmigue]

Yes, I understand your point of views. I myself "bumped with" theory after I've been messing with SQL (MySQL for the most) for relatively a long time now. I do not argue your stance.

 

But.

 

Clearly, the OP is lost on where to proceed. He has no idea on what he is doing.

Yeah sure, manuals, examples, will help.

 

 

But he is asking about the "relationship of SELECT and JOIN", w/c in itself is a phrase that is too ambiguous and misguided that I don't

think a product manual exists to explain such question.

 

For all we know, all those manuals and the SQL grammar specified therein have relational algebra as their basis.

 

Wouldn't it be wiser to point him/her to its basis (relational algebra), rather than to the specifics (SQL as implemented by MySQL)?

I am just pointing to another option, another path to take, w/c, IMO, is more productive long-term-wise as I have now come to realise.

For what if he will encounter a different DBMS that is non-SQL? What then? If he knows the relational algebra, it wouldn't be that difficult to make a shift, so to speak.

 

If you guys found it easy and manageable to understand SQL even w/o knowing relational algebra ("and all that bshit"), maybe it was because of your collective experience w/ another language; or maybe you were just gifted individuals.

 

I do not want to take my chances with those "maybe's" w/ regards to this inquiry by OP; that is why I've pointed him to the conventional approach.

 

If you want your kids to know how to use calculators, surely, you must teach them arithmetic first, right? Rather than play that dangerous wager that maybe your kid will turnout a genius; or maybe he will come to know arithmetic by himself.

 

OP, if you'll learn relational algebra in two weeks. You'll save yourself of two years of mastering SQL's syntax. YOu'll understand SQL even if you only have a passing acquaintance w/ relational algebra. Trust me.

 

That was the idea of my suggestion.

 

Of course, I am not precluding the possibility that someone will help him the "non-theory" style; how could I?

 

Just pointing him to a possibility. That's all. I am not forcing anyone here to adhere to my thoughts/suggestions. All I can do is convince and persuade.

 

Thank you and hope it helps.

[ebmigue]

For the interested, here is wikipedia's entry for Relational Algebra (RA): http://en.wikipedia.org/wiki/Relational_algebra

(Note: the RA described in that article might be Codd's original algebra, proposed about 40 years ago. Of course, many improvements

have been done since then; consult the book by Date and Darwen (the Third Manifesto) for a detailed treatment of the topic.)

 

It is not that difficult to learn really. Similar to arithmetic, but instead of numbers, we are dealing with relations. Similar w/ set-theory, but instead of sets we deal with relations.

 

SQL of course has the operators that RA has, among them: UNION, JOIN, PROJECT (SELECT), RESTRICT (WHERE), TIMES (,), EXTEND (SELECT ,), RENAME (SELECT '' AS '')

 

In fact there are many other operators w/c MySQL failed to implement: MINUS, SEMIMINUS, COMPOSE, SUMMARIZE, TCLOSE, etc, etc.

 

W/c could have reduced the amount of code we are producing everyday had they implemented it.

 

See the significance?

 

So clearly, knowing them in an abstract basis will greatly help the novice user on how to use them in an actual basis (or maybe even the experienced user).

 

Thank you and hope it helps.

[fenway]

Actually, you just confused absolutely everyone reading this topic -- especially the novice user.

[ebmigue]

I don't know why you were able to say that, especially since you are NOT a novice user.

[fenway]

I don't know why you were able to say that, especially since you are NOT a novice user.

Because I've taught a great many people a great many things -- not just technological -- and bombarding novice users with abstract notions is just asking for trouble.  If you deny this, then you're in an ivory tower.

[ebmigue]

The teacher and the novice will never have the POVs.

[fenway]

The teacher and the novice will never have the POVs.

That's true -- but irrelevant.

[ebmigue]

Because I've taught a great many people a great many things -- not just technological -- and bombarding novice users with abstract notions is just asking for trouble.  If you deny this, then you're in an ivory tower.

 

Then since that was directed to the quote above, the content of the quote above must be irrelevant too -- all according to you.

[ebmigue]

Because I've taught a great many people a great many things -- not just technological -- and bombarding novice users with abstract notions is just asking for trouble.  If you deny this, then you're in an ivory tower.

I meant: The teacher and the novice will never have the same POVs.

 

Actually, you just confused absolutely everyone reading this topic -- especially the novice user.

Your claim that my posts/advice of acquainting oneself w/ relational algebra is confusing people is a subjective claim. Please avoid generalising your opinions for others. Let them think on their own.

 

Thank you and hope it helps.

[fenway]

I generalize when I feel it's appropriate -- others can "think on their own" when they read my advice.  I don't have to censor my opinions.  And you're welcome to refute them -- but arguing with my right to generalize isn't productive.