Import User Data To Excel From WordPress Via MySQL: Relationships

Question

I am trying to extract user data from my WordPress MySQL database to analyse in Excel. My output would be a flat table with rows of UserID and columns of Name, Email, Address, Telephone etc. Very simple.

The two tables with this data are (in my installation): wp_swm_users and wp_swm_usermeta.

• wp_swm_users: a simple table with ID, user_login, user_email etc

• wp_swm_usermeta: a table with meta_key and meta_value pairs, each associated to a user_id

There is a one (ID) to many (user_id) relationship between the two tables.

I have connected Excel to the MySQL database and created two Excel tables from the MySQL tables above and added both to the Data Model. I then created the one-to-many relationship between ID and user_id and a flat pivot table to display my output.

If I take fields from just one of the tables everything works OK e.g. wp_swm_usermeta.user_id and wp_swm_usermeta.meta_key=first_name and wp_swm_usermeta.meta_value, I get a lovely table of user ID's by first names. Sweet.

But no matter what I do, as soon as I use any field from the other table, ALL values are listed of the added field instead of just the value that has the relationship.

RELATIONSHIP RELATIONSHIP

FIELDS FROM ONE TABLE FIELDS FROM ONE TABLE ONLY

FIELDS FROM TWO TABLES FIELDS FROM BOTH TABLES

It's as though the relationship is just not working but I can't fathom out why. Any help really appreciated. I know it's going to be dumb but I've spent so long on this now I can't see the wood for tress.

My warmest regards, David.