Results 1 to 2 of 2

Thread: MySQL query help...

  1. #1
    Spinal Pap Tomahawk's Avatar
    Join Date
    Jul 2003
    Location
    Bristol/Manchester Uni
    Posts
    1,002
    Thanks
    8
    Thanked
    13 times in 8 posts

    MySQL query help...

    Put very simply I have two tables:

    - users
    - briefs

    I am trying to SELECT several fields from the 'users' table but within the WHERE clause the 'users.userID = briefs.userID'.

    Here's my problem; that users may have a row in the users table but not necessarily in the briefs table. So if there is no corresponding row to be found where the two ID's match I obviously get no row returned.

    So how do I select all users with AND without an entry in the briefs table (or for example displaying '0' for those without an entry in the briefs table) from a query?

    Currently I have:
    ---
    SELECT users.userID, users.prefix, users.firstname, users.surname, users.lastlogin, COUNT(briefs.briefID) AS briefs
    FROM users, briefs
    WHERE (users.userID = briefs.userID)
    GROUP BY briefs.userID
    ORDER BY users.surname
    ---

    So I realise the WHERE clause is causing those rows without a corresponding entry in the BRIEFS table to not be displayed. So my question is how to return all those values currently being returned with the SQL above; plus displaying '0' (zero) for those without a corresponding row in the briefs table.

    I've been looking at this and thinking I may need to use a CASE or IF function; but thought there may be a simpler way I'm just missing!

    Many thanks,
    Lewis.


    [ iTomaHawk | My Music MySpace ]

  2. #2
    Spinal Pap Tomahawk's Avatar
    Join Date
    Jul 2003
    Location
    Bristol/Manchester Uni
    Posts
    1,002
    Thanks
    8
    Thanked
    13 times in 8 posts

    Re: MySQL query help...

    /ignore! ..doh.

    Sorry had a funny five; after speaking to someone realised I need to use JOINS.. and a LEFT JOIN in this case.


    [ iTomaHawk | My Music MySpace ]

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help connecting to a remote MySQL server...
    By chicken in forum Software
    Replies: 10
    Last Post: 27-11-2007, 11:52 AM
  2. MySQL extension for PHP 5.2.3 not working
    By Jerrythafast in forum Help! Quick Relief From Tech Headaches
    Replies: 18
    Last Post: 13-06-2007, 08:03 PM
  3. ASP querystring database query
    By pritsey in forum Software
    Replies: 2
    Last Post: 30-12-2006, 01:31 AM
  4. RMA Query
    By billythewiz in forum SCAN.care@HEXUS
    Replies: 7
    Last Post: 07-11-2006, 05:44 PM
  5. Making PHP and MySQL Talk?
    By Dav0s in forum Software
    Replies: 4
    Last Post: 18-09-2005, 10:53 PM

Posting Permissions

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