Is this some sort of join? 1

[Recordsetclown]

I'm working with a mysql table that includes columns field_name, field_value, and indexnumber. The indexnumber is common to all rows within a single record, one row per field.

One of the fields is an agency code, and I want to find every row with that field, compare its value to my target, and if the comparison fails, use the corresponding indexnumber to insert into the shortdescription field a trimmed version of the full_description field from that record.

This is well beyond my understanding of sql, so if anyone can point me in the right direction, I would be most grateful.

 

[r937]

target?

are there two tables here? it's kind of vague

perhaps you could do a SHOW CREATE TABLE for it/them, to help us understand

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

 

[Recordsetclown]

Sorry to be so vague. My brain has turned to mush since I've been thinking about this. Let me try to explain more clearly. This is from a real estate application into which listings from various realtors are downloaded. Regulations restrict the amount of information a realtor can display if the listing isn't from his/her office. All the pertinent information is in this one table.

I need to read down the field_name column until I find a row with officeidnumber, check to see if the originating office is my realtor, and if not, use the indexnumber in that row to find other rows relating to the same property listing. One of those rows will contain a full_description field with a long property description. I need to put a truncated version of that description into the short_description field in another row with the same indexnumber. The indexnumber is what ties each row to the correct property listing, however, there is no particular order to the rows that I can determine, and rows with different indexnumbers are intermingled.

Is there a way to say for each indexnumber in turn, if the officeidnumber matches mine, replace short_description with complete full_description, else replace short_description with left 120 characters of full_description?

Thanks for looking. I appreciate any help or direction.

 

[r937]

INSERT
  INTO daTable
     ( indexnumber , fieldname, fieldvalue )
SELECT r2.indexnumber, 'short_description', LEFT(r2.fieldvalue,120)
  FROM daTable AS r1
INNER
  JOIN daTable AS r2
    ON r2.indexnumber = r1.indexnumber
   AND r2.fieldname = 'long_description'
 WHERE r1.fieldname = 'officeidnumber'
   AND r1.fieldvalue = 'myoffice'

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

 

[Recordsetclown]

Thanks so much for your response!! After changing the operator to match all listings that aren't my broker's, everyone else got a truncated description.

Is it possible in the same query to give only my broker the full_desc in the shortdesc field, so I can just display that field and have either the truncated or full description as appropriate?

Also, is it possible to insert additional fields at the same time? Along with the shortened description, I need to display a logo and disclaimer statement. The logo field 'brlogopath' would hold the html and path to the logo file, and 'brdisc' would hold the disclaimer text.

This is the actual query as it is:

INSERT
INTO default_en_listingsdbelements
( listingsdb_id , listingsdbelements_field_name, listingsdbelements_field_value, userdb_id )
SELECT r2.listingsdb_id, 'shortdesc', LEFT(r2.listingsdbelements_field_value,120),1
FROM default_en_listingsdbelements AS r1
INNER
JOIN default_en_listingsdbelements AS r2
ON r2.listingsdb_id = r1.listingsdb_id
AND r2.listingsdbelements_field_name = 'full_desc'
WHERE r1.listingsdbelements_field_name = 'listingofficemlsid'
AND r1.listingsdbelements_field_value != 'xxxxx'

Again, thanks for looking at this. I would have never gotten it on my own.

 

[r937]

inserting additional fields at the same time will require additional INSERT statements



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

 

[Recordsetclown]

OK, thanks - I'll try it. Any ideas how to populate the shortdesc field with either truncated or long text depending on broker id?