Data from two tables without repeating data from the first?

Question

I have two tables in a MySQL Database.

Users table and Users Meta Table

I am looking for a way to get all the information out of both tables with one query. But without repeating the information from Users table.

This is all information relating to the users id number as well. So for example user_id = 1.

Is there a way to query the database and collect all the information I from both tables without repeating the information from the first?

Structure Example

Users Table

• user_id
• user_login
• user_pass

Users Meta Table

• user_meta_id
• user_id
• user_meta_key
• user_meta_value

Im wanting to get out of this

user_id, user_login, user_pass, user_meta_id, user_id, user_meta_key, user_meta_value
                                user_meta_id, user_id, user_meta_key, user_meta_value
                                user_meta_id, user_id, user_meta_key, user_meta_value

Answer 1

Sure, that's easy, just specify the fields you want from each table in the query with a join and don't include the columms that are repeated.

SELECT Users.field1, Users.field2, Users.field3, Users.user_ID, 
       UsersMeta.field4, UsersMeta.field5
FROM USERS
LEFT JOIN UsersMeta ON (Usuers.user_ID=UsersMeta.User_ID)

Answer 2

You didn't mention what database server you are using.

Assuming your tables are like:

USERS (user_id, first_name, last_name, gender)
USER_META (user_id, comment_count, likes)

Your query would look like this in MySQL:

SELECT u.user_id, first_name, last_name, gender, comment_count, likes 

FROM USERS u LEFT JOIN USER_META m ON (u.user_id = m.user_id);

Answer 3

yes, it is possible using DISTINCT keyword in query

    SELECT DISTINCT Users.field1, DISTINCT users.field2, UsersMeta.field3
          FROM USERS, UsersMeta
    WHERE Users.user_ID=UsersMeta.User_ID

Distinct description

Answer 4

That's the work of front-end tools/language, e.g. Crystal Report, PHP, C#, etc. Don't do it in query

Answer 5

SELECT DISTINCT table1.id, table1.field2, table1.field3, tab_id.id_table1 FROM table1 left join tab_id on (table1.id=tab_id.id_table1)