Databases
StackOverflow2013
Postsdbo
POA question about JOIN
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POA question about JOIN
    text
    copied!<p>I need to do something like this to fill a parts table:</p> <pre><code>SELECT (CASE t1.part IS NULL THEN t2.part ELSE t1.part END) AS partno, t3.desc FROM t1 LEFT JOIN join t2 ON [certain condition] LEFT JOIN t3 ON t1.part = t3.part OR t2.part = t3.part </code></pre> <p>...so this will select the value for partno from t2 in case that part is null in t1, then i need to take the description from t3 but when I run it it takes forever and never return the results, How can I do this faster? if I am missing some details please ask.</p> <p>this are the tables</p> <p><a href="http://img15.imageshack.us/img15/3878/74385879.png" rel="nofollow noreferrer">alt text http://img15.imageshack.us/img15/3878/74385879.png</a></p> <p>this is the actual procedure</p> <pre><code>DELIMITER $$ DROP PROCEDURE IF EXISTS `getMonthDetail` $$ CREATE DEFINER=`root`@`%` PROCEDURE `getMonthDetail`(fechai Date, wid int) BEGIN select distinct ins.inventoryinid, ( select group_concat(concat(documents.documentname,': ', inventoryin_documents.documentno)) from inventoryin_documents left join documents on documents.documentid=inventoryin_documents.documentid where inventoryin_documents.inventoryinid = docin.inventoryinid group by inventoryin_documents.inventoryinid )as docin, trace.inventoryoutid, ( select group_concat(concat(documents.documentname,': ', inventoryout_documents.documentno)) from inventoryout_documents left join documents on documents.documentid=inventoryout_documents.documentid where inventoryout_documents.inventoryoutid = docout.inventoryoutid group by inventoryout_documents.inventoryoutid ) as docout, outs.inventoryoutdate, (case when trace.partnumberp is null then indetails.partnumberp else trace.partnumberp end) as nopart, p.descriptionsmall, trace.quantity from inventoryin as ins left join inventoryinouttrace as trace on trace.inventoryinid = ins.inventoryinid left join inventoryin_documents as docin on docin.inventoryinid = ins.inventoryinid left join inventoryout_documents as docout on docout.inventoryoutid = trace.inventoryoutid left join inventoryout as outs on outs.inventoryoutid = trace.inventoryoutid left join inventoryindetails indetails on ins.inventoryinid = indetails.inventoryinid left join product as p on trace.partnumberp=p.partnumberp where ((ins.inventorydate &gt; fechai+0 and ins.inventorydate &lt; fechai+100) or (outs.inventoryoutdate &gt; fechai+0 and outs.inventoryoutdate &lt; fechai+100)); END $$ DELIMITER ; </code></pre> <p>and when I Hit the explain button in the query browser it returns a error...</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload