multiple entry to single entry

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiple entry to single entry

Author

Topic

leracj
Starting Member

13 Posts

Posted - 2013-06-28 : 14:47:15

MY CODE:
----------------------------
SELECT DISTINCT ID, user_login, user_email, meta_value
FROM wp_users um, wp_usermeta u
WHERE (
user_login LIKE '%tr%'
OR meta_value LIKE '%tr%'
OR user_email LIKE '%tr%'
)
AND (
user_id = ID
AND (
meta_key = 'first_name'
OR meta_key = 'last_name'
OR meta_key = 'city'
OR meta_key = 'address'
OR meta_key = 'programs'
)
)
ORDER BY `um`.`user_login` ASC
LIMIT 0 , 30
-----------------------------------
Output:

table wp_user:

table wp_usermeta:

i want to know how to have only 1 single entry every distinct wp_users id

please help

rs3gold
Starting Member

2 Posts

Posted - 2013-06-28 : 22:55:07

unspammed

leracj
Starting Member

13 Posts

Posted - 2013-06-29 : 12:06:10

I did once again answer my own post...

SELECT DISTINCT ID, user_login, user_email, meta_value, count(*)
FROM wp_users um, wp_usermeta u
WHERE (
user_login LIKE '%tr%'
OR meta_value LIKE '%tr%'
OR user_email LIKE '%tr%'
)
AND (
user_id = ID
AND (
meta_key = 'first_name'
OR meta_key = 'last_name'
OR meta_key = 'last_name'
OR meta_key = 'city'
OR meta_key = 'address'
OR meta_key = 'programs'
)
)
group by ID,user_login,user_email
having count(*) > 1 ORDER BY `um`.`ID` ASC

please help

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 14:14:07

Is this what you want assuming you don't care about met_value:
[CODE]

SELECT DISTINCT ID, user_login, user_email, MAX(meta_value)
FROM wp_users um, wp_usermeta u
WHERE (
user_login LIKE '%tr%'
OR meta_value LIKE '%tr%'
OR user_email LIKE '%tr%'
)
AND (
user_id = ID
AND (
meta_key = 'first_name'
OR meta_key = 'last_name'
OR meta_key = 'last_name'
OR meta_key = 'city'
OR meta_key = 'address'
OR meta_key = 'programs'
)
)
group by ID,user_login,user_email
ORDER BY `um`.`ID` ASC

[/CODE]

leracj
Starting Member

13 Posts

Posted - 2013-06-29 : 14:21:59

thanks for the post but i already answered this

quote:
Originally posted by MuMu88

Is this what you want assuming you don't care about met_value:
[CODE]

SELECT DISTINCT ID, user_login, user_email, MAX(meta_value)
FROM wp_users um, wp_usermeta u
WHERE (
user_login LIKE '%tr%'
OR meta_value LIKE '%tr%'
OR user_email LIKE '%tr%'
)
AND (
user_id = ID
AND (
meta_key = 'first_name'
OR meta_key = 'last_name'
OR meta_key = 'last_name'
OR meta_key = 'city'
OR meta_key = 'address'
OR meta_key = 'programs'
)
)
group by ID,user_login,user_email
ORDER BY `um`.`ID` ASC

[/CODE]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 14:51:02

You don't have meta_value in your group by clause,
were you able run this query without any errors?

quote:
Originally posted by leracj

I did once again answer my own post...

SELECT DISTINCT ID, user_login, user_email, meta_value, count(*)
FROM wp_users um, wp_usermeta u
WHERE (
user_login LIKE '%tr%'
OR meta_value LIKE '%tr%'
OR user_email LIKE '%tr%'
)
AND (
user_id = ID
AND (
meta_key = 'first_name'
OR meta_key = 'last_name'
OR meta_key = 'last_name'
OR meta_key = 'city'
OR meta_key = 'address'
OR meta_key = 'programs'
)
)
group by ID,user_login,user_email
having count(*) > 1 ORDER BY `um`.`ID` ASC

please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-29 : 14:54:25

One thing to note
You're using MySQL I guess seeing LIMIT 0 , 30 etc in query. Please keep in mind that this is ms sql server forum and there're not too much expertise on MySQL here. So your best bet would be posting in MySQL forums like www.dbforums.com to get MySQL specific syntax help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-29 : 14:54:26

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources