18 replies to this topic

[BASHERS33]

In MYSQL when would it be best to use separate queries in place of one query with a lot of joins?

My mom said that in DB2 really only "unusual" queries needed to be done as multiple queries rather than one with a joins.

[WingedPanther]

It really depends a lot on the nature of the data and the purpose of the joins. I've done both. For me, the most common reason to use separate queries is when I want the first X entries from a child table for each item in the parent table.

[BASHERS33]

In most of my cases what I am doing is getting either counts, maxes, averages, or just flat being sure a row even exists in the child table under the conditions I am looking for. So it's always grouped by some id in the main table and then simply adding a few fields to the row. In fact I didn't even know you could get x number of rows from another table to go along with a row from the main one. Since it returns as one row. Does it return it as one row, but with multiple rows of another table as an array or something?

I am doing this all based around a forum software's functions, so I really only learned a little mysql since the forum software's functions does the actual work such as escaping and setting it all up. So I know A and C and not B too well. lol

Also in some cases I am only going to have one row PERIOD. From the main table. But in most I will have x number of rows so if I did break those into multiple queries I would have to use some associative array to put them back together when displaying I guess.

[WingedPanther]

BASHERS33 said:

In most of my cases what I am doing is getting either counts, maxes, averages, or just flat being sure a row even exists in the child table under the conditions I am looking for. So it's always grouped by some id in the main table and then simply adding a few fields to the row. In fact I didn't even know you could get x number of rows from another table to go along with a row from the main one. Since it returns as one row. Does it return it as one row, but with multiple rows of another table as an array or something?

I use separate queries for that situation because a join would give multiple rows.

BASHERS33 said:

I am doing this all based around a forum software's functions, so I really only learned a little mysql since the forum software's functions does the actual work such as escaping and setting it all up. So I know A and C and not B too well. lol

Also in some cases I am only going to have one row PERIOD. From the main table. But in most I will have x number of rows so if I did break those into multiple queries I would have to use some associative array to put them back together when displaying I guess.

Something you may want to look at is: http://forum.codecal...-sql-joins.html

[BASHERS33]

You mean for what i was tlaking about at the end of the paragraph? When you just need an average or count to go along with it, you would use a join wouldn't you? When I am going to have multiple rows yes I do ane xtra query instead.

[WingedPanther]

I've created some fairly complicated queries. Sub-sub-selects get nasty. The whole trick is to think about what you're trying to accomplish, and what will represent the data you care about.

[BASHERS33]

I think what's going to happen is as I elarn mroe I will just go back and improve my queries. At elast queries can be improved later on. What gets more tricky is if I make a bad decision in what's stored in the DB and later think hey I want something else stored there or in a different way. So I guess I'm ok for now and will just try to improve the performance as I learn more.

If someone sees my code they are going to probably cry though. I don't even line up brackets properly sometimes and I usually have the { on the same line as the if() instead of below it. I learned some bad habits by someone else.

[WingedPanther]

No time like now to start fixing those habits. I use a text editor that makes changing indentation easy (alt-right-arrow, alt-left-arrow in jEdit). That way, if I copy some code that needs to be indented, I just highlight the block and move it.

[BASHERS33]

I hate how it looks when the opening bracket is not on the same line. :( I guess I better get used to doing it the other way though because if someone looks at the coding they would be a little more confused. I personally am rarely confused by how I do it though.

I think the text editor I have has some sort of bracket matching. I have never used it yet though.

[WingedPanther]

In The Elements of C++ Style, rule 7 is "Choose one style for brace placement." The two common versions are open brace at end of line:

if (condition) {
  //do something
}

or open brace on new line:

if (condition)
{
  //do something
}

I prefer the second, mainly because I started programming in Pascal. It used BEGIN and END instead of { and }, so using BEGIN on new line looks better to me. Just pick one and go with it.

[BlaineSch]

I have always prefered the first style - I started in PHP and I believe that is a PHP standard for most websites. I never learned aligning I just thought it was how it was done. When I started other languages I had a rude awakening lol.

Indent style - Wikipedia, the free encyclopedia

With JOIN VS New Queries

I think it was mentioned before but yea just depends how its stored and how you want it outputted - the best way to go is less queries just because its faster. But if you cant you cant.

[BASHERS33]

ok well I got used to doing #1, but I definitely think #2 makes it a little easier to quickly notice what you're looking for when scanning through some code.

Man I have sat here and not coded much the past few days because I am dreading soemthing. I am going to totally redo the mains etup in one of my programs and right within a week of when I want to have the program finished. So now I may introduce bugs while trying to make things more optimal. Sigh.

And why I brought up brackets in here, I don't know. I must have thought I was posting in the php forum when I mentioned that.