CODEWITHSUNDEEP
phpmysqljoin
tonoslfx
tonoslfx

Reputation: 3442

mysql joining two table

table user:
id_u*   f_name   l_name
----------------------
1       andi     mitchel
2       sarah    bench
3       kirsty   larx

table voucher:
id_v*   id_user    id_target
1       1          2
2       2          3

quite confused how to join those table with two foreign keys

$db->query("SELECT * FROM voucher v 
LEFT JOIN user u ON u.id_u = v.id_user
LEFT JOIN user u1 ON u1.id_u = v.id_target
WHERE .... ")

echoing while loop... and returns nothing??

while($r = $q->fetch_array(MYSQLI_ASSOC)) :
   echo  $r['u.f_name'];
   echo  $r['u1.f_name'];
endwhile;

Upvotes: 0

Views: 511

Answers (4)

Poonam Bhatt
Poonam Bhatt

Reputation: 10342

SELECT * FROM voucher v 
    LEFT JOIN user u ON u.id_u = v.id_user OR u.id_u = v.id_target
    WHERE ....

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

Your JOIN seems absolutely correct. The only issue is that you have joined table user twice, therefore you have columns with same name (like f_name). The database will assign different (but arbitrary) names to these columns. You can override this behaviour with the AS keyword:

$db->query("SELECT v.*
                 , u.f_name AS user_f_name
                 , u.l_name AS user_l_name
                 , ta.f_name AS target_f_name
                 , ta.l_name AS target_l_name
            FROM voucher v 
            LEFT JOIN user u ON u.id_u = v.id_user
            LEFT JOIN user ta ON ta.id_u = v.id_target
            WHERE .... ")

Then:

while($r = $q->fetch_array(MYSQLI_ASSOC)) :
   echo  $r['user_f_name'];
   echo  $r['target_f_name'];
endwhile;

And I think you can replace the LEFT JOINs with (inner) JOINs. Unless you have id_user or id_target values referencing non-existing userids (id_u).

Upvotes: 2

DRapp
DRapp

Reputation: 48179

It looks like you are asking for all people who are in the voucher table regardless of them being in position 1 (user) or position 2 (target)... Then, showing that person's name.

This query does a pre-query of each possible person and their position basis (via WhichPosition). 

SELECT STRAIGHT_JOIN
      AllVoucherUsers.WhatPosition,
      u.*
   FROM
      ( select distinct 
              v.id_user, 
             '1' as WhatPosition
           from voucher v
        union select distinct
             v.id_target as id_user,
             '2' as WhatPosition
           from voucher v
      ) AllVoucherUsers
      join users u
         on AllVoucherUsres.id_user = u.id_u

If you only want ONE instance of a given person -- REGARDLESS of their position, just strip out all instances of the "WhatPosition" reference...

SELECT STRAIGHT_JOIN
      u.*
   FROM
      ( select distinct 
              v.id_user
           from voucher v
        union select distinct
             v.id_target as id_user
           from voucher v
      ) AllVoucherUsers
      join users u
         on AllVoucherUsres.id_user = u.id_u

Upvotes: 1

Dartoxian
Dartoxian

Reputation: 780

how about: SELECT * FROM voucher JOIN user ON id_user = id_u

Simpler still:

SELECT * FROM voucher, user WHERE id_user = id_u

Upvotes: -1

Related Questions