开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜