在MySQL的两个表中选择查询 [英] Select query in two tables in MySQL

问题描述

我正在运行此查询

select * from user_meta JOIN user ON user_meta.userid=user.userid where user_meta.userid=9

但是这不是我想要的结果，它返回多行.

But this is not resulting what i wanted, its returning multiple lines.

我的一张桌子看起来像这个名字user_meta:

My one table looks like this name user_meta:

umeta_id     userid      meta_key       meta_value
 1              9         mobile        123324
 2              9         address       some address
 3              9         city          some city
 4              9         country       some country
 5              9         occupation    some details
 6              9         website       someurl
 7              9         mobile        123324
 8              9         address       some address
 9              9         city          some city
 10             10        country       some country
 11             10        occupation    some details
 12             10        website       someurl

另一个表看起来像这个名字user:

Another table looks like this name user:

userid          username      fullname      email              role
  9             someuser       john Doe    [e-mail]    admin

我如何使其选择查询，以便可以从两个表中获取与userid 9相关的所有值，并使它看起来像这样

How can i make it select the query so that all the values related to userid 9 can be fetch from both the tables and make it look like this

所需的输出:

userid          username      fullname      email              role      Mobile     address     city     country    occupation     website
  9             someuser       john Doe    [e-mail]    admin    123123     someaddres    Somecity    somecountry    some details    someurl

谢谢！ (提前！)

推荐答案

为您提供所需的输出(但这并不灵活，因此，如果添加要输出的更多详细信息，将无法进行更改) :-

To give you the output you want (but this isn't flexible, so won't cope without changes if you add more details that you want to output):-

SELECT user.userid, 
        user.username, 
        user.fullname, 
        user.email, 
        user.role, 
        mobile_meta.meta_value AS `Mobile`, 
        address_metameta_value AS `Address`, 
        city_metameta_value AS `City`, 
        country_metameta_value AS `Country`, 
        occupation_metameta_value AS `Occupation`, 
        website_metameta_value AS `Website`
FROM user
LEFT OUTER JOIN user_meta AS mobile_meta ON mobile_meta.userid=user.userid AND mobile_meta.meta_key = 'mobile'
LEFT OUTER JOIN user_meta AS address_meta ON address_meta.userid=user.userid AND address_meta.meta_key = 'address'
LEFT OUTER JOIN user_meta AS city_meta ON city_meta.userid=user.userid AND city_meta.meta_key = 'city'
LEFT OUTER JOIN user_meta AS country_meta ON country_meta.userid=user.userid AND country_meta.meta_key = 'country'
LEFT OUTER JOIN user_meta AS occupation_meta ON occupation_meta.userid=user.userid AND occupation_meta.meta_key = 'occupation'
LEFT OUTER JOIN user_meta AS website_meta ON website_meta.userid=user.userid AND website_meta.meta_key = 'website'
WHERE user.userid=9

这篇关于在MySQL的两个表中选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！