CODEWITHSUNDEEP
mysqlsqldatabase
user1813962
user1813962

Reputation: 549

Get one result on sql

Have a website with 2 tables (profile and photo). I want to get one profile with a photo, but I get many photos with same profile. I want just one.

SELECT ph.path as photo, pro.id_profile, pro.name, pro.address 
FROM profile pro, photo_profile ph 
WHERE pro.id_profile = 41 
AND pro.id_profile = ph.id_profile;

Please help me!

Upvotes: 1

Views: 23881

Answers (3)

paxdiablo
paxdiablo

Reputation: 882078

You should be able to use the limit clause of the select statement to limit the rows returned.

SELECT ph.path as photo, pro.id_profile, pro.name, pro.address 
FROM profile pro, photo_profile ph 
WHERE pro.id_profile = 41 
AND pro.id_profile = ph.id_profile
LIMIT 1;

Keep in mind that, without an ORDER BY clause, SQL won't guarantee which row you get.

As an example, say you have the following names in a table: Allan, Barbara, Colin and Debbie. The query:

select name from people limit 1;

will get you one row but an indeterminate one. On the other hand:

select name from people order by name asc limit 1;

will get you Allan.

Full syntax details for MySQL select can be found here, including the limit clause.

Keep in mind that limit may not be in all database management systems (it is in MySQL, which is your specific case here). For example, DB2 uses FETCH FIRST n ROWS to do a similar thing.

Upvotes: 4

Himanshu
Himanshu

Reputation: 2454

If you want to know why your database has more than one photo per profile you may want to debug from the results of this :-

SELECT * FROM profile pro, photo_profile ph WHERE pro.id_profile = ph.id_profile;

This is just a plain join of the profile and photo_profile tables.

Upvotes: 0

doublesharp
doublesharp

Reputation: 27637

You can either use LIMIT or GROUP BY depending on your needs, and using a proper JOIN will improve readability:

To get just the first result of your set, use LIMIT:

SELECT ph.path as photo, pro.id_profile, pro.name, pro.address 
FROM profile pro
JOIN photo_profile ph ON 
WHERE pro.id_profile = 41 AND pro.id_profile = ph.id_profile 
LIMIT 1;

To group your results by the profile ID, use GROUP BY:

SELECT ph.path as photo, pro.id_profile, pro.name, pro.address 
FROM profile pro
JOIN photo_profile ph ON pro.id_profile = ph.id_profile
WHERE pro.id_profile = 41 
GROUP BY pro.id_profile;

Upvotes: 0

Related Questions