Select from two tables without repeating data

Asked

Viewed 4,197 times

0

I have two tables, one for users and one for photos where each line contains the user id of the respective photo.

I want to select the user data and grab the photos by the user id of the photos table.

In this way it returns me the duplicated data from the user table because the user contains several photos in the photo table.

How can I only get once the user data(user table) and all the photos to which it belong from the photos(photo table), which the user(user_id) contains in the table photos? 

$sql = "SELECT  u.id, u.username, u.genero, u.idade, u.local, u.descricao, p.user_id, p.location 

FROM user AS u 

INNER JOIN photos AS p

ON u.id=p.user_id


";
mysql database
  • 1

    David you already tried to use "select distinct"

  • already yes, but the return is the same

  • But you want the pictures, right? If you want the pictures and you need their location p.local then when doing Join you will have to have user data for each line. Think about the real world, each photo has a user, so each SQL line comes a different picture and each one has a user, only in your case the user is the same...

  • I understood that he didn’t want the photos, but the quantity. I understood this by this phrase removed from the question: "and the totality of the photos (table photo) that the user(user_id) contains in the table photos".

  • @Cantoni, I noticed that too, but I also saw the p.local There, I decided to leave the comment to see which of the two things he really needs. In case the whole he meant could be all the photos and not a Count... Since you had already put Count in the answer I only talked about the case of p.local

  • @Ricardo only wanted the user’s information and all the user’s own photos. Only photos can be repeated because the user can have multiple photos.

  • David, do you want the total photo only, type, user david has 50 photos or want the details of all photos?

  • the p.location is the directory of the photos, no problem. they are displayed with the query q I have. only for each photo line it duplicates the user data

  • @I want to receive the user information and all the photos that belong to him. This query returns duplicate user information because it has 2 photos, for example. If the user has 4 photos, see the 4 photos plus 4x the username, for example.

Show 4 more comments

3 answers

3


If what you want to know is the user data and the amount of photos that exist for each one, then this SQL should solve:

SELECT  u.id, u.username, u.genero, u.idade, u.local, u.descricao, count(*)
FROM user AS u 
INNER JOIN photos AS p
ON u.id=p.user_id
GROUP BY u.id, u.username, u.genero, u.idade, u.local, u.descricao

2

I’m putting an answer because in the comment will get very bad make the table I want to put example.

What you’re saying is a problem is actually the "right". Let’s take a practical example:

User Ricardo has 5 photos:

When doing SQL the return is:

u.id   u.username   p.user_id   p.location
1      Ricardo      1           (local foto1)
1      Ricardo      1           (local foto2)
1      Ricardo      1           (local foto3)
1      Ricardo      1           (local foto4)
1      Ricardo      1           (local foto5)

This is the current result:

What you’re asking is to stay that way:

u.id   u.username   p.user_id   p.location
1      Ricardo      1           (local foto1)
null   null         1           (local foto2)
null   null         1           (local foto3)
null   null         1           (local foto4)
null   null         1           (local foto5)

That wouldn’t be right.

If you want only the data of the photos makes a direct SQL in the photos:

select * from photos where user_id = 1

It is not wrong for it to return duplicated data if you do the Join of the two tables.

Got it?

  • Okay, that’s right. But I also want to get user information. select user id=1 and receive all user photos in table photos($user_id=1).

  • Your question SQL do exactly what you’re saying, bring everything from the user and grab all the photos from it, and it comes in the formed from the first table of that answer, which you just said (Okay, that’s right). Agrees?

  • Yes, the SQL of my question returns everything. User information and photos all belong to the user. As in your first example. It has 5 photos and repeats 5x the username

  • So you understand that what you have is the right one?

  • For it not to repeat I would have to do a query only for the user exit and then do another query to select the photos of the user that left in the first query?

  • That’s right. Now, as for the right or wrong of making two queries or one just as it is going from each.

Show 1 more comment

0

use the select distinct.

$sql = "SELECT DISTINCT u.id, u.username, u.genero, u.idade, u.local,u.descricao, p.user_id, p.location 

FROM user AS u 

INNER JOIN photos AS p

ON u.id=p.user_id


";

Browser other questions tagged mysql database

You are not signed in. Login or sign up in order to post.