Page 1 of 1

MySQL: How manys rows reference this row?

Posted: Fri May 30, 2003 4:11 am
by Gleeb
First up, here's my structure

`intranet`.`page`

Code: Select all

+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| PageID | int(10) unsigned |      | PRI | NULL    | auto_increment |
| Title  | tinytext         |      |     |         |                |
+--------+------------------+------+-----+---------+----------------+
`intranet`.`content`

Code: Select all

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| ContentID | int(10) unsigned |      | PRI | NULL    | auto_increment |
| PageID    | int(10) unsigned |      |     | 0       |                |
| Title     | tinytext         |      |     |         |                |
| Body      | text             |      |     |         |                |
| UserID    | int(10) unsigned |      |     | 0       |                |
| Order     | tinyint(4)       |      |     | 0       |                |
+-----------+------------------+------+-----+---------+----------------+
Now, my problem is, how would I get the amount of rows in `intranet`.`content` that reference a row in `intranet`.`page`, bearing in mind that a `content` row may not reference a page, which indicates it is to appear on all pages.

Preferably, it should be done in a single SQL statement, but take as many as you need.

This one has me beat :(

Posted: Fri May 30, 2003 4:28 am
by Gleeb
Got it!

Code: Select all

SELECT *, COUNT(*) 
FROM `page`
LEFT JOIN `content` ON `page`.`PageID` = `content`.`PageID` 
GROUP BY `page`.`PageID`;
It's a bit of a hack, but it works... gonna refine it to SELECT only what's needed. I'm still open to better suggestions though :)

Did U Mean?

Posted: Fri May 30, 2003 4:30 am
by coolpravin
Did you mean to say

Code: Select all

$sql="select content.* from content,page where
content.PageID=page.PageID and page.PageID='$no'
Replace $no with respective no always
or

Code: Select all

$sql="select content.* from content,page where
content.PageID=page.PageID and page.title='$title'
Replace $title with appropriate.

Or if U want for all

Code: Select all

$sql="select content.* from content,page where
content.PageID=page.PageID group by page.PageID
What actually you want to do .
Please clarify
Does any of above helped?

Posted: Fri May 30, 2003 4:44 am
by Gleeb
Well, that last one sure is a funny way to hack in a join ;) but basically, I needed a COUNT() of the rows in which the `PageID`s matched for each unique PageID, along with various bits of information concerning the pages or their content.