0

I have this query which is going through table B twice I want it go through tableB once as I should be able to select all necessary fields from a table once and use that selected result table in multiple joins. select tableA.aa,tableA.bb, tableK.address, tableP.address from tableA LEFT JOIN ( SELECT tableB.ID, tableB.Address, tableD.Name, tableC.tableCName, tableC.Country FROM tableB INNER JOIN tableD ON tableB.tableDID = tableD.ID AND tableD.Deleted = 0 INNER JOIN tableC ON tableD.tableCID = tableC.ID WHERE tableB.Deleted = 0 ) AS tableP ON tableP.ID = tableA.aa LEFT JOIN ( SELECT tableB.ID, tableB.Address, tableD.Name, tableC.tableCName, tableC.Country FROM tableB INNER JOIN tableD ON tableB.tableDID = tableD.ID AND tableD.Deleted = 0 INNER JOIN tableC ON tableD.tableCID = tableC.ID WHERE tableB.Deleted = 0 ) AS tableK ON tableK.ID = tableA.bb

  • If Your MySQL version is 8, You can use CTE (WITH clause - dev.mysql.com/doc/refman/8.0/en/with.html ). – Akina May 16 '18 at 5:50
  • @Akina Thanks for reply. MYSQL version I am using is 5.6 – Abdur Rehman May 16 '18 at 5:54
  • @Akina Sorry as joined on different values tableA.aa,tableA.bb its not fetching duplicates it is fetching different details from different tables.In fact this query is a part of a bigger query I just cut this section out here as it was bothering. so the need is to avoid full table scan twice. – Abdur Rehman May 16 '18 at 7:27
  • The only way I see on Your server version is to convert the subquery to the VIEW form. But I'm not sure... And if You need only one field Address from the subquery I think it's safe to remove another fields from its output. – Akina May 16 '18 at 7:45
  • @akina yes I can remove extra fields but original problem is still there I have written same query subquery twice... – Abdur Rehman May 16 '18 at 9:47
0

I have written same query subquery twice

Create VIEW (created once, it exists in database until ALTER or DROP, like tables):

CREATE VIEW my_subquery AS
SELECT tableB.ID
     , tableB.Address
--   , tableD.Name
--   , tableC.tableCName
--   , tableC.Country
FROM
    tableB
INNER JOIN tableD ON tableB.tableDID = tableD.ID AND tableD.Deleted = 0
INNER JOIN tableC ON tableD.tableCID = tableC.ID
WHERE
    tableB.Deleted = 0;

and then use it in Your query like a common table:

SELECT tableA.aa, tableA.bb, tableK.address, tableP.address 
FROM tableA
LEFT JOIN my_subquery AS tableP ON tableP.ID = tableA.aa
LEFT JOIN my_subquery AS tableK ON tableK.ID = tableA.bb;
  • You are awesome...... Best solution... Thanks – Abdur Rehman May 17 '18 at 7:04
0

see http://sqlfiddle.com/#!9/bd9bbc/3

select aa, bb, max(paddress), max(kaddress) from ( 
  select tableA.aa,tableA.bb,
    if(tableA.aa = tableP.id, tableP.address, null) paddress,
    if(tableA.bb = tableP.id, tableP.address, null) kaddress
    from tableA
          LEFT JOIN (
      SELECT
          tableB.ID,
          tableB.Address,
          tableD.Name,
          tableC.tableCName,
          tableC.Country
      FROM
          tableB
      INNER JOIN tableD ON tableB.tableDID = tableD.ID
      AND tableD.Deleted = 0
      INNER JOIN tableC ON tableD.tableCID = tableC.ID
      WHERE
          tableB.Deleted = 0
  ) AS tableP ON tableP.ID = tableA.aa or tableP.ID = tableA.bb
) A  group by aa, bb;:

result:

aa  bb      max(paddress)           max(kaddress)
1   2       Langestraat 35 B        (null)
3   4       (null)                  Stationstraat 1
5   6       Weggevoerdenlaan 5B     Veemarkt 5
  • Thanks... Highly appreciate your effort and help..... but unfortunately your solution is extremely expensive on database and processor I can not afford that anyways thanks. – Abdur Rehman May 17 '18 at 7:03
  • Do you have foreign keys defined on your tables? See sqlfiddle.com/#!9/9ec879/1. Without them you may be missing a number of indices that could improve performance. – Gerard H. Pille May 17 '18 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.