need mysql help

Discussion in 'Computer Games and General Discussion' started by MFDC12, Apr 4, 2011.

Apr 4, 2011

need mysql help by MFDC12 at 2:31 AM (494 Views / 0 Likes) 2 replies

  1. MFDC12
    OP

    Member MFDC12 GBAtemp Advanced Fan

    Joined:
    Oct 12, 2007
    Messages:
    761
    Country:
    United States
    I need some help with a mysql query but I can't figure it out for the life of me.
    I need to count the amount of people who were referred to the site by another member, as long as they have one post. This would not be a problem for me if they were not in two different tables and having needed a count (which I am having trouble with).

    Here are the two queries I want to join:
    SELECT DISTINCT u.ref_by, u.id FROM users AS u WHERE u.status = 'confirmed' GROUP BY u.ref_by ORDER BY refs DESC;
    SELECT COUNT(*) FROM history WHERE userid = u.id AND posts > 1";

    Problems:
    I dont know how to count the total amount.
    The part in bold, I am not sure how to do that. I have tried unions and all types of joins, but I can't seem to get this to be used from the first query (always results in something life "u.id unknown in where clause")

    Any help is appreciated, this has given me a headache for about 5 hours now and I give up.
    Thanks!
     
  2. Jiggah

    Member Jiggah GBAtemp Maniac

    Joined:
    Nov 9, 2002
    Messages:
    1,223
    Country:
    United States
    Why wouldn't you use the first select statement to find the people with one post?


    For example,

    SELECT DISTINCT u.ref_by, u.id FROM users as u, history WHERE u.id = userid and u.status = "confirmed" and post >= 1;

    You can then use that select statement within a count:

    SELECT COUNT(*) FROM (SELECT DISTINCT u.ref_by, u.id FROM users as u, history WHERE u.id = userid and u.status = "confirmed" and post >= 1) as CONUSER;


    The reason why your second query is messing up is because for that SELECT statement you're only referencing the "history" table. It doesn't know what u.id is.
     
  3. MFDC12
    OP

    Member MFDC12 GBAtemp Advanced Fan

    Joined:
    Oct 12, 2007
    Messages:
    761
    Country:
    United States
    You got exactly what I'm trying to do except for one thing - i need it so it groups it by ref_by, rather than a total number.

    Thanks for helping so far!
     

Share This Page