Combine queries in MySQL

Question

I have two table in MySQL that looks as follows:

ID  Name    Information
1   A       fsdf
2   B       ada
3   A       dsafd
4   A       retret
5   C       asdfsa
6   B       xzc

and,

P_ID    Name    Loc_X   Loc_Y
1       A       2       3
2       B       3       4
3       C       4       5

I would like to run a query in MySQL that return the result as follows:

NAME    COUNT   Loc_X   Loc_Y
A       3       2       3 
B       2       3       4
C       1       4       5

Currently, I am able to execute the following query:

SELECT Name,COUNT(*) as count FROM Table_A GROUP BY Name ORDER BY count DESC;

to get the following result:

NAME    COUNT
A       3
B       2
C       1

I know that probably I can use this result to extract only the "Name" and fire another query to get the Loc_X and Loc_Y using PHP, but I was wondering whether there is a moe efficient way of doing it using DML. Is there a way to nest the queries?

tried inner join? `SELECT a.Name,Count(*) as count,b.Loc_X,b.Loc_Y FROM Table_A a INNER JOIN Table_B ON a.Name = b.Name ORDER BY count DESC` — UnskilledFreak, Dec 11 '14 at 18:38

score 1 · Accepted Answer · answered Dec 11 '14 at 18:38

1

Try something like this :

SELECT Table_A.Name,COUNT(*) as count, Table_B.Loc_X, Table_B.Loc_Y
FROM Table_A 
INNER JOIN Table_B ON Table_A.name = Table_B.name
GROUP BY Table_A.Name, Table_B.Loc_X, Table_B.Loc_Y 
ORDER BY count DESC;

answered Dec 11 '14 at 18:38

Fabien TheSolution

5,055
1
18
30

score 0 · Answer 2 · answered Dec 11 '14 at 18:42

0

I think you just need to join the tables on the common field here.

Try:

SELECT a.Name, count(a.id) AS Count b.Loc_X, b.Loc_Y
FROM Table_A a
INNER JOIN Table_B b ON a.Name = b.Name
GROUP BY a.Name

answered Dec 11 '14 at 18:42

Chuck

196
1
6

Combine queries in MySQL

2 Answers2