Select query in two tables in MySQL

Question

I am running this query

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.

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

Another table looks like this name user:

userid          username      fullname      email              role
  9             someuser       john Doe    [email protected]    admin

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

Desired output:

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

Thank you! (In Advance!)

You query looks like it should return all the rows for userid 9 (which is 9 rows). Can you give an example of what you want it to output? — Kickstart
– Kickstart, Commented Jan 16, 2014 at 9:12

Abhik Chakraborty · Accepted Answer · 2014-01-16 11:51:06Z

2

SELECT 
    *
FROM
    user
        INNER JOIN
    user_meta ON user_meta.userid = user.userid
WHERE
    user.userid = 9

The above answer was with respect to your initial requirement. But as per your new requirement it can not be done with simple query, it needs to be dynamic and here I have created the demo for you, you can use this

http://sqlfiddle.com/#!2/39861/2

edited Jan 16, 2014 at 11:51

answered Jan 16, 2014 at 9:13

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3201500 Over a year ago

This is returning multiple line values. I just want single line.

Abhik Chakraborty Over a year ago

can you post your desired output information, the inner join will do join to the table and finds all the match rows of join. In user_meta you have multiple rows for userid = 9 so it returns this way. There is another way using GROUP_CONCAT() function, but this will concat the data with a separator all in one row something like mobile,address,...etc for meta_key using group by.

user3201500 Over a year ago

yes but i want something similar to "desired output" as i mentioned above. thank you! for your reply.

Kickstart · Accepted Answer · 2014-01-16 11:18:20Z

0

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

answered Jan 16, 2014 at 11:18

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

11 Comments

Abhik Chakraborty Over a year ago

Yes and I am trying to create a dynamic query for this , see here sqlfiddle.com/#!2/13703/11 , not sure where it goes wrong, if someone can edit it this might solve the issue.

Kickstart Over a year ago

That should work. However I think the problem you have is a very simple typo. You have set the test data up for user id 1 yet you select for user id 9, so no records returned. Note you could use the GROUP_CONCAT suggestion I made earlier to do this, extracting the details in your script.

Abhik Chakraborty Over a year ago

Ah @Kickstart thanks a lot it worked now, my bad and I was going crazy. sqlfiddle.com/#!2/39861/2 here is the output. I used the logic suggested here stackoverflow.com/questions/15977322/…

Kickstart Over a year ago

It is a perfectly valid way of doing this, although it will confuse most people when they see your code! Down side is that it is effectively doing 2 queries.

Abhik Chakraborty Over a year ago

Yeah looking for a better approach if we really do not know the number of rows in the 2nd table. And you mentioned that in your answer.

|

Arion · Accepted Answer · 2014-01-16 09:15:35Z

0

SELECT * FROM user_meta um, user u WHERE um.userid = u.userid

edited Jan 16, 2014 at 9:15

Arion

31.3k10 gold badges74 silver badges89 bronze badges

answered Jan 16, 2014 at 9:11

GuyT

4,4562 gold badges20 silver badges34 bronze badges

Comments

Arion · Accepted Answer · 2014-01-16 09:15:57Z

0

simple as this:

select * from user_meta, user where user_meta.userid=9 
and user_meta.userid=user.userid

edited Jan 16, 2014 at 9:15

Arion

31.3k10 gold badges74 silver badges89 bronze badges

answered Jan 16, 2014 at 9:14

draganndi

1551 gold badge2 silver badges11 bronze badges

Comments

Arion · Accepted Answer · 2014-01-16 09:16:07Z

0

select 
    user_meta.umeta_id,
    user_meta.userid,
    user_meta.meta_key,
    user_meta.meta_value,
    user.userid,
    user.username,
    user.fullname,
    user.email,
    user.role
from
    user_meta
        LEFT JOIN
    user ON user_meta.userid = user.userid
where
    user_meta.userid = 9

edited Jan 16, 2014 at 9:16

Arion

31.3k10 gold badges74 silver badges89 bronze badges

answered Jan 16, 2014 at 9:13

Raju Rathore

1495 bronze badges

Comments

Kickstart · Accepted Answer · 2014-01-16 09:22:13Z

0

The only real way to reduce it to a single line is using an aggregate function

for example, grouping the details in a field

SELECT user.userid, user.username, user.fullname, user.email, user.role, GROUP_CONCAT(CONCAT_WS('-', user_meta.umeta_id, user_meta.meta_key, user_meta.meta_value))
FROM user_meta 
INNER JOIN user ON user_meta.userid=user.userid 
WHERE user_meta.userid=9
GROUP BY user.userid, user.username, user.fullname, user.email, user.role

answered Jan 16, 2014 at 9:22

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Comments

Puttu · Accepted Answer · 2014-01-16 09:23:39Z

0

If you want only user_meta table all values means use this.

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

If you want only user table all values means use this.

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

If you want both means mention like this

select 
    meta.umeta_id,
    meta.userid, // only one userid is enough
    meta.meta_key,
    meta.meta_value,
    us.username,
    us.fullname,
    us.email,
    us.role from user_meta JOIN
    user ON meta.userid = us.userid

answered Jan 16, 2014 at 9:23

Puttu

981 gold badge1 silver badge7 bronze badges

1 Comment

user3201500 Over a year ago

Thank you! for your kind reply. Can you please suggest me something related to desired output?

Mohammad Amin · Accepted Answer · 2014-01-16 10:23:38Z

0

SELECT * FROM USERS U INNER JOIN Meta_Users M ON U.userid = M.userid
WHERE U.userid = 9

answered Jan 16, 2014 at 10:23

Mohammad Amin

624 bronze badges

Collectives™ on Stack Overflow

Select query in two tables in MySQL

8 Answers 8

3 Comments

11 Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

11 Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related