CODEWITHSUNDEEP
phpmysql
Dusan Dimitrijevic
Dusan Dimitrijevic

Reputation: 3219

How to use this query properly?

I have three tables in my DB, one for storing user details, one for storing team details and one for storing users and teams ids for recognizing which team belongs to user.

I want now to fetch all users from DB who are belonging to selected group. And i'm doing that like this:

SELECT u.name, u.image FROM groups g, user_groups ug, users u WHERE
 g.id = ? AND ug.group_id = g.id

enter image description here

but i'm not getting the result i wanted. As you can see in attached image above, i have two users in one group, so i'm expecting two records in my response, but this is what i'm getting in response:

JSON response:

    {
  "error": false,
  "users": [
    {
      "name": "Dušan Dimitrijević", // This is the user from selected group, but i'm getting duplicate record
      "image": "http://192.168.42.6:8081/timster/uploads/user_images/%2018.png"
    },
    {
      "name": "Dušan Dimitrijević",
      "image": "http://192.168.42.6:8081/timster/uploads/user_images/%2018.png"
    },
    {
      "name": "Miroslav", // And this one too, but also duplicated
      "image": "null"
    },
    {
      "name": "Miroslav",
      "image": "null"
    },
    {
      "name": "Pera Peric",
      "image": "null"
    },
    {
      "name": "Pera Peric",
      "image": "null"
    },
    {
      "name": "Marko Markovic",
      "image": "null"
    },
    {
      "name": "Marko Markovic",
      "image": "null"
    },
    {
      "name": "Stefan Dimitrijevic",
      "image": "null"
    },
    {
      "name": "Stefan Dimitrijevic",
      "image": "null"
    },
    {
      "name": "Petar Nikolic",
      "image": "null"
    },
    {
      "name": "Petar Nikolic",
      "image": "null"
    },
    {
      "name": "Nikola Ristic",
      "image": "null"
    },
    {
      "name": "Nikola Ristic",
      "image": "null"
    }
  ]
}

So, i guess that i'm doing something wrong in my query SELECT.

Upvotes: 0

Views: 58

Answers (2)

Matthias Beaupère
Matthias Beaupère

Reputation: 1847

To do a SELECT query between different tables like this :

SELECT * FROM table1,table2,table3

This is doing a Cartesian Product between the tables ( = CROSS JOIN). This will duplicate a lot of data to make a table with all the possible combinations of the different tables.

See some documentation about CROSS JOIN here

In your case you should use INNER JOIN, which is more apropriate.

Upvotes: 0

jiboulex
jiboulex

Reputation: 3021

I think you are implementing your relations wrong, you should join the user_group table based on the user id.

And also, you should consider using JOIN statements :

SELECT
    u.name,
    u.image
FROM
    users u
INNER JOIN
    user_groups ug
    ON ug.user_id = u.id
WHERE
    ug.group_id = ?

(I assumed that your user_group table had a user_id field ?)

Upvotes: 5

Related Questions