Data from two tables without repeating data from the first?
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
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)
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)
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
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);
That's the work of front-end tools/language, e.g. Crystal Report, PHP, C#, etc. Don't do it in query
精彩评论