Databases
StackOverflow2013
Postsdbo
POfetching data from different tables based on different joins in single query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POfetching data from different tables based on different joins in single query
    primarykey
    data
    text
    <p>the query returns perfect results when <code>invstorelocation</code>.<code>Description</code> AS Location , is not included in the query along with the join</p> <pre><code> INNER JOIN `invstorelocation` ON (`invlabtes`.`StockLocCode` = `invstorelocation`.`Code`) </code></pre> <p>But when I include this join in the query the table returns empty even though all conditions are fully met. Plz mention the alternative to fetch Location record along with other records.</p> <pre><code> SELECT `invlabtes`.`CommAppCode` , `invlabtes`.`Code` , `invlabtes`.`GatePassNO` , `invlabtes`.`VehicleNo` , `invlabtes`.`SupplierCode` , `invstorelocation`.`Description` AS Location , `invstore`.`Description` AS MaterialName FROM `invlabtes` INNER JOIN `invstorelocation` ON (`invlabtes`.`StockLocCode` = `invstorelocation`.`Code`) INNER JOIN `invgatepass` ON (`invlabtes`.`GatePassNO` = `invgatepass`.`InwardNo`) AND (`invlabtes`.`GatePassDate` = `invgatepass`.`EntryDate`) AND (`invlabtes`.`VehicleNo` = `invgatepass`.`VehicleNo`) INNER JOIN `invgatepasssubtable` ON (`invlabtes`.`GatePassNO` = `invgatepasssubtable`.`InwardNo`) AND (`invlabtes`.`ItemCode` = `invgatepasssubtable`.`ItemCode`) INNER JOIN `invstore` ON (`invgatepass`.`StoreCode` = `invstore`.`Code`) WHERE (`invlabtes`.`Code` LIKE '%%' OR `invlabtes`.`CommAppCode` LIKE '%%' OR `invlabtes`.`GatePassNO` LIKE '%%' OR `invlabtes`.`VehicleNo` LIKE '%%' OR `invlabtes`.`SupplierCode` LIKE '%%') AND `invlabtes`.`CommAppCode`&lt;&gt;'' AND `invlabtes`.`Unit_Code`='1' LIMIT 100; </code></pre> <p>This query successfully returns the data. The above query has problem.</p> <pre><code> SELECT `invlabtes`.`CommAppCode` , `invlabtes`.`Code` , `invlabtes`.`GatePassNO` , `invlabtes`.`VehicleNo` , `invlabtes`.`SupplierCode` , `invstore`.`Description` AS MaterialName FROM `invlabtes` INNER JOIN `invgatepass` ON (`invlabtes`.`GatePassNO` = `invgatepass`.`InwardNo`) AND (`invlabtes`.`GatePassDate` = `invgatepass`.`EntryDate`) AND (`invlabtes`.`VehicleNo` = `invgatepass`.`VehicleNo`) INNER JOIN `invgatepasssubtable` ON (`invlabtes`.`GatePassNO` = `invgatepasssubtable`.`InwardNo`) AND (`invlabtes`.`ItemCode` = `invgatepasssubtable`.`ItemCode`) INNER JOIN `invstore` ON (`invgatepass`.`StoreCode` = `invstore`.`Code`) WHERE (`invlabtes`.`Code` LIKE '%%' OR `invlabtes`.`CommAppCode` LIKE '%%' OR `invlabtes`.`GatePassNO` LIKE '%%' OR `invlabtes`.`VehicleNo` LIKE '%%' OR `invlabtes`.`SupplierCode` LIKE '%%') AND `invlabtes`.`CommAppCode`&lt;&gt;'' AND `invlabtes`.`Unit_Code`='1' LIMIT 100; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USA.M
    1. USA.M
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you sure you know what this query is supposed to do?
      singulars
      1. POfetching data from different tables based on different joins in single query
      1. USasprin
    2. COyes this query is fetching data from invlabtes table and invstore table on basis of matching entries in invgatepass table and invgatepasssubtable. I also want to fetch corresponding location description from invstorelocation table
      singulars
      1. POfetching data from different tables based on different joins in single query
      1. USA.M
    3. COThere's really no way for us to investigate without having your data as well. I'm sorry, but if you're not geting any rows, then clearly all conditions are ***not*** fully met. DBs don't just refuse to show data. The conditions include the conditions of the join, and the only way to figure it out is to dig in and figure out why the conditions aren't met when you add the join. That might be easier to do by changing that join that causes it to fail to an outer join and looking at the records.
      singulars
      1. POfetching data from different tables based on different joins in single query
      1. USDavid
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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