Join query without foreign key

Databases
1

I need to query 2 tables that don’t have any relation. This is the situation. In de administration section I have a navigation for the site i.e. (Pages, Widges, Blog posts, Users etc. Obviously I have tables for Pages, Widgets, Blog posts, Users etc. What I need in the admin section Is to have the navigation items along with badges with the count of each table:

  • Pages 14
  • Widgets 8
  • Blog posts 8
  • Users 50

So bassically I need to have a query that puls the navigation info from the navigation table and the count from the pages table. And the same offcource for the other tables. How would I do that

Edit: I changed the structure a bit. I added an extra field dashboard_id to all tables (site_pages, site_widgets etc.) to match the primary_key from the dashboard navigation table and gave them all a default value. So the dashboard_id for all rows in the table site_pages is 1, in site_widgets 2 and so on. Than I thought that the following query would give me the desired result:

SELECT DN.*    
       (SELECT COUNT(*)
          FROM site_pages SP, site_widgets SW
         WHERE SP.dashboard_id = DN.dashboard_id
           AND SW.dashboard_id = DN.dashboard_id
           AND SP.isActive = 1
           AND SW.isActive = 1) AS counter
  FROM dashboard_navigation DN
 ORDER BY DN.dashboard_sequence

But unfortunately I was wrong. How should the query look like to get the desired results?

Another edit:

The above query is actually giving me results back, I forgot the comma after SELECT DN.* only the counter for both tables (site_pages and site_widgets is giving me 0. While when I run the query with just one table counter giving me the right number. So it has something to do with the alias counter I guess

2

I found the sollution. For anybody interessted. This is the query that gave me the right result

SELECT DN.*, 
       (SELECT COUNT(*) 
          FROM site_pages SP 
         WHERE SP.dashboard_id = DN.dashboard_id  
           AND isActive = 1)+
       (SELECT COUNT(*) 
          FROM site_widgets SW 
         WHERE SW.dashboard_id = DN.dashboard_id  
           AND isActive = 1)+
       (SELECT COUNT(*) 
          FROM blog_posts BP 
         WHERE BP.dashboard_id = DN.dashboard_id) AS counters
  FROM dashboard_navigation DN
3

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.