Select query from 2 tables

[safra]

Hi,

I have to 2 tables, with among others the following columns:

Table 1:
- uniqueid
- memberid

Table 2:
- uniqueid
- fullmember

I want to search in both tables where uniqueid matches in both tables and fullmember in Table 2 is '1'.

The problem I am having is the desired output. Ideally it should output a Total Number based on memberid in table 1, it should also output the value for this specific memberid and ideally the results should be ordered based on the Total Number with the highest number at the top.

Is this doable with a single mysql query?

Thanks!

[dalecosp]

Possibly??

Can you provide sample data to further explain the desired output? I mean, you don't even really say whether those are all numbers ... 😉

[safra]

Sorry...

table 1:

Uniqueid     memberid
1111                  2
2222                  1
3333                  2
4444                  3
5555                  2
6666                  2
7777                  1

table 2:

uniqueid  fullmember
1111               1
2222               1
3333               1
4444               1
5555               0
6666               1
7777               1

Output:

memberid        Total
      2                   3
      1                   2
      3                   1

Memberid 2 is listed at the top with a total of 3 results, this member is listed 4 times in table 1 but fullmember is 0 for the matching uniqueid in table 2 so thi one should not be included. memberid has 2 matches and fullmember is 1 on both matching id's in table 2. memberi only ha one listing so itis at the bottom.

Does this help?

Edit: I see the spaces between the column values are removed to 1 space only...

[Lars_Berg]

select t1.memberid,
       count(*) as total
  from t1
  join t2
    on t1.uniqueid = t2.uniqueid
 where t2.fullmember = 1
 group by t1.memberid
 order by total desc

[safra]

Thanks a lot Lars, works well!