Join 4 tables

[rashmi_k28]

Hi,

 

Here is the queries below.

How to join all the tables into one single query

 

select cid from c_table limit 1;

select * from g_table where cid='4';

select * from a_table where gid='43';

select count(*) from k_table where aid='48';

 

I have to remove limit from c_table,

I have to fetch all the cid's from c_table where count(*) from k_table is greater than 1

[kickstart]

Hi

 

Which fields are on which tables , which can be used to link the rows together?

 

All the best

 

Keith

[rashmi_k28]

In g_table contains gid,cid,

a_table contains aid,gid

k_table contains kid,aid

[kickstart]

Hi

 

Something like this:-

 

SELECT a.cid, count(kid) as KidCount
FROM c_table a
INNER JOIN g_table b ON a.cid = b.cid
INNER JOIN a_table c ON b.gid = c.gid
INNER JOIN k_table d ON c.aid = d.aid
GROUP BY a.cid
HAVING KidCount > 1

 

All the best

 

Keith

[rashmi_k28]

Thanks for the query. This works.

How to get the count of a.cid where KidCount >=1

 

SELECT a.cid, count(kid) as KidCount
FROM c_table a
INNER JOIN g_table b ON a.cid = b.cid
INNER JOIN a_table c ON b.gid = c.gid
INNER JOIN k_table d ON c.aid = d.aid
GROUP BY a.cid
HAVING KidCount > 1

[kickstart]

Hi

 

SELECT a.cid, count(kid) as KidCount
FROM c_table a
INNER JOIN g_table b ON a.cid = b.cid
INNER JOIN a_table c ON b.gid = c.gid
INNER JOIN k_table d ON c.aid = d.aid
GROUP BY a.cid
HAVING KidCount >= 1

 

That should work.

 

All the best

 

Keith