If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Help collating multiple records from multiple tables into a single record

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 11-14-09, 20:44
oorin oorin is offline
Registered User
 
Join Date: Nov 2009
Posts: 2
Help collating multiple records from multiple tables into a single record

That is probably a horrible description of what I need.

What I have is a two tables, one that contains "field" information. This table was designed to allow a web site administrator a high level of configuration for collecting data.

Table Layout:

id int(10)
type varchar(255)
name varchar(255)
tips text
visible tinyint(1)
required tinyint(1)
searchable tinyint(1)
options text
fieldcode varchar(255)

The other table simply contains the values for these configurable fields:

id int(10)
user_id int(11) -> user data contained in another table
field_id int(10) -> ties to id field in previous table
value text

Now what I want to do is create a tmp table based off of search criteria... but I want to collate the user data into a single record using the fieldcodes from the first table as columns. Because as you can imagine, if the admin has created 13 fields each user has 13 entries in the "values" table.

Can this be done with SQL or does it have to be done using multiple steps with a secondary processing language (in my case php)?

Thank you very much for your help.
Reply With Quote
  #2 (permalink)  
Old 11-14-09, 21:16
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
Quote:
Originally Posted by oorin View Post
This table was designed to allow a web site administrator a high level of configuration for collecting data.
while at the same time no consideration for how hard it would be to extract meaningful information

do a search for EAV (entity-attribute-value)

you will find a litany of woe, and warnings from experienced database designers to avoid this scheme if possible

you're gonna have to write some pretty slick application logic to produce the reports you want
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 11-14-09, 21:50
oorin oorin is offline
Registered User
 
Join Date: Nov 2009
Posts: 2
Oh believe me... I know. I unfortunately joined the team late... this also means I'm not allowed to change what the previous developer did.

So what you're telling me - if I understand - is I need to go outside let out a primal scream and handle this outside of a SQL query.

Thank you for replying.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On