SQL problem (sort of pivoting data)

komodosp

Hi folks

I'm racking my brains over this one but can't come up with a solution.

I have two tables containing user data in MySQL and I need to come up with a way of being able to remotely view and update the data in one table using and ODBC connection.

The first "user" table just has login info (ID, username, password).

e.g.
234, "komodosp", "selie03"

The second "usermeta" table has all the other info (firstname, lastname, tel, address1, address2, etc.) but is stored as one record per field.

So a record would have
rowID, UserID, FieldName, FieldValue (containing for example)
1, 234, "firstname", "Spodo"
2, 234, "lastname", "Komodo"
3, 234, "tel", "06123049"

etc.

Don't ask me why it was set up like that I have no idea and I can't change it now. But I need to see in the ODBC connection as one row per user.

And I can't use triggers (I thought about setting up a third table to hold the data together and having any updates triggering updates in the "proper" tables and vice versa")... the type of account for reasons unknown doesn't allow it.

I tried setting up a View, but it uses subqueries to get the data from the second table, and this makes it non-updateable.

Any ideas anyone?

Thanks!!

Freddio

Hi,

if you want to look at the two rows in one row, you need to perform a table join. Unfortunately you cant update two tables with the one statement though.

Freddio

Have you looked at stored procedures

voxpop

Actually - didnt read your post fully OP. You can prob use groupby clause and case to pivot the data - this is in MSSQL, im assuming mysql is the same

Otherwise as suggested - use a stored procedure and a temp table

komodosp

Basically I'm trying to set it up so a non-developer can view the data easily.
I would have thought a join would have given me something like

234, "komodosp", "selie03", "first_name", "Spodo"
234, "komodosp", "selie03", "last_name", "Komodo"
234, "komodosp", "selie03", "tel", "06123049"

Whereas what I want is more like:
234, "komodosp", "selie03", "Spodo", "Komodo", "06123049"

I have thought about stored procedures... However, the user doesn't seem to think he could do this through and ODBC connection from Access.

voxpop

Just threw this together as an example

declare @table table( 
	id int,
	fname varchar(10),
	fval varchar(10)
)

insert into @table values (1,'first','bob')
insert into @table values (1,'second','bobson')

select * from @table
RESULT : 
id          fname      fval
----------- ---------- ----------
1           first      bob
1           second     bobson


select id,
min(case when fname='first' then fval end) as 'firstname',
min(case when fname='second' then fval end) as 'lastname'
from (select * from @table where id = 1) tab
group by id

RESULT:
id          firstname  lastname
----------- ---------- ----------
1           bob        bobson

fenderlvr

Another way to do it...

declare @table1 table 
(
	ID int,
	username varchar(20),
	password varchar(20)
)

declare @table2 table
(
	rowID int,
	UserID int,
	FieldName varchar(20),
	FieldValue varchar(20)
)

insert into @table1 (id, username, password) values (234, 'komodosp', 'selie03')

insert into @table2 (rowID, UserID, FieldName, FieldValue) values (1, 234, 'firstname', 'Spodo')
insert into @table2 (rowID, UserID, FieldName, FieldValue) values (2, 234, 'lastname', 'Komodo')
insert into @table2 (rowID, UserID, FieldName, FieldValue) values (3, 234, 'tel', '06123049')

select
	a.ID,
	a.username,
	a.password,
	b.FieldValue as firstname,
	c.FieldValue as lastname,
	d.FieldValue as tel
from @table1 a 
	left join @table2 b on a.ID = b.UserID and b.fieldname = 'firstname'
	left join @table2 c on a.ID = c.UserID and c.fieldname = 'lastname'
	left join @table2 d on a.ID = d.UserID and d.fieldname = 'tel'

I'm using SQL Server so you might have to change it slightly for MySQL