Query Help

[codemonkey123]

Hi, I'm trying to figure out the best way to do this query. I have a table setup for an ordering page like this.

table 1: order_items
item_id width height media price

table 1:
25 10 20 transparency 10.00

table 2: graphics
graphic_id graphic_name item_id

table 2:
10 Jon 25
11 Tim 25

so there are two graphics for this particular item and I would like to display it something like this below, what would be the best way to query this info from the databases?

item data......
graphic 1 info....
graphic 2 info....

any help would be appreciated.

[ZydoSEO]

I'm not sure about MySQL but in MS SQL*Server you can have a stored procedure return multiple record sets. So your stored procedure could contain two selects whose results get returned in separate record sets/result sets similar to:

SELECT item_id, width, height, media, price FROM order_items WHERE item_id = #*$!
SELECT * FROM graphics WHERE item_id = #*$!

Of course, you can get all of the data in one select but the order_item data will be repeated for each row.

SELECT oi.item_id, oi.width, oi.height, oi.media, oi.price, g.graphic_id, g.graphic_name
FROM order_items oi
JOIN graphics g on oi.item_id = g.item_id
WHERE oi.item_id = #*$!

Which approach is best would depend on things like the average number graphics rows per item and the number of fields in the two tables and how often these queries are triggered.

Assuming your DBMS supports returning multiple result sets from a single stored procedure, using a stored procedure is probably the most efficient method. At least in MS SQL*Server, the query plan for your stored procedure can be pre-compiled. You make a single round trip to the DB by calling a single stored procedure and return no duplicate data. If there are 1000 graphics for an item, you get back one copy of the item data and data about 1000 graphics.

If it's not used that often or you are not familiar with stored procedures, then the single select approach (2nd one described above) should suffice. It returns duplicate item data for every graphic after the first. So if you have 1000 graphics, it will return 1000 duplicate copies of the same item data as well as 1000 graphics data. But if you a small number of graphics on average per item, then it's likely ok to return duplicate item data.

A third approach would be to simply make 2 round trips to the DB by first selecting the item data and then calling a second select to get the graphics for the items (basically, calling the same statements that you would have called in a stored procedure... but calling them one at a time from you PHP or ASP code).

[edited by: ZydoSEO at 2:20 pm (utc) on Oct. 13, 2008]

[codemonkey123]

Ah ok, I'll look into the stored procedure and see if that's possible. In reality, the amount of graphics per item isn't very high, however, it would be nice to be able to return larger amounts of data if I would like.

[LifeinAsia]

Depending on what language you're using on your front end, you can also just use 1 query for all the data and have nested loops to group the data by item then by graphic info. (It's trivial in Cold Fusion, but I don't know other languages we enough to suggest how to do it.)