Solved

Help required writing a complex query

Posted on 2013-05-16
16
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
16 Comments
 

Author Comment

by:Sreejith22
ID: 39172599
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 41

Expert Comment

by:Sharath
ID: 39173349
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
ID: 39173719
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Sreejith22
ID: 39173806
@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
ID: 39174418
any updates. Is the question not clear enough?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39174501
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
ID: 39174555
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
ID: 39174560
>>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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39174574
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
ID: 39174597
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
ID: 39174626
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
ID: 39180491
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
ID: 39180526
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
ID: 39183001
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
ID: 39183024
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
ID: 39196258
I got the solution for the same question from stackoverflow. Link added.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This video teaches viewers about errors in exception handling.

726 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