Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: One more SELECT question...

    Hi,

    Given the query below (courtesy of roga)
    Code:
    SELECT *
    FROM logins
    LEFT JOIN profiles USING (some_id)
    LEFT JOIN prefs USING (some_id)
    LEFT JOIN avatars ON profiles.avatar_id=avatars.avatar_id
    LEFT JOIN groups ON profiles.group_id=groups.group_id
    LEFT JOIN tmpl ON prefs.tmpl_id=tmpl.tmpl_id
    WHERE username='john'
    I would like to add another clause to the query to count the number of rows from another table given certain conditions. On it's own, that new query looks like this:
    Code:
    SELECT COUNT(*) FROM message where username=sender
    How do I append that to the group of LEFT JOINs query above? The COUNT(*) should only apply to the query on the 'message' table and not only the other tables.

  2. #2
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    My solution is to have two separate SELECT statements for the two queries. Let me know if I'm missing something terribly obvious

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    There 'ya go... why be "clever?" Just run two queries. If you need more, write a few more queries that work with the output from that. It's really easy to write a "write-only query," if you know what I mean.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    thanks, sundialsvcs!

    it was more of a case of not knowing what my options are.

    i had this funny idea that you try get as much as you can with a single query. i didn't know multiple selects for a related transaction was an option

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •