mohammed siddiq mohammed siddiq - 4 years ago 96
SQL Question

select columns from different tables with different types without using joins

Here's the situation I have a table 'teachers' whose columns are (tid, tname)
and I have a table 'students' whose columns are (sid,sname). Assuming they have m:n relationship, I have one more table t_s (tid,sid) where both tid and sid are referencing tid of teachers and sid of students respectively.
Now I want to find: The teachers name(with their tid's) and the number of students under each teacher.[without using Joins or cartesian product]

The following query returns me the count with tid :

SELECT t.tid, count(*) as numberofstudents
FROM t_s t
GROUPBY by t.tid;


How will I match the tname with the respective tid, tried with union:

SELECT t.tid, count(*) as numberofstudents
FROM t_s t
GROUP BY t.tid
UNION
SELECT t1.tid,t1.tname
FROM teachers t1
WHERE t1.tid in (SELECT t2.tid
FROM t_s t2 )


Discovered that it doesn't work because the column types are different.

Is there a way to achieve it?
Thank you!

Answer Source

I'm also curious about why

without using Joins or cartesian product

however, you can try this:

SELECT 
    t.tid,
    COUNT(*) as numberofstudents,
    (SELECT t1.tname FROM teachers t1 WHERE t1.tid = t.tid) AS tname
FROM t_s t
GROUP BY by t.tid;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download