PDA

View Full Version : [SQL] Getting client names from other table knowing only theirs ids



RobsoN
18th March 2014, 22:23
Hello, Im new in SQL. I want to get in one query: reason, banned NAME, admin NAME (thats missing in my query - i need this :P), ban type, time_add, time_expire, type. My current query looks like this:


SELECT p.id, p.type, p.client_id, p.time_expire, p.time_add, p.reason AS ban_reason, c.name AS banned_client, p.admin_id AS banned_by FROM penalties p, clients c WHERE (p.inactive = '0' AND (p.time_expire > UNIX_TIMESTAMP() OR p.time_expire < 1)) AND (p.type = 'TempBan' OR p.type = 'Ban') AND c.id = p.client_id

As you can see, I got 2 tables.

1st table "clients" with id, name (...)
2nd table "penalties" with client_id, admin_id, time_add, reason, type

I dont know how can I get names (admin name + banned name) in one query, knowning only clients ids ..:confused:

Thanks in advice.
Regards RobsoN.

IzNoGoD
18th March 2014, 23:27
SELECT p.name, a.name FROM (SELECT client_id, admin_id FROM penalties WHERE ...[your where clause goes here if any]) b INNER JOIN (SELECT name, id FROM clients) a ON (a.id = b.admin_id) INNER JOIN (SELECT name, id FROM clients) p ON b.client_id = p.id

RobsoN
23rd March 2014, 00:24
Thanks a lot!