Page 1 of 1

SELECT an id while counting rows from another table

Posted: Thu Nov 24, 2005 8:03 pm
by kendall
Hi,

i am developing a query where by i want to select information from 1 table1 and count the corresponding rows of another table2 while i select the unique row from that table2 corresponding table

e.g.

Code: Select all

---table2---
id | foregin key 
1  |	2
2  |	2
3  |	2
4  |	1
5  |	1
6  |	6
----table1---
id 
1
2
3
4
5
6
thus i need to pull
1,2,6 from table1 counting each occurance in table2 and selecting 1,4,6 from table2

i have com up with this thus far

Code: Select all

SELECT DISTINCTROW
  `galleries`.`GalleryID`,
  `galleries`.`Title`,
  `galleries`.`Description`,
  COUNT(`photos`.`Gallery`) AS `photoCount`,
  `photos`.`PhotoID`
FROM
  `galleries`,
  `photos`
WHERE
  (`galleries`.`Status` = 1)
GROUP BY
  `galleries`.`GalleryID`,
  `galleries`.`Title`,
  `galleries`.`Description`,
  `photos`.`Gallery`
i get the photoCount for each table and i get a photoID....but the GalleryID are being duplicated twice in the results for each result table
im puzzled why as i have selected using DISTINCTROW

anyone care to point out the problem or a solutions

Kendall

Posted: Thu Nov 24, 2005 8:12 pm
by kendall
Ok,

i think i got it

Code: Select all

SELECT 
  `galleries`.`GalleryID`,
  `galleries`.`Title`,
  `galleries`.`Description`,
  `photos`.`Gallery`,
  COUNT(`photos`.`PhotoID`) AS `photoCount`,
  `PHOTOS`.`PhotoID`
FROM
  `galleries`,
  `photos`
WHERE
  (`galleries`.`Status` = 1)
GROUP BY
  `galleries`.`GalleryID`,
  `galleries`.`Title`,
  `galleries`.`Description`,
  `photos`.`Gallery`
HAVING
  (`photos`.`Gallery` = `galleries`.`GalleryID`)
if anyone cares to validate it for me let me know something ok?

thanks