By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,326 Members | 2,873 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,326 IT Pros & Developers. It's quick & easy.

sqlite conditional select from multiple tables

100+
P: 131
I've got 5 tables that are related; there are other fields in each table, but these are trouble makers.

Expand|Select|Wrap|Line Numbers
  1.    tbl_1
  2. vol_key TEXT(),
  3. fil_id INTEGER
  4. type INTEGER
  5.  
  6.    tbl_2
  7. vol_key TEXT(),
  8. fil_key INTEGER
  9.  
  10.    tbl_3 (type is 1)
  11. vol_key TEXT(),
  12. fil_key INTEGER
  13.  
  14.    tbl_4 (type is 2)
  15. vol_key TEXT(),
  16. fil_key INTEGER
  17.  
  18.    tbl_5 (type is 3)
  19. vol_key TEXT(),
  20. fil_key INTEGER
  21.  
right now, I've got the following

Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_1.*, tbl_2.* FROM tbl_1,tbl_2
  2. WHERE
  3. (tbl_1.vol_key='some_string'
  4. AND
  5. (tbl_2.fil_key=tbl_1.fil_id
  6. AND
  7. tbl_2.vol_key='some string'))
The above seems to work to get results from tbl_1 and tbl_2, but I also need to add the data from tbl_3 through tbl_5 when tbl_1.type is a 1, 2, or 3. I tried the CASE WHEN, but got an error near "CASE" message.

My question is how would I code the conditional CASEs to pull in tbl_3 - tbl_5 if they're required while not breaking the query in tbl_1 and tbl_2 if the data from the latter tables aren't needed?

Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_1.*, tbl_2.* FROM tbl_1, tbl_2,
  2. CASE WHEN tbl_1.type=1 THEN tbl_3.* END AS t_3,
  3. CASE WHEN tbl_1.type=2 THEN tbl_4.* END AS t_4,
  4. CASE WHEN tbl_1.type=3 THEN tbl_5.* END AS t_5
  5. FROM tbl_1, tbl_2 WHERE (...)
  6.  
I started with this, but it ended before it bagan with the error message near "CASE". I'm lost with this overly complicated exotic stuff. :D

Any ideas would be greatly appricated. TIA
Mar 14 '14 #1

✓ answered by Rabbit

Are the fields in the 3, 4, and 5 not the same type of data? If they aren't, then you should normalize your data and only have one table with the type as a field.
http://bytes.com/topic/access/insigh...ble-structures

If they are, then you need to outer join those tables.

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,359
Are the fields in the 3, 4, and 5 not the same type of data? If they aren't, then you should normalize your data and only have one table with the type as a field.
http://bytes.com/topic/access/insigh...ble-structures

If they are, then you need to outer join those tables.
Mar 14 '14 #2

100+
P: 131
Thank you for the reply and the link.

I've saved the link and will mill over it to see which table method is best suited for what I'm trying to do.

The tables do share common data types such as the "keys" but also have different fields for details specific to graphics, audio, or video.
Mar 15 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.