Jump to content

Complicated database query assistance


Recommended Posts

...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:

 

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 by Champak
Link to post
Share on other sites

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));

 

Link to post
Share on other sites
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 by pixelsearch
typo
Link to post
Share on other sites

@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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

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?

Link to post
Share on other sites

Typo! I meant DDL (data declaration language).

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

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...