Left Join Mutliple Tables

2012-12-28  Source: Original Site  Category:PHP  Views:0 

Advertisement

I have this query:

SELECT articles . *, article_tags.art_id, COUNT( article_tags.id ) AS appres, COUNT( article_comments.art_id ) AS comms
FROM articles
LEFT JOIN (
article_tags, article_comments
) ON ( article_tags.art_id = articles.id
AND article_comments.art_id = articles.id
AND article_tags.art_id = article_comments.art_id )
GROUP BY id

The simplified table structure is like

articles article_comments article_tags
--------- ---------------------- --------------
id id id
art_id art_id

If I left join just articles to comments or just articles to tags, it shows the correct count number. When I join both it completely messes up the number in no logical way to me.

Related articles
  • Left Join Mutliple Tables 2012-12-28

    I have this query: SELECT articles . *, article_tags.art_id, COUNT( article_tags.id ) AS appres, COUNT( article_comments.art_id ) AS comms FROM articles LEFT JOIN ( article_tags, article_comments ) ON ( article_tags.art_id = articles.id AND article_c

  • Get values from two rows based on values in one row when left joining two tables. 2012-07-01

    I have two tables, one with a list of users like so: "users" table user_id user_type user_first user_last 22 admin Bob Bobson 34 author Sally Sallerton And one with let's say forum posts like so: "posts" table post_id post_author post_admin 7856 34 2

  • LEFT JOIN multiple tables 2012-12-03

    I have been to this site countless times reading the forums getting my questions answered and it is much appreciated. One question I have is about LEFT JOINS. I have a roster for a module in PHPNuke I did called MILPACS. The development site is locat

  • Left Join two tables---so slow! 2013-10-08

    I'm trying to find all members in the CBA table who have a record in either the DUES_PAID table with 2008 in the DUES_Year column or who have 2009 in the itemname column of paypal_payment_info. This works but takes about two minutes! Any suggestions

  • MySQL Left join multiple tables 2014-01-24

    I want to join more than one table using left join. I have a table that stores shop profile where id field is primary key. This id field is foriegn key in orders, products and customers table. Now i want to show a listing in one query. I tried follow

  • MySQL how to optimize left join two tables? 2015-03-27

    Hi,please to help me to solve the following problem. I want to join two tableļ¼Œusing the following clause: create table a as select b.*,c.* from b left join c on b.pno=c.pno. Both in table b and c, I create index on pno. There are 100,000 items in tab

  • MySQL Bad performance left join on table keys 2015-04-02

    Today I migrated an MS Access database to the latest MySQL containing interface files I have to compare, because Access is grinding to a halt as the DB is growing. I am converting a legacy interfacing system and I have to make sure that the new syste

  • Left Joins where tables are query sources 2013-11-24

    I'm building an incredibly irritating omnibus report for one of my managers and I'll confess I'm stuck. I've managed to cram 5 queries into one, but I can't for the life of me figure out how to join them, right now I'm using an = operator in the wher

  • MySQL How to left join a table to itself and show all the options in a column. 2012-10-18

    I have the following sample table. +---------+-------+ | REGION | PRICE | +---------+-------+ | CENTRAL | 10 | | METRO | 10 | | CENTRAL | 20 | +---------+-------+ If I run the following query notice I get both REGION columns. mysql> SELECT REGION,

  • left join 2 tables multiple times 2014-06-12

    I am having a bit of a dilemma I have products in a table with multiple categories and need to retrive each relationship as its value (foreigne key as value) this would be simple if it were a single relationship connection but I need to relate the tw

  • Can I LEFT JOIN more than two tables 2012-03-04

    Let's see if I can describe this succinctly: I have several tables that I want to join. Primarily, I'm looking for invoice records (invoices) which don't have any line item entries (in transactions). Here's a query which works: select invoices.ID, in

  • Oracle Left Join gives no row despite first table not being empty 2012-04-27

    I'm using Oracle 9.2.0.1 on windows. I'm having this strange situation : select count(*) from folder; -> 410 If I left join this table with another one, I expect to have at least 410 rows: SELECT DISTINCT F.keyfolder,DFV5.val_string AS SLC4 FROM f

  • Oracle Add third table with Left Join 2012-08-07

    I have written the following query. SELECT /*+NO_MERGE(INV1)*/ vbc.acc_rk FROM a vbc LEFT JOIN b stfc ON vbc.acc_rk = stfc.acc_rk LEFT JOIN (SELECT MAX (src.party_src_rk), acc_rk, country_cd FROM rcdwstg.stg_fin_acc_party_src src, rcdwstg.stg_party_s

  • LEFT JOIN more than 2 tables? 2013-09-10

    OK, I have three tables: tb1, tb2, tb3 with fields below: + tb1: id, id1, id2 (main table) + tb2: id1 + tb3: id2 How to make a select statement with left join three tables, display all fields in three tables? In 'id1' and 'id2' in table tb1, one of t

  • Oracle Left join for three tables 2014-09-05

    I have written the following sample query, SELECT a.acc_rk FROM a, b, c WHERE a.acc_rk = b.acc_rk AND b.acc_rk = c.acc_rk; Now i need to change equi join to Left join I have to Left join table "a" with table "b" and again i need to do Left join "b" t

  • MySQL Slow left join query because of SUM operator 2012-02-05

    I have to tables ... CREATE TABLE `treffen_allgemein` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL DEFAULT '0', `ort` longtext NOT NULL, `datum_start` int(11) NOT NULL DEFAULT '0', `datum_ende` int(11) NOT NULL DEFAULT '0', `uhrzei

  • MySQL LEFT JOIN confusion... 2012-07-17

    there, like every other MySQL novice alive I'm having trouble with LEFT JOIN. All the examples and tutorials I find online are either absurdly simply, or horribly convoluted, and I'm having trouble grasping a key principle. Let's say you have these t

  • join a table based on a previously joined tables column value 2012-08-12

    I need to join a table thats name is a value in another table that was joined onto the query, here is my query: SELECT * FROM flare_objects_obj LEFT JOIN flare_objects_links ON (flare_objects_links.group_id = flare_objects_obj.id) LEFT JOIN flare_obj

  • Double Left Join? 2013-01-24

    Helllo.... I need to do a query that left joins a table conditionally. select t.*, v.vwap, a.moniker,v.dateTime from accounts a, stocks s, trades t left join vwaps v on t.[date] = v.dateTime where a.accountNumber = t.accountNumber AND t.symbol = s.sy

  • Left Join + count 2013-02-03

    Can I left join another table and istead of outputting it's results, just count how many instances of a particulat item entered into that table. Something like that?... SELECT * FROM table1 LEFT JOIN table2 ON ( table1.item1 = table2.item2 ) COUNT ta