MySQL: How manys rows reference this row?

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
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

MySQL: How manys rows reference this row?

Post 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 :(
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Post 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 :)
User avatar
coolpravin
Forum Newbie
Posts: 20
Joined: Mon May 19, 2003 12:56 am
Location: India

Did U Mean?

Post 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?
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

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