CODEWITHSUNDEEP
mysqlpdo
Marc Towler
Marc Towler

Reputation: 705

Pull multiple rows from one table but only one row from a related table

I currently am trying to join two tables but prevent duplication of information from one of the tables.

The user's table has 4 columns, uid, name, email and status. The stats table has 4 columns, uid, date, follows, views

What I would like to be able to do is pull every record from the stats table and only the name, email and status values from the user table. The issue I have with the below SQL is that it duplicates the data from the user table, is there a way around this?

SELECT u.name
     , u.email
     , u.status
     , s.date
     , s.follows
     , s.views 
  FROM users u
  JOIN stats s 
    ON u.id = s.uid 
 WHERE name = :name

Upvotes: 1

Views: 159

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562280

If you don't want every matching stats row to be accompanied by its matching users row, then you have to run two queries:

SELECT u.id, u.name, u.email, u.status FROM users u WHERE name = :name

Note the result of u.id because you'll use it as a parameter for the second query:

SELECT s.date, s.follows, s.views FROM stats s WHERE s.uid = :uid

You have to understand that the relational model works because every query result is itself a relation. The matching data is returned in every row, and this is what allows JOIN to be part of an algebra, where the result can be used as the operand of another JOIN, or a GROUP BY, or some other relational operation.

You should read SQL and Relational Theory: How to Write Accurate SQL Code by C. J. Date.

Upvotes: 1

Related Questions