Nbento Nbento -4 years ago 137
MySQL Question

SQL SELECT different foreign Key

I need select in two tables the foreign key match each other, but I don't want repeat the sames foreign key.

I have these tables on database:


|Photo| (0/*) --------- (1)|Advertisement|

|Photo|
-photo_id
-url
-advertisement_id

|Advertisement|
-advertisement_id
...


Data on database


|Photo|
photo_id | url | advertisement_id
1 | x | 1
2 | x | 1
3 | x | 2
4 | x | 3


I want this:


ID | URL
1 | x
2 | x -> It is anyone url
3 | x


I try SQL below, but this repeat ID =(


SELECT a.advertisement_id as ID, p.url as URL
FROM Advertisement a
RIGHT JOIN Photo p
ON a.advertisement_id = p.advertisement_id
LIMIT 6


Result:


ID | URL
1 | http://i0.statig.com.br/bancodeimagens/18/46/2h/18...
2 | http://www.yaves.es/images/Animales/Tiernos-Gatito...
2 | http://images.forwallpaper.com/files/thumbs/previe...
10 | http://www.petmag.com.br/img/gatos/racas/7892/euro...
7 | http://www.gmstatic.com/content/images/1369932859_...
8 | http://www.navegandonaweb.com/wp-content/uploads/2...


Anyone help me construct the sql query?

Answer Source
SELECT a.advertisement_id as ID, p.url as URL  
FROM Advertisement a  
RIGHT JOIN Photo p
ON a.advertisement_id = p.advertisement_id
GROUP BY a.advertisement_id
ORDER BY p.photo_id ASC (or DESC ? )
LIMIT 6

OR

SELECT a.advertisement_id as ID, p.url as URL  
FROM Advertisement a,Photo p
WHERE a.advertisement_id = p.advertisement_id
GROUP BY a.advertisement_id
ORDER BY p.photo_id ASC (or DESC ? )
LIMIT 6
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download