Multiple selects in one query 1

[HowardMarks]

Dear all,

hopefully some one can help me with this, i've been banging my head against it all day.

My table structure is as follows:

listing_options:
loption_id, loption_key
1, "title"
2, "maindesc"
3, "mainimage"

listing_user_options:
lo_listingid, lo_loptionid, lo_value
1343, 1, "This is the title"
1343, 2, "This is the Description"
1343, 3, "This is the image"

listings:
listing_id
1343

So I want to query all of the listings and pull out any listing_user_options and, based on their associated listing_option, assign them to one results set

something like:

SELECT listings.listing_id FROM listings,

(lo_value FROM listing_user_options LEFT JOIN listing_options ON lo_loptionid = loption_id WHERE loption_key = 'title' AND lo_listingid = listings.listing_id) As Title,

(lo_value FROM listing_user_options LEFT JOIN listing_options ON lo_loptionid = loption_id WHERE loption_key = 'maindesc' AND lo_listingid = listings.listing_id) As MainDescription,

(lo_value FROM listing_user_options LEFT JOIN listing_options ON lo_loptionid = loption_id WHERE loption_key = 'mainimage' AND lo_listingid = listings.listing_id) As MainImage

ORDER BY Title

So my results set end up with

listing["listing_id"] = 1343
listing["Title"] = "This is the title"
listing["MainDescription"] = "This is the Description"
listing["MainImage"] = "This is the image"

I've tried all manner of joins, subqueries, table aliases etc but my lack of knowledge of more complex SQL is failing me. Could any one point me in the right direction?

Many thanks

Nick


Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org

 

[r937]

point you in the right direction? sure

your table design is overly complex

why not have actual columns called Title, MainDescription, and MainImage?

do a search for entity-attribute-value (EAV) to see why so many people think this is an anti-pattern instead of a good way to go

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[HowardMarks]

Hiya

I know that would be a way to simplify the design, and believe me I would have done it that way if it were possible.

The crux is that the system hinges on the ability for an administrator to create an unlimited amount of various fields, each with a type of short text, long text, boolean, image, etc etc which the end users who create the listings can populate. This is all part of quite a complex content management system.

So it's not viable to have a fixed set of fields built into the listings table.

Any more suggestions?
Many thanks
Nick

Nick Price

http://www.twoclicks.co.uk

 

[r937]

okay, i understand what you're saying, you want the administrator to add whatever columns the users want

good luck with this, because your queries are going to be ~way~ complex

SELECT l.listing_id 
     , luo_title.lo_value AS title
     , luo_maindesc.lo_value AS maindesc
     , luo_mainimage.lo_value AS mainimage
  FROM listings AS l
CROSS
  JOIN listing_options AS lo
LEFT OUTER
  JOIN listing_user_options AS luo_title
    ON luo_title.lo_listingid = l.listing_id
   AND luo_title.lo_loptionid = lo.loption_id
   AND luo_title.loption_key = 'title'
LEFT OUTER
  JOIN listing_user_options AS luo_maindesc
    ON luo_maindesc.lo_listingid = l.listing_id
   AND luo_maindesc.lo_loptionid = lo.loption_id
   AND luo_maindesc.loption_key = 'maindesc'
LEFT OUTER
  JOIN listing_user_options AS luo_mainimage
    ON luo_mainimage.lo_listingid = l.listing_id
   AND luo_mainimage.lo_loptionid = lo.loption_id
   AND luo_mainimage.loption_key = 'mainimage'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[HowardMarks]

Hello,

Thanks very much for your help.
I decided to make life a bit simpler for myself by storing a copy of the key from the listing_options table in each listing_user_options row. This allowed me to dispense with the cross join to the listing_options table and simplifies the structure somewhat. It is working great now.

In regards to the EAV model - thanks for enlightening me on this subject, I had not realised it was such a contentious issue until I did some background reading on it, as you suggested. I understand the negative implications of using such a model, but I think in this particular application it is only a small part of the overall structure, and the benefits of its flexibilty outweigh the performance issues (it has no requirement to be particularly scaleable either).

Thanks again for your help
Nick

Nick Price

http://www.twoclicks.co.uk