CODEWITHSUNDEEP
phpmysql
MultiDev
MultiDev

Reputation: 10649

MySQL: JOIN two tables with matching row names

I have two tables like so:

user_users

ID    UserGroup    Email
1     3            [email protected]
2     3            [email protected]

user_groups

ID    GroupName
2     Some Group
3     Another Group

I am retrieving data from both tables in one query, like so:

SELECT u.*, g.GroupName FROM user_users u JOIN user_groups g ON u.UserGroup = g.ID

The problem that I'm having is that I need to retrieve all the columns in both tables, but both the user_users table and the user_groups table have a column name of ID.

So, this:

SELECT u.*, g.* FROM user_users u JOIN user_groups g ON u.UserGroup = g.ID

... will not return the ID from the user_groups table.

How can I modify this query so that all the columns in the user_groups table can be returned as one array? Where the results will be like:

[
    'ID' => 2,
    'UserGroup' => 3,
    'Email' => '[email protected]',
    'GroupTable' => [
        'ID' => 3,
        'GroupName' => 'Another Group'
    ]
]

Upvotes: 0

Views: 116

Answers (3)

jvk
jvk

Reputation: 2201

//Hi this might helpfull to you.

$sqlQuery = 'SELECT * FROM user_groups';

$stmt = $conn->prepare($sqlQuery);
$stmt->execute();
$output = $stmt->fetchAll();

$sqlUGQuery = 'SELECT * FROM user_users WHERE UserGroup=:userGroupId';
$stmtUG = $conn->prepare($sqlUGQuery);
foreach ($output as $key => &$value) {
    $stmtUG->execute([':userGroupId'=>$value['id']]);
    $value['GroupTable'] = $stmtUG->fetchAll();
}

echo "<pre>";
print_r($output);
echo "</pre>";
exit;

Output will be as below. enter image description here

Upvotes: 0

Sonam Tripathi
Sonam Tripathi

Reputation: 183

You should try this and to get it in array structure please modify the same through code:

SELECT 
  ux.*, 
  uy.* 
FROM 
  user_groups uy 
  JOIN (
    SELECT 
      u1.* 
    FROM 
      user_users u1 
      LEFT JOIN user_users u2 ON (
        u1.UserGroup = u2.UserGroup 
        AND u1.id < u2.id
      ) 
    WHERE 
      u2.id IS NULL
  ) ux ON ux.UserGroup = uy.ID;

Upvotes: 1

alimbaronia
alimbaronia

Reputation: 504

You may need to specify each field and put field aliases for ID fields:

SELECT u.ID as ‘User ID’, u.UserGroup, u.Email, g.ID as ‘Group ID’  g.GroupName FROM user_users u JOIN user_groups g ON u.UserGroup = g.ID

Or try specifying only the ID fields, like this:

SELECT u.ID as ‘User ID’, u.*, g.ID as ‘Group ID’, g.* FROM user_users u JOIN user_groups g ON u.UserGroup = g.ID

Upvotes: 2

Related Questions