List:General Discussion« Previous MessageNext Message »
From:Lech Buszczynski Date:November 25 2009 11:12am
Subject:sql query question that puzzles me
View as plain text  
Hi,

This thing puzzles me for quite some time and I wasn't successful in
finding a clear answer anywhere - I would be grateful for some
help.

Here is a db example:

table_1
id
some_field_01
[...]
some_field_20

table_2
itemid (table_1_id)
value

Let's say that the table_2 is used to store some properties of the
item in table_1, there can be many of them (let's say these are just
integers values - not that important in this example).

What I'd like to get is the item from table_1 and the item properties
from table_2, I can do 2 things:

1:
on table_1
SELECT * FROM table_1 WHERE id = SOME_ID
then on table_2
SELECT value FROM table_1 WHERE itemid = SOME_ID.from.table_1

so I get one row from table_1 and many rows from table_2

2:
on both tables:
SELECT tb_1.*,tb_2.value AS property FROM table_1 AS tb_1, table_2 AS
tb_2 WHERE tb_1.id = SOME_ID AND tb_1.id = tb_2.itemid

so I get many rows with one query. The question is, which way is
better if these requests may be executed a large number of times?

I think 1 query is better than 2 but if the table_1 holds a lot of
data - resending the whole thing every time (example 2) just to get the
one integer value seems like a waste of resources.

Or is there a better way to do it? Could someone enlighten me? Thanks!


--
Regards,

Lecho


Thread
sql query question that puzzles meLech Buszczynski25 Nov