Select from 2 tables with 2 foreign keys

Question

I have a question for you and I have not found a solution to this, So I have 2 tables:

dosar
id      name     date      fk_user     fk_verificator   
1      dosar 1   08/08/14   1               2
users
id         name         is_admin         is_verificator
1         admin           Y                   N
2        verificator      N                   Y

fk_user is foreign key pointed to users, fk_verificator is also a foreign key pointed to users table.So I need to create an select to get 2 users for dosar

name           date           name            is_admin              is_verificator
dosar1       08/08/14         admin            Y                        N
                              verificator      N                        Y

My query:

 $uid = (int) $this->uri->segment(3, 0);
    $this->load->database();
    $get_dosar = $this->db->query("SELECT * FROM dosar,users WHERE
                                    users.id = dosar.fk_user AND
                                    users.id = dosar.fk_verificator
                                    AND dosar.id_dosar = $uid");

Help me please guys.

Where to start. There's lots of basic, bad stuff here. – Strawberry Aug 23 '14 at 14:10 — Strawberry, Aug 23 '14 at 14:10

Brian DeMilia · Accepted Answer · 2014-08-23T14:17:07.703

This uses modern join syntax (it is a good practice not to put join conditions in the WHERE clause):

Fiddle: http://sqlfiddle.com/#!9/d6e60/2/0

select d.name, d.date, u.is_admin, u.is_verificator
  from dosar d
  join users u
    on u.id = d.fk_user
    or u.id = d.fk_verificator
 where d.id = $uid

Output (for ID #1 on dosar):

|    NAME |                          DATE | IS_ADMIN | IS_VERIFICATOR |
|---------|-------------------------------|----------|----------------|
| dosar 1 | August, 08 2014 00:00:00+0000 |        Y |              N |
| dosar 1 | August, 08 2014 00:00:00+0000 |        N |              Y |

You seem to want to not repeat values of NAME or DATE where it is the same as the previous row. That should be done in PHP, not the SQL.

Because you're always just selecting one ID it's somewhat simple to do it in MySQL as well (see below) however I would still recommend doing that part in PHP.

select case when rn = 1 then name end as name,
       case when rn = 1 then date end as date,
       is_admin,
       is_verificator
from(select d.name, d.date, u.is_admin, u.is_verificator, @rw := @rw + 1 as rn
      from dosar d
      join users u
        on u.id = d.fk_user
        or u.id = d.fk_verificator
      cross join (select @rw := 0) r
     where d.id = 1) x

Output:

|    NAME |                          DATE | IS_ADMIN | IS_VERIFICATOR |
|---------|-------------------------------|----------|----------------|
| dosar 1 | August, 08 2014 00:00:00+0000 |        Y |              N |
|  (null) |                        (null) |        N |              Y |

Fiddle: http://sqlfiddle.com/#!9/d6e60/3/0

d'alar'cop · Answer 2 · 2014-08-23T14:02:00.887

0

How about that? Please let me know if it doesn't satisfy and why. If it does work then I'll explain why.

"SELECT d.name,d.date,u.name,u.is_admin,u.is_verificator 
FROM dosar d,users u WHERE
users.id = dosar.fk_user OR
users.id = dosar.fk_verificator
AND dosar.id_dosar = $uid"

If you really want blank cells, I would suggest avoiding putting that burden on the SQL.

edited Aug 23 '14 at 14:02

answered Aug 23 '14 at 13:49

d'alar'cop

2,357
1
14
18

score 0 · Answer 3 · edited May 23 '17 at 12:13

0

Your default JOIN type doesn't work here.

Check MySQL: Quick breakdown of the types of joins

And probably you need an outer join between the tables.

(And after your change to the question, probably a RIGHT JOIN)

edited May 23 '17 at 12:13

Community

1
1

answered Aug 23 '14 at 13:52

Pieter21

1,765
1
10
22

Select from 2 tables with 2 foreign keys

3 Answers3