MySQL query to display data from joined tables

Question

I have 2 tables, one is for merits they recieved, and the other is for the list of possible merits, and every merit it part of a bigger catagory

table 1

merit_id
merit_name
merit_desc
cat_id

table 2

uid
merit_id

now what happens is in the php page, the user enter a catagory, and I want to display all the merits in said catagory and the one that the user already has, to display them first.

SELECT * FROM $achiev_table(table1) 
LEFT JOIN $user_achiev_table(table2) using(merit_ID) 
where $achiev_table.area_id=$area_id
ORDER BY UID DESC

the query I use for the php is as stated, and the problem is that it displays the merits more than once, and if I add a where clause to include the UID='$uid' than nothing is displayed.

Thank you.

Galz · Accepted Answer · 2011-03-06 22:59:36Z

2

To get the merits from category X that user Y has:

SELECT m.merit_id, m.merit_name
FROM merits m
JOIN users_merits um ON m.merit_id = um.merit_id
WHERE m.cat_id = X
AND um.uid = Y

To get all the merits from category X:

SELECT m.merit_id, m.merit_name
FROM merits m
WHERE m.cat_id = X

EDITED: To get all the merits from category X, and show the ones user Y already has at the top (I am guessing that's what you meant):

SELECT m.merit_id, m.merit_name, um.uid as `user`
FROM merits m
LEFT JOIN users_merits um ON m.merit_id = um.merit_id AND um.uid = Y
WHERE m.cat_id = X
ORDER BY user, m.merit_name DESC

Explanation:

The LEFT JOIN attaches records from users_merits table to records from merits table, if there are records to attach.

That means, that for every record in merits that satisfies the WHERE clause, the DB will try to match a record (or records) from *users_merits* that satisfy the condition in the ON clause.

Being a LEFT JOIN (unlike a simple JOIN), all the records from the main table (merits) will be shown even if they have no matches in *users_merits* - as long as they satisfy the WHERE clause. Originally I put the uid = Y in the WHERE clause, so merits that were not received by user Y were not showing.

Finally we get a table of merits, all of them from category X, some of them achieved by user Y - those have a uid attached to them while others have NULL. ORDER BY uid puts the ones with the uid above the ones with NULL.

P.S. My SQL is better than my English, hope this is helpful :)

edited Mar 6, 2011 at 22:59

answered Mar 6, 2011 at 18:07

Galz

6,8525 gold badges35 silver badges39 bronze badges

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

3 Comments

Itai Sagi Over a year ago

Well, I tried those queries, and basically I want than even if a user don't have any merits, all of them will be displayed. using the last query you provided, it only shows the merits if the user has them.

Itai Sagi Over a year ago

SELECT kaizen_achievments.aid, kaizen_achievments.achieve_name, kaizen_user_achievments.uid as user FROM kaizen_achievments LEFT JOIN kaizen_user_achievments ON kaizen_achievments.aid = kaizen_user_achievments.aid WHERE kaizen_achievments.area_id = '29' AND (kaizen_user_achievments.uid = '104' or isnull(uid)) ORDER BY user, kaizen_achievments.achieve_name DESC using this query it's getting there, but the problem is that it doesn't display the merits which users have already been rewarded for.

Itai Sagi Over a year ago

You're totally awesome guy, thanks, can you explain me the logic a bit so I won't be lost next time?

Satya · Accepted Answer · 2011-03-06 14:26:09Z

0

You want to SELECT FROM the merit table, left join user table, where UID=$uid OR UID IS NULL, order by uid.

answered Mar 6, 2011 at 14:26

Satya

4,49824 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL query to display data from joined tables

table 1

table 2

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

table 1

table 2

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related