CODEWITHSUNDEEP
mysql
DonWagner
DonWagner

Reputation: 71

Getting data form one table with help from another

Sorry for the title, didn't know how to describe the problem in a short way.

I don't know if i'm asking the right place (with the right tags), and in a way you guys understand, but i will try :)

First off all, my english is not that good, and it's difficult for me to describe my problem, so be nice :)

I would like to get these tings in my tabels: I have 2 tables in my DB. One db_users and db_tasks

db_users: [uId][uName][uActive]

db_tasks [tId][tUserId][t1][t2][t3][t4][t5][t6][tYear]

I link them together with userid like this: uId = tUserId

Then i only wanner get the users from db_users who got 0 points ind task5 (t5) and task6 (t6) And wher tYear is 2014

Not all users has begone solving tasks, so not all users is registered in db_tasks Some users have solved task 1 and 2 and is registered in db_tasks

I would like to get follow data: uId, uName And If the user is in the db_tasks then i would like the tId = tId if the user is not in db_tasks tId = 0.

I would like it all to be in one sql="select ..... query, because i will have the data in a array to print in my viewer (the page is in a MVC franework).

I would like to make a list on my page like this:

echo $uId.' - '.$uName.' ('if($tId > 0){ echo 0; } else { echo $tId }')';

I have tried to Join, If case and every thing i could think of, but nothing works. Is it possible to do what i want, without changing the table and database structure?

Upvotes: 0

Views: 34

Answers (1)

CodeBird
CodeBird

Reputation: 3858

This to get all users and tId if there is...

SELECT uId, uName, COALESCE(tId, 0) 
FROM db_users u LEFT JOIN db_tasks t 
ON u.uId=t.tUserId

This is to get users with 0 in t5 and t6 in 2014

SELECT uId, uName, tId FROM db_users u INNER JOIN db_tasks t 
ON u.uId=t.tUserId WHERE tYear=2014 
AND (t5=0 OR t6=0)

I hope these help as I am not sure I understood well, but I am sure you can go from here and attain what you want.

UPDATE Try this:

SELECT uId, uName, 0 as tId FROM db_users 
WHERE uId NOT IN (SELECT tUserId FROM db_tasks)
UNION
SELECT uId, uName, tId FROM db_users u INNER JOIN db_tasks t
ON u.uId=t.tUserId WHERE tYear=2014 
AND (t5=0 OR t6=0)

Upvotes: 1

Related Questions