[sqlite] Asking for SQL statement help
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
Re: [sqlite] Asking for SQL statement help
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
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 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
Re: [sqlite] Asking for SQL statement help
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
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
> 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