Solved

Help required writing a complex query

Posted on 2013-05-16
16
211 Views
Last Modified: 2013-05-25
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
Comment
Question by:Sreejith22
  • 7
  • 5
  • 3
  • +1
16 Comments
 

Author Comment

by:Sreejith22
Comment Utility
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
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:Sreejith22
Comment Utility
@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
 

Author Comment

by:Sreejith22
Comment Utility
any updates. Is the question not clear enough?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:Sreejith22
Comment Utility
>>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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:Sreejith22
Comment Utility
ok, it was not intentional. I re-framed my question since I thought my original post was misleading
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Accepted Solution

by:
Sreejith22 earned 0 total points
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
if you were to re-read the first 2 responses on this question you would see the answer was given. Oh well.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Closing Comment

by:Sreejith22
Comment Utility
I got the solution for the same question from stackoverflow. Link added.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now