Link to home
Create AccountLog in
Avatar of Sreejith22
Sreejith22Flag for India

asked on

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!
Avatar of Sreejith22
Flag of India image


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.
Avatar of Sharath S
Are you looking for something like this?
select,coalesce(t3.content_common_name,'Not Available')  content_common_name
  from userneed_comparison_labels t1
 inner join tbl_comparison_values t2
   on  = t2.userneed_comparison_label_id
 left join tbl_content_commons t3
   on = t2.tbl_content_common_id

Open in new window

If not, post some sample data from 3 tables.
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)

Ignore my original post and read below:

I have uploaded the db with these three tables here.

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.
any updates. Is the question not clear enough?
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.
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).
>>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).

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.
ok, it was not intentional. I re-framed my question since I thought my original post was misleading
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.
Avatar of Sreejith22
Flag of India image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
if you were to re-read the first 2 responses on this question you would see the answer was given. Oh well.
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.

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
I got the solution for the same question from stackoverflow. Link added.