Complicated database query assistance

[Champak]

...complicated for me at least.

So my query spans 3 tables...all made up of different structures...to get the result that I need, and I'm having trouble figuring out how to execute it.

Table 1 = this is the main one I'm working with. This is for the "package" it contains a "package_id" column......one row contains all the info needed.

Table 2 = this contains the "package" id and the "item" id......one "package" can span 2 to X amount of rows. Column one will be the "package_id" which can repeat 2 to X amount of rows, and column two will be the "item_id". (Sometimes items can be a part of multiple packages). 

Table 3 = this contains the "items" data which will have the amount value.....one row contains all the info needed. So let's say the "item_id" is column 1 and the "amount" is column 2.

I need to eliminate any "package" (row) from Table 1 (not from the actual database, just from my query) if ANY of the "items" within the "package" have a value of = 0 which is in Table 3.

All three tables will be of different size/structure so I believe that eliminates the use of LEFT JOIN, which is really throwing me off, so I can't do everything in one query.

I'm lost on how to proceed with the proper database queries.

So let's say the following is my 3 tables and the last one is the result of the query:

 

expandcollapse popup

Table 1
<table>
  <tr>
    <td>packageID</td><td>something1</td><td>something2</td>
  </tr>
  <tr>
    <td>1</td><td>something1</td><td>something2</td>
  </tr>
  <tr>
    <td>2</td><td>something1</td><td>something2</td>
  </tr>
  <tr>
    <td>3</td><td>something1</td><td>something2</td>
  </tr>
  <tr>
    <td>4</td><td>something1</td><td>something2</td>
  </tr>
</table>

Table 2
<table>
  <tr>
    <td>packageID</td><td>itemID</td><td>something2</td>
  </tr>
  <tr>
    <td>1</td><td>400</td><td>something2</td>
  </tr>
  <tr>
    <td>1</td><td>300</td><td>something2</td>
  </tr>
  <tr>
    <td>2</td><td>320</td><td>something2</td>
  </tr>
  <tr>
    <td>2</td><td>220</td><td>something2</td>
  </tr>
  <tr>
    <td>2</td><td>420</td><td>something2</td>
  </tr>
  <tr>
    <td>3</td><td>500</td><td>something2</td>
  </tr>
  <tr>
    <td>3</td><td>550</td><td>something2</td>
  </tr>
  <tr>
    <td>4</td><td>390</td><td>something2</td>
  </tr>
  <tr>
    <td>4</td><td>380</td><td>something2</td>
  </tr>
  <tr>
    <td>4</td><td>370</td><td>something2</td>
  </tr>
</table>

Table 3
<table>
  <tr>
    <td>itemID</td><td>value</td>
  </tr>
  <tr>
    <td>400</td><td>1</td>
  </tr>
  <tr>
    <td>300</td><td>0</td>
  </tr>
  <tr>
    <td>320</td><td>1</td>
  </tr>
  <tr>
    <td>220</td><td>1</td>
  </tr>
  <tr>
    <td>420</td><td>1</td>
  </tr>
  <tr>
    <td>500</td><td>1</td>
  </tr>
  <tr>
    <td>550</td><td>0</td>
  </tr>
  <tr>
    <td>390</td><td>1</td>
  </tr>
  <tr>
    <td>380</td><td>1</td>
  </tr>
  <tr>
    <td>370</td><td>1</td>
  </tr>
</table>

TABLE 1 Result For me to now process and work with
<table>
  <tr>
    <td>packageID</td><td>something1</td><td>something2</td>
  </tr>
  <tr>
    <td>2</td><td>something1</td><td>something2</td>
  </tr>
  <tr>
    <td>4</td><td>something1</td><td>something2</td>
  </tr>
</table>

Any help appreciated, thanks.

I thought html code in posting meant it would come out as actual tables.

Edited August 1, 2022 by Champak

[spudw2k]

Not totally sure what the rest of your table structure or data looks like, but here is a quick and dirty I threw together.

SELECT Table1.package_name, Table3.item_value
FROM (Table1 INNER JOIN Table2 ON Table1.package_id = Table2.package_id) INNER JOIN Table3 ON Table2.item_id = Table3.item_id
WHERE (((Table3.item_value)>0));

 

[pixelsearch]

2 hours ago, Champak said:

I need to eliminate any "package" (row) from Table 1 [...] if ANY of the "items" within the "package" have a value of = 0 which is in Table 3.

If a package got only 1 item, it surely creates a single record in Table2, right ?

imho, the 1st step should be to work on Table 2 and Table 3 (joined by item_id) and Select from Table 2 all packages id's where at least one item_value = 0

After that, when you got that list of packages containing at least one item_value = 0, then it's easy to eliminate them from table 1.

Here is the old FoxPro style code, tested on your 3 tables (sorry I don't know newer databases) but maybe the logic described before could help you :

Select package_id From Table1 ;
Where package_id Not In ;
(Select Table2.package_id ;
 From Table2, Table3 ;
 Where Table3.item_id = Table2.item_id ;
   And Table3.item_value = 0 ;
 Group By Table2.package_id)

spudw2k syntax is certainly more appropriate to your needs, but his code seems to return packages with items values > 0 (even if these packages contain other items having a value = 0) when you asked not to include packages if any of their items got a value of 0
I may be totally wrong with the preceding sentence, my deepest apologies to spudw2k if I am

Edited August 1, 2022 by pixelsearch
typo

[jchd]

@Champaknext time a good idea will be to post the DLL in full, I mean all the relevant CREATE TABLE statements AND tell us which RDBMS engine you're using.

[Champak]

Thanks @pixelsearch and @spudw2k. I needed to make some adjustments but it was pretty much exactly what I needed, and I was able to expand it to a few other things that needed somewhat similar capabilities. For the particular issue I came here for @pixelsearch was right, but it turns out I needed @spudw2k version for something else, so all worked out.

@jchd no dll and I just thought this would be easier to understand, not typing out though...maybe not I guess. As far as RDBMS do you mean like mysql?

[jchd]

Typo! I meant DDL (data declaration language).

RDBMS yes like mssql, oracle, db2, sqlite, ...