How to reference field names in this query 1

[Recordsetclown]

Hi folks,

The following query gives me a list of all field names and values associated with each listingsdb_id value in my table.

SELECT r2.listingsdbelements_field_name as field, r2.listingsdbelements_field_value as value
FROM default_en_listingsdbelements AS r1
INNER
JOIN default_en_listingsdbelements AS r2
ON r2.listingsdb_id = r1.listingsdb_id

How can I reference specific field names, e.g., streetnumber, streetname, from that result to concatenate them into a complete address?

Thanks for any help.

 

[r937]

not easily

could you do a SHOW CREATE TABLE just to confirm my suspicion that this is another example of the dreaded EAV (entity-attribute-value) design anti-pattern

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

 

[Recordsetclown]

Hi,

This is what it returns:

default_en_listingsdbelements
Field Type Null Default Comments
listingsdbelements_id int(11) No
listingsdbelements_field_name varchar(80) No
listingsdbelements_field_value text No
listingsdb_id int(11) No
userdb_id int(11) No


Indexes: Keyname Type Cardinality Field
PRIMARY PRIMARY 9673 listingsdbelements_id
idx_name INDEX 37 listingsdbelements_field_name
idx_value INDEX 3224 listingsdbelements_field_value 255
idx_listing_id INDEX 403 listingsdb_id
idx_fieldmashup INDEX 9673 listingsdbelements_field_name
listingsdb_id


Space usage: Type Usage
Data 538,044 B
Index 496,640 B
Total 1,010 KiB
Row Statistics: Statements Value
Format dynamic
Rows 9,673
Row length ø 55
Row size ø 107 B
Next Autoindex 47,595
Creation Dec 10, 2009 at 11:40 PM
Last update Jan 18, 2010 at 01:05 PM
Last check Jan 20, 2010 at 10:45 AM

 

[r937]

you'll need one join per column to be returned

SELECT r1.listingsdbelements_field_value as streetnumber  
     , r2.listingsdbelements_field_value as streetname
     , r3.listingsdbelements_field_value as city  
  FROM default_en_listingsdbelements AS r1
LEFT OUTER
  JOIN default_en_listingsdbelements AS r2    
    ON r2.listingsdbelements_id = r1.listingsdbelements_id
   AND r2.listingsdbelements_field_name = 'streetname'
LEFT OUTER
  JOIN default_en_listingsdbelements AS r3    
    ON r3.listingsdbelements_id = r1.listingsdbelements_id
   AND r3.listingsdbelements_field_name = 'city'
 WHERE r1.listingsdbelements_field_name = 'streetnumber'

EAV is deceptively simple to store data into, and horribly complex and inefficient to retrieve information from in a meaningful way

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

 

[Recordsetclown]

Thanks so much for your help. I certainly appreciate it.

 

[Recordsetclown]

Running this query results in a populated left column, but NULL in every value of those columns to the right. Do you see why?

 

[r937]

if you could show the actual rows of data in the table, i could figure it out

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

 

[Recordsetclown]

When I looked again at the table, I saw that r# joined r1 on listingsdbelements_id rather than listingsdb_id. With that changed, it worked fine. Thanks again.

 

[r937]

oh yes, you're right, i'm sorry, that was a copy/paste typo

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