• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Help required writing a complex query

CREATE TABLE userneed_comparison_labels (
  id    integer PRIMARY KEY,
  name  char(50) NOT NULL
)

Open in new window




CREATE TABLE tbl_comparison_values (
  id                            integer PRIMARY KEY,
  tbl_content_common_id         integer NOT NULL,
  userneed_comparison_label_id  integer NOT NULL,
  value                         char(50) NOT NULL,
  
)

Open in new window




CREATE TABLE tbl_content_commons (
  id                               integer PRIMARY KEY,
  content_common_code              char(20) NOT NULL,
  content_common_name              char(100) NOT NULL,
  
)

Open in new window



I have three tables as shown above. From these tables, I am trying to generate a comparison table as shown in the attached image. For that, I need to write a query with the following requirements. Or experts can suggest queries after referring the attached screenshot and the tables defined above.

Conditions in my words:

1) Get all the name fields from userneed_comparison_labels using the userneed_comparison_label_id of tbl_comparison_values. At the same time, fetch the 'value' corresponding to the userneed_comparison_label_id

2) The input or the parameter would be tbl_content_common_id . This can vary from 2 to n number of id's at a time. Means user can compare any number of items at a time.

3) Preferrably, the result should be given in the order from most common userneed_comparison_label_id's for the given set of tbl_content_common_id's to the least common.

4) Preferrably, in the result, if a particular value is not available for a given tbl_content_common_id, then that value for that content should be denoted by "Not Available"

5) Finally, the name corresponding to tbl_content_common_id should be fetched from tbl_content_commons field content_common_name.


I prefer the result of the query is more close to the attached image of comparison table itself , if possible, so that things become much easier.

Any help in implementing this query would surely be well appreciated.

PS: I have attached the screenshot of the UI I am planning to implement once I have the above query. The sqlite db with three tables and some data is uploaded in this link > I am unable to attah .db file here!
comparison.bmp
0
Sreejith22
Asked:
Sreejith22
  • 7
  • 5
  • 3
  • +1
1 Solution
 
Sreejith22Author Commented:
it is indeed a simple query for an sql expert I presume. Is there any lack of clarity with the question I posted. Please  let me know.
0
 
SharathData EngineerCommented:
Are you looking for something like this?
select t1.name,coalesce(t3.content_common_name,'Not Available')  content_common_name
  from userneed_comparison_labels t1
 inner join tbl_comparison_values t2
   on t1.id  = t2.userneed_comparison_label_id
 left join tbl_content_commons t3
   on t3.id = t2.tbl_content_common_id

Open in new window

If not, post some sample data from 3 tables.
0
 
PortletPaulCommented:
think you might need  left joins

i.e. change line 3 above 'inner' to 'left'

I'm assuming any one of those values could be null at some point (i.e. this is a guess)
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Sreejith22Author Commented:
@Sharath_123

Ignore my original post and read below:

I have uploaded the db with these three tables here.

https://docs.google.com/file/d/0BwhgDtGvE2HgNFRxM3JPNWdyUm8/edit?usp=sharing


I am reframing my question. Hope you might have seen comparison tables in some electronic sites, tv sites etc. where you can select 2 or 3 items and press compare button. It will provide you comparison of certain features for the selected items.

Keeping this in mind, please refer the image attached in my original post. You can see a comparison table structure there. Since, this screen is from a mobile device, it is shown as 3 different screens. Actually, it is a scrollable table in a mobile.

Is it possible to have a query which will yield me a table like that?

The values corresponding to 'Item' column is obtainable from userneed_comparison_labels.

Against each of these labels, the values corresponding to each content need to be fetched. If the value for a particular content is not available, it should be shown as "Not avialable"

The only input I will provide is 2 or 3 id's for the contents ( tbl_content_common_id) which are the ID's for the contents that need to be compared.
0
 
Sreejith22Author Commented:
any updates. Is the question not clear enough?
0
 
Anthony PerkinsCommented:
any updates. Is the question not clear enough?
Patience my friend, we are all volunteers here and have our work and life and they don't necessarily revolve around you.  Besides not everyone is in the same time zone as you.
0
 
PortletPaulCommented:
I am unable to use that upload I'm afraid (long story) so new information is trapped in a format I could not access

and might be interpreted that you were inviting a named individual, and that may deter involvement too. (yes, it probably depends on how you read that reference).
0
 
Sreejith22Author Commented:
>>and might be interpreted that you were inviting a named individual, and that may deter involvement too. (yes, it probably depends on how you read that reference).

means?
0
 
PortletPaulCommented:
that you specifically were targeting a particular person to reply...
please re-read ID: 39173806

it might not have been intended, but it's a possible interpretation.
0
 
Sreejith22Author Commented:
ok, it was not intentional. I re-framed my question since I thought my original post was misleading
0
 
PortletPaulCommented:
understood, sorry I cannot use the file so I am unable to get the to the additional facts it may reveal. someone else will I trust.
0
 
Sreejith22Author Commented:
Thank you all for the participation

@ Sharath_123, @PortletPaul - thankyou for the active responses, and I appreciate the help.


@acperkins - thanks for the advice!

Finally, I got the solution in minutes, after I posted this to stackoverflow

http://stackoverflow.com/questions/16615534/joining-three-tables-in-sqlite

I really appreciate and respect SO for being a no-profitable site and treating the ones who need technical solutions with due respect.
0
 
PortletPaulCommented:
if you were to re-read the first 2 responses on this question you would see the answer was given. Oh well.
0
 
Anthony PerkinsCommented:
I really appreciate and respect SO for being a no-profitable site and treating the ones who need technical solutions with due respect.
Absolutely.  I am glad you understand now how this site works.
0
 
Anthony PerkinsCommented:
PortletPaul,

If you read through the author's stats you will be less surprised:
Questions asked:193 (of which 33 are Community Support for example this one from nearly 5 years ago How to inform moderators?)
Open: 6 (including one from last August).
Deleted: 32
PAQ:Forced: 5
0
 
Sreejith22Author Commented:
I got the solution for the same question from stackoverflow. Link added.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now