Re: [sqlite] Asking for SQL statement help

[Dennis Volodomanov]

> SELECT * FROM TABLEB WHERE ID IN (
>   SELECT IDB FROM TABLEA WHERE IDC = 1 AND IDD IN (1, 3)
> )

Yes, I think you are right - I'll do some extensive testing of course, but it 
looks good logically.

Thank you!

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asking for SQL statement help

[P Kishor]

On Tue, Sep 8, 2009 at 12:58 AM, Dennis Volodomanov wrote:
> Thank you for the quick replies and sorry for not being too clear.
>
> I will try to state the problem more clearly, without my own attempts to 
> solve it, as they are incorrect anyway.
>
> The simplified schemas again:
>
> CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD 
> INTEGER );
> CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );
>
> And some sample data:
>
> TABLEA
> 1|0|0|0
> 2|1|0|0
> 3|2|1|1
> 4|0|1|2
> 5|1|2|0
> 6|3|1|3
>
> TABLEB, TABLEC, TABLED
> 1|A
> 2|B
> 3|C
>
> The problem is that I need to grab rows from TABLEB where the ID of that row 
> appears in TABLEA, at the same time satisfying other conditions such as IDC 
> of that row also has multiple values (IDC=1 OR IDC=2, for example).
>
> So, given:
>
> IDC=1 AND (IDD=1 OR IDD=3)
>
> I need to get rows 2 and 3 from TABLEB.


SELECT * FROM TABLEB WHERE ID IN (
  SELECT IDB FROM TABLEA WHERE IDC = 1 AND IDD IN (1, 3)
)


>
> Hopefully this makes more sense :)
>
> Best regards,
>
>   Dennis
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asking for SQL statement help

[Dennis Volodomanov]

Thank you for the quick replies and sorry for not being too clear.

I will try to state the problem more clearly, without my own attempts to solve 
it, as they are incorrect anyway.

The simplified schemas again:

CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD 
INTEGER );
CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );

And some sample data:

TABLEA
1|0|0|0
2|1|0|0
3|2|1|1
4|0|1|2
5|1|2|0
6|3|1|3

TABLEB, TABLEC, TABLED
1|A
2|B
3|C

The problem is that I need to grab rows from TABLEB where the ID of that row 
appears in TABLEA, at the same time satisfying other conditions such as IDC of 
that row also has multiple values (IDC=1 OR IDC=2, for example).

So, given:

IDC=1 AND (IDD=1 OR IDD=3)

I need to get rows 2 and 3 from TABLEB.

Hopefully this makes more sense :)

Best regards,

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asking for SQL statement help

[Simon Slavin]

On 8 Sep 2009, at 6:31am, Dennis Volodomanov wrote:

> Let's say we've got 4 tables:
>
> CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC  
> INTEGER, IDD INTEGER );
> CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );


> What I'm trying to achieve is something like the following:
>
> SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT  
> JOIN TABLEB ON IDB=1 )
>
> But I also need to add additional constraints from TABLEC and TABLED  
> to narrow down the results. Basically, I only want results from  
> TABLEA with a given set of IDB, IDC and IDD (there could be multiple  
> of each of course).

You do not refer to your DATA columns in your SELECT command.  All you  
are doing is SELECTing on your IDs.  So can you explain why you can't  
use

SELECT * FROM TABLEB WHERE IDB = "xxx" AND IDC = "yyy" AND IDD = "zzz"

?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asking for SQL statement help

[P Kishor]

On Tue, Sep 8, 2009 at 12:31 AM, Dennis Volodomanov wrote:
> Hello all,
>
> A small SQL problem, no doubt, for experts here.
>
> Let's say we've got 4 tables:
>
> CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD 
> INTEGER );
> CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
> CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );
>
> What I'm trying to achieve is something like the following:
>
> SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT JOIN 
> TABLEB ON IDB=1 )
>

The above statement doesn't make any sense. In your sub-select, you
are JOINing TABLEA to TABLEB, but you are not specifying the columns
on which to JOIN. You have to do like so...

TABLEA LEFT JOIN TABLEB ON TABLEA.some_column = TABLEB.some_column

then you can specify a WHERE clause for IDB = 1, but then, your query
doesn't make any sense.

> But I also need to add additional constraints from TABLEC and TABLED to 
> narrow down the results. Basically, I only want results from TABLEA with a 
> given set of IDB, IDC and IDD (there could be multiple of each of course).
>

Now things get even more confusing. You want results from TABLEA, so
you have to have

SELECT * FROM TABLEA
WHERE IDB IN (...) AND IDC IN (...) AND IDD IN (...)

(replace AND in the above statement with OR to get a bigger set back)

> Can you please help me out - any hints are greatly appreciated!

It would help to restate the problem more clearly, perhaps with some
example data, so the relationship between TABLEA and the other tables
is more clear.


>
> Thank you in advance,
>
>   Dennis
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Asking for SQL statement help

[Darren Duncan]

Dennis Volodomanov wrote:
> SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT JOIN 
> TABLEB ON IDB=1 )

First of all, "IDB=1" isn't a join condition; it doesn't compare a column from 
TABLEA with a column from TABLEB.  Did you mean to say "WHERE" rather than 
"ON"? 
  In which case you also had a cartesian product between TABLEA and TABLEB.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Asking for SQL statement help

[Dennis Volodomanov]

Hello all,

A small SQL problem, no doubt, for experts here.

Let's say we've got 4 tables:

CREATE TABLE TABLEA ( ID INTEGER PRIMARY KEY, IDB INTEGER, IDC INTEGER, IDD 
INTEGER );
CREATE TABLE TABLEB ( ID INTEGER PRIMARY KEY, DATA );
CREATE TABLE TABLEC ( ID INTEGER PRIMARY KEY, DATA );
CREATE TABLE TABLED ( ID INTEGER PRIMARY KEY, DATA );

What I'm trying to achieve is something like the following:

SELECT * FROM TABLEB WHERE ID IN ( SELECT IDB AS ID FROM TABLEA LEFT JOIN 
TABLEB ON IDB=1 )

But I also need to add additional constraints from TABLEC and TABLED to narrow 
down the results. Basically, I only want results from TABLEA with a given set 
of IDB, IDC and IDD (there could be multiple of each of course).

Can you please help me out - any hints are greatly appreciated!

Thank you in advance,

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users