SELECT an id while counting rows from another table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

SELECT an id while counting rows from another table

Post 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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
Post Reply