Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

INNER JOIN question

 
Timy McTipperstan
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Origianally I was doing this query as three seperate select statements, I have trimmed, poked and pushed them down to one. However I didnt think of the potential issue. Or maybe I am over thinking it.



What this will return is the information for productid number 27, plus its related product. 1 row.
Next you'll see that its looking for sizes which is in another table, that will return 4-10 rows. But because that its in the query what I get is 4-10 rows of the sizes and 4-10 rows of productid number 27.

Now my question is that okay, will that cause issues, is that not good form, my first thought is no that isnt okay so you should do a second query to get the size information.

Anyway your thoughts are appreciated.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Timy,
You should do the two separate queries. The size information is really a logically separate request. This makes the queries clearer and more maintainable.

Interestingly, it doesn't necessarily make them slower. Sometimes complex queries take longer because the database has to do unnecessary work.
 
Steve Lee
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know Java, yet, but I do know SQL. I'd suggest keeping them separate, for maintainability and scalability. It's easier to read, and less grief to add fields or tables, if they're separate queries. To me, a big benefit of object oriented is small, easily maintained units of work...why would we send our SQL in the opposite direction?

I'm guessing you had something like the following, and I think it's a lot more legible...

//* Get product information *//
SELECT
productid,
name,
action,
lg_picture,
description,
short
FROM
products
WHERE
productid = '27'

//* when we get the product, *//
//* get related product name *//
SELECT
name
FROM
products
WHERE
short = '27'

//* we've got the product, *//
//* get the product sizes *//
SELECT
abrv
FROM
sizes S, prod_size PS
WHERE
PS.sid = S.sid AND
PS.productid = '27'

Hope that helps!
Steve
 
Timy McTipperstan
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I broke them up in to two pieces, I agree that the sizes was a seperate step and therefore didnt need to be in the first one.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic