mysql table problem?

i hav开发者_高级运维e these two tables tables for a chatting app

users{user_id,username,pictures}
chat_data(con_id, chat_text}

i used this sql query

SELECT c.chat_text, u.username
FROM chat_data c, users u
WHERE c.con_id =1

but its giving me duplicate results, when i know thiers only row with the con_id =1, what is the problem with the query!! :))

---

You need to "join" the tables to avoid duplicates. For example

SELECT c.chat_text, u.username
FROM chat_data c, users u
WHERE c.con_id =1
  and u.id = c.user_id

You can read a bit about relational algebra which is the theory behind relational databases.

---

The users and chat_data tables should be JOINED in order to get a unique tuple as result. Since users and chat_data cannot be joined, you simply get Cartesian product of the two tables.

Cartesian Products

If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, then the optimizer may choose a join order that avoids producing an intermediate Cartesian product.

Refer: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/queries006.htm

---

With a query like that, you will get as many rows in the results as there are rows in the table users.

---

It's because of the type of join the SQL is doing. Is it returning a row for each user you have? This is what I expect it is doing, i.e. if you have 2 users John and Jack then are you getting a row for both of these users being returned?

Are you just trying to get the data related to the users involved in a conversation? If so you need some link between the 2 tables, like foreign key references from the chat_data table referencing users.

---

As previously stated, you're missing a link between the two tables. If you are trying to retrieve the user associated to a particular chat you will need to add a foreign key reference in chat_data that references user.user_id. But if you are trying to get multiple users associated to a chat you would need to add a new table. Your new tables would look something like this:

users{user_id,username,pictures}
chat_data(con_id, chat_text}
user_chat(user_id, con_id) //By adding this new table you can have multiple users per chat

The the query would look something like

SELECT u.username, c.chat_text
FROM users u, chat_data c, user_chat uc
WHERE u.id = uc.id
AND c.con_id = uc.con_id