Join of Two Tables

[lyric0n]

Hello,

I have two tables that I am trying to join together to create a particular view, but I am have a difficult time coming up with the correct query.

One table looks like...

Uid | Login
1 | John
2 | Paul
3 | Sally

The second table looks like
Uid | Fid | Value
1 | 1 | john@sw.com
1 | 2 | San Francisco
1 | 3 | 2007
2 | 1 | Paul@sw.com
2 | 2 | Atlanta
2 | 3 | 1997
3 | 1 | sally@sw.com
3 | 2 | New York
3 | 3 | 2006

I need the resulting data to look like
Uid | Login | Email | HomeTown | YearGraduated
1 | John | john@sw.com | San Francisco | 2007
2 | Paul | Paul@sw.com | Atlanta | 1997
3 | Sally | sally@sw.com | New York | 2006

Thanks for the help!
Chris

 

[r937]

i urge you to reconsider that design

it's called EAV (entity-attribute-value) and it has severe drawbacks

select onetable.Uid
     , onetable.Login
     , max(case when secondtable.Fid = 1
                then secondtable.Value end) as Email 
     , max(case when secondtable.Fid = 2
                then secondtable.Value end) as Hometown 
     , max(case when secondtable.Fid = 3
                then secondtable.Value end) as YearGraduated
  from onetable
inner
  join secondtable
    on secondtable.Uid = onetable.Uid
group
    by onetable.Uid
     , onetable.Login

complexity of queries, as i'm sure you discovered trying to do this on your own, being one of the drawbacks



r937.com | rudy.ca

 

[lyric0n]

I appreciate your response. When I saw the way these were setup, I was disappointed, but unfortunately, that is the way the application works and I can't change it

I have run into a new query with a bit more complexity and I was wondering if you could help. What if I wanted to see only users where Fid > 2000?

I tried the following, but it wasn't producing the correct results...

select onetable.Uid
, onetable.Login
, max(case when secondtable.Fid = 1
then secondtable.Value end) as Email
, max(case when secondtable.Fid = 2
then secondtable.Value end) as Hometown
, max(case when secondtable.Fid = 3
then secondtable.Value end) as YearGraduated
from onetable
inner
join secondtable
on secondtable.Uid = onetable.Uid
where
secondtable.Fid = 3 AND secondtable.Val > 2000
group
by onetable.Uid
, onetable.Login

 

[ESquared]

I have an EAV database I'm working with. I have a stored procedure which builds views that look exactly how you are describing, for each different type of entity that the EAV database can describe. Any time there's a change to the metadata, I run the SP and the views automatically have their columns and data types updated, or are created, or are deleted. The views support insert, update, and delete (although since you can't do an update with a from clause on a view with an instead-of trigger, I convert inserts for rows that already exist to updates, handling nulls as actual values).

You could even, if you needed to and were technically up to the task, build the actual tables that you want to work with, populate them with values from the EAV value table, and write a view (with an instead-of trigger) that replaces the EAV value table. Then the old application stuff still works, but you can work with the "real" objects themselves.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[ESquared]

Er, please forgive me if I said anything that doesn't apply to MySQL. I forgot what forum I'd wandered into. Perhaps someone with more MySQL knowledge than I could "translate" if it's possible.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[guelphdad]

you haven't used mysql in a while! mysql 5 handles stored procedures, triggers, views etc.