CODEWITHSUNDEEP
mysqlsqljoinsubquery
Circas Creed
Circas Creed

Reputation: 111

MySql join multiple select subquery

-SOLUTION-

SELECT Table1.unvan, Table2.sayi FROM 
(SELECT id , unvan FROM unvan WHERE id IN 
(SELECT unvani FROM personel WHERE gorev_yeri IN 
(SELECT id FROM gorev_yeri WHERE il_id="34") ) ) AS Table1 
JOIN
( SELECT unvani as id , count(*) AS sayi FROM personel WHERE gorev_yeri IN (SELECT id FROM gorev_yeri WHERE il_id="34") GROUP BY unvani ) AS Table2 
ON Table1.id = Table2.id

-EDIT-

When I use join with as I get this error : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS table1

I have 2 query and I want join them.

It is first.

SELECT id
     , unvan 
  FROM unvan 
 WHERE id IN (SELECT unvani 
                FROM personel 
               WHERE gorev_yeri IN (SELECT id 
                                      FROM gorev_yeri 
                                     WHERE il_id="34")
              )

It is second one.

SELECT unvani as id
     , count(*) AS sayi 
 FROM personel 
WHERE gorev_yeri IN (SELECT id 
                       FROM gorev_yeri 
                        WHERE il_id="34") GROUP BY unvani

I need this:

unvan|sayi

--------|----

Upvotes: 0

Views: 1548

Answers (2)

jmai
jmai

Reputation: 94

Based from error provided

It should be like this:

SELECT `Table1`.unvan, `Table2`.id
FROM
(
    SELECT id
    , unvan 
   FROM unvan 
   WHERE id IN (SELECT unvani 
            FROM personel 
           WHERE gorev_yeri IN (SELECT id 
                                  FROM gorev_yeri 
                                 WHERE il_id="34")
          )
) AS `Table1`

JOIN

(
SELECT unvani as id
 , count(*) AS sayi 
 FROM personel 
 WHERE gorev_yeri IN (SELECT id 
                   FROM gorev_yeri 
                    WHERE il_id="34") GROUP BY unvani
) AS `Table2`

ON `Table1`.id = `Table2`.id

Upvotes: 2

user8084576
user8084576

Reputation:

(SELECT id , unvan FROM unvan WHERE id IN (SELECT unvani FROM personel WHERE gorev_yeri IN (SELECT id FROM gorev_yeri WHERE il_id="34") ) )table1

join

( SELECT unvani as id, count(*) AS sayi FROM personel WHERE gorev_yeri IN (SELECT id FROM gorev_yeri WHERE il_id="34") GROUP BY unvani )table2

on table1.id=table2.id;

Upvotes: 1

Related Questions