MySQL query help with subquery

[eluser]jonnyjon[/eluser]
Hi,
I have three tables:
[urls] id, url
[url_tags] url_id, tag_id
[tags] tag_id

I would like to select N records from the "urls" table as well as all the tags for each url.

The way I'm doing it now is:

1. Selected the urls from the urls table
2. Loop through each url and select the tags

While this works it results in dozens of mysql statements...

Is there a better way to get the results?

[eluser]Randy Casburn[/eluser]
Clarify criteria established with this please "select N records from the 'urls' table" ... then my dumb brain can wrap around the the rest of the where clause.

Randy

[eluser]jonnyjon[/eluser]
Ok, thanks. Here's a simplified version of the php

$array = "SELECT a.id, a.url FROM urls LIMIT 5"

foreach($array as $row) {
$url_id = $row['id'];
$tags = "SELECT a.tag from tags a JOIN page_tags b on a.id = b.tag_id WHERE b.url_id = $url_id"
}

As you can see, its one query to get the urls then an additional query PER url to get each of its tags. Seems quite inefficient.

[eluser]Randy Casburn[/eluser]
While I take a look a this, are you using MySQL or Postgres? I see ANSI sql here so don't want to give you back MySQL specific syntax.

Randy

[eluser]jonnyjon[/eluser]
MySQL

[eluser]Randy Casburn[/eluser]
Here you go then:

Code:

SELECT
  u.url,
  t.tag
FROM
  urls u,
  tags t,
  page_tags pt,
  (
   SELECT
      uu.url_id
   FROM urls uu
   ) as UU
WHERE
  UU.url_id = pt.url_id
AND pt.tag_id = t.tags_id
AND uu.url_id = u.url_id;

Randy

[eluser]jonnyjon[/eluser]
Thanks Randy.

[eluser]Randy Casburn[/eluser]
You got it, glad to help.

Randy

[eluser]unosoft[/eluser]
If I understand your question correctly - give me all the tags for a given URL, then this query should work.

SELECT urls.id, url, url_tags.tag_id
FROM urls, tags
WHERE urls.id = tags.url_id
ORDER by 2,3