CODEWITHSUNDEEP
mysqlsqljoin
simon
simon

Reputation: 2387

Joining tables together Mysql 3 times

I have 4 tables. They are customers, items, images & specs. The goal is to join those 4 tables together. Right now I'm combining items and images like this:

SELECT item_id, item_name, item_price,images_id, GROUP_CONCAT(images_fullname) AS images, images_itemid
FROM items
LEFT OUTER JOIN images ON item_id=images_itemid

But what is the best way to join the third table specs? The specs contains: spec_id, spec_itemid, spec_name, spec_value (specs is specifications of a specific item). I guess the best practice is not to do a dublicate of Left Outer Joine once again?

The layout I need is following:

Item name 1
image1, image2, image 3 (is working now with concat)
spec_name, spec_value
spec_name, spec_value
spec_name, spec_value

Item name 2
image1, image2, image 3 (is working now with concat)
spec_name, spec_value
spec_name, spec_value
spec_name, spec_value

Upvotes: 1

Views: 78

Answers (2)

Matthew
Matthew

Reputation: 9949

If I under stand your request correctly, it is that you have a set of specs in the spec table for each item you select?

If this is the case you have a few choices:

  1. Get everything in a single query getting the multiple records per item (one row per image / spec / etc) and then sort this out in code (very ugly)

  2. Use a subquery and group concat the specs like you are currently doing with images (I think this is also messy and will be more so in the key-value pair setup you have for your specs.

  3. Make multiple calls to the DB to build up your object in code <-- this is how I think you should handle it, if I am guessing your data model correctly.

I am assuming you have an item which has in addition to standard item fields:

  1. a number of pictures
  2. several key-value pairs which specify item specific properties

And the thing you are trying to create is not unlike a shopping site?

In your shoes I would have several separate queries to do this (pseudo-code):

 items = SELECT item_id, item_name, item_price,images_id FROM items

Then in a foreach loop for these items, if using an OO language you would populate your lists in the Item object in the loop.:

foreach (item in items) {
   SELECT images_fullname, images_itemid FROM images WHERE item_id=images_itemid;
   // put into a list of type image in the item object
   SELECT * FROM specs WHERE item_id=spec_itemid;
   // put into a list of type spec in the item object 
}

If the concern is to reduce DB calls and merge this info in code, (i.e. you are fetching thousands of items) then you end up doing something like your joins but to fetch the (for example) the images for all items you are interested in with one query and mapping them to the items in code.

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191809

Outer joins are only necessary when the joined table can be null. It's not clear whether or not that's the case here, but just keep that in mind.

SELECT
    various_columns
FROM
    items
    LEFT JOIN images ON item_id = images_itemid
    LEFT JOIN specs ON item_id = spec_itemid

There's nothing wrong with adding another join; you are still joining on a key column (I assume).

Upvotes: 3

Related Questions