Jump to content

Need help with a query, INNER JOIN I think.


jgarcia

Recommended Posts

I am looking for help in contructing a query which I believe will need to use an INNER JOIN. I am not greatly experienced with SQL and would appreciate suggestions.I have a table with data stored as follows:User1 Response1 Data1User1 Response2 Data2User1 Response3 Data3User2 Response1 Data4User2 Response2 Data5User2 Response3 Data6...I want to display the Response field like this:User1 Data1 Data2 Data3User2 Data4 Data5 Data6...Any suggestions? I'm happy to provide any additional information I can that might help. Thanks for taking a look.Joel

Link to comment
Share on other sites

Oh, I hope it isn't impossible! But I thought it might be a bit unusual. I've had the following idea, though I don't know how to go about it.If a new table was created and each field was populated with the results of a separate, much simpler queries, then I could basically reformat the table. The individual queries would look like:select [Data] where [Response] = Response1 order by [userID]or something like that, where the resulting records would contain all data for a given UserID.Would this work and how would I go about it? The existing table must remain as it is part of a module in use. If there is some way to create a temporary table on the fly or even create/update a new table to contain the data in a more easily queried format, that would be fine.Not impossible? :)

Link to comment
Share on other sites

Well, if you are willing to create temporary tables and such then there may be a way (so maybe not impossible :) ). What information do you know about the table? Is it always three responses for each user, or will that vary (is there a max number of responses/data units)? and how large of a table are we talking about....100 row table, or 100,000 row table? Also, are the response fields predictable values, such as Response1, response2, etc. or are they different for each record? Finally, is this something you will need to reuse over and over?To give you some idea how the answers might affect things....you can do a join on a single table, and if for instance you knew you only had three responses to deal with, you could write a query (an ugly one mind you....but hey if it works :( )that would join the table to itself three times, and could populate the fields correctly. If the number of possible responses is unknown, then that idea goes out the door.If the number of rows in the table is low....I would personally suggest just try to manually fix the problem.Oh, forgot to ask, what kind of SQL are you running...MS SQL, Access, Oracle, MySQL, or something else?I still do not know exactly how this can be done, but since you are being flexible and not trying to do all of this in one query, I can try to be flexible too and think on it for a while and see what I can come up with. :)

Link to comment
Share on other sites

What information do you know about the table? Is it always three responses for each user, or will that vary (is there a max number of responses/data units)? and how large of a table are we talking about....100 row table, or 100,000 row table? Also, are the response fields predictable values, such as Response1, response2, etc. or are they different for each record? Finally, is this something you will need to reuse over and over?There are currently 11 responses for each user but only 8 are required fields. I could make all of them required to aid in the predictability of the table. I am anticipating around the 100 user range, so that gives a table of approximately 1000 rows.The responses can be placed into 11 columns based on the QuestionID being asked. QuestionID is a field of the existing table that could be used to help sort responses into like columns.This query will be executed each time a webpage loads, to refresh the data in case new entries have been made since the last visit, so, yes, it will be used often.To give you some idea how the answers might affect things....you can do a join on a single table, and if for instance you knew you only had three responses to deal with, you could write a query (an ugly one mind you....but hey if it works :) )that would join the table to itself three times, and could populate the fields correctly. If the number of possible responses is unknown, then that idea goes out the door.Does this get too ugly when we talk of 11 responses? I thought of something like this, but didn't know how to write the query.If the number of rows in the table is low....I would personally suggest just try to manually fix the problem.Can't do this, because then I would have to rewrite the DNN module that is generating and reading from the existing table. Ugh!Oh, forgot to ask, what kind of SQL are you running...MS SQL, Access, Oracle, MySQL, or something else?MS SQL 2000I still do not know exactly how this can be done, but since you are being flexible and not trying to do all of this in one query, I can try to be flexible too and think on it for a while and see what I can come up with.I really appreciate your input. Thanks a bunch and I hope I'm explaining things accurately. SQL queries are not my expertise.Joel

Link to comment
Share on other sites

Alright, let me start off by saying, I am not doing this for you (too much work, and I don't have the time...sorry :D ), but I can still hopefully guide you along the way (by the way, I am not a SQL expert either so what I tell you may not be the most efficient way, but hopefully it will work for your situation).By the way, you keep referring to this in the future tense as if this table is not currently being used, if so that will simplify things some since you won't have to worry about moving existing data, rather just making sure that all isnerted data ends up in the right location.To continue on though, with all that you are wanting to do, I think you will find it easiest to go ahead and dive into SQL a little further. I suggest learning Transact-SQL, it is not complicated (at least not the basic stuff) and it will add a lot of power to your queries. So for instance, you could include conditional statements, loops, variables, and case statements to perform what you are wanting. At that point, I suggest you think on your own situation and decide the algorithm that will work best for you.Whatever you decide to do with the SQL code, I suggest you wrap it up into a stored procedure, so that it will be easier for you to call again in the future and reuse.Finally, if you are going to be forced to insert into the table as is, and then forced to insert into the table you are creating, I would suggest using triggers to call a stored procedure, that would contain most of the code you generated for the first task. In case you don't know, a trigger can be set to execute everytime either an insert, update, or delete occurs on a given table. The trigger can execute most anything you can write in SQL, including basic select/insert/update/delete statements, or a stored procedure, that may contain multiple statements.I know this may not be much help to you, but as I look over the problem, I see it as something that you will need to learn and do yourself and as you get specific questions on each step you can post those in here and we can help you where we can. Please know that this is POSSIBLE :( but may be difficult. I am sure there is probably some SQL guru out there who could present an elegant solution, but what I am suggesting is more of a brute force method...may not be pretty but it will get the job done :). Please don't hesitate to ask more questions as you continue on and I hope all of this helps a little at least. **Wow I am long winded today :) **

Link to comment
Share on other sites

Okay, let's take this down a step. Here is a smaller more general question which may be part of the larger solution.Is there a way to create a select statement that will output the contents of two records of the same table in one row? The data from each record to be listed together come from the same column.Example:From a table such asUser1 Data1User2 Data2User3 Data3User1 Data4User2 Data5User3 Data6Can I use a select to outputUser1 Data1 Data4User2 Data2 Data5User2 Data3 Data6Or is that another impossibility? If this is possible, then I am halfway to a solution. I think my struggle right now is in not knowing what sql is capable of doing. I know that I could write an app that could dissect the table and give me what I want, but I am hoping to keep this simple if possible. I am working within some limitations.At this point, I am considering that writing an asp script to run multiple queries and process the data manually might be easier for me than to try and figure out how to do it with a more complex query.Any of this make sense? Again, I appreciate any input on my ideas. I know I can accomplish this somehow, but finding the most efficient path is challenging.Joel

Link to comment
Share on other sites

Okay, let's take this down a step.  Here is a smaller more general question which may be part of the larger solution.

Sorry if I scared you off a bit...didn't mean to do that necessarily, but since you broke it down to a smaller chunk I can help with that...in the case that there are just 2 pieces of data such as this, this query would work:
SELECT a.user, a.data, b.dataFROM table1 aJOIN table1 b on a.user = b.userWHERE a.data <> b.data /* <> means not equal */

That will take care of 2 pieces of data, but it gets tricker when you add in more.

Link to comment
Share on other sites

That worked great! But I did modify it with two conditionals so that it only returned one a.data and one b.data otherwise it was returning a row for every possible combination of a.data and b.data where a.user=b.user So it looks like this now:

SELECT a.user, a.data, b.dataFROM table1 aJOIN table1 b on a.user = b.user and a.ResponseID='field1'WHERE a.data <> b.data and b.ResponseID='field2'

Now it returns rows that contain each a.data that is a field1 value partnered with the matching b.data that is the field2 value of the same user. Beautiful! So, what is the secret of adding a third or more joins to this? This is going to work! :)

Link to comment
Share on other sites

That was easier that we thought! I figured out how to do the multiple joins and it works like a charm! :) Here's an example with 4 fields:

SELECT a.data AS field1, b.data AS field2, c.data AS field3, d.data AS field4FROM table1 aJOIN table1 b ON a.user = b.user AND a.ResponseID='field1'JOIN table1 c ON a.user = c.user AND a.ResponseID='field1'JOIN table1 d ON a.user  = d.user AND a.ResponseID='field1'WHERE a.data <> b.data AND a.data <> c.data AND a.data <> d.data AND b.data <> c.data AND b.data <> d.data AND c.data <> d.data AND b.ResponseID='field2' AND c.ResponseID='field3' AND d.ResponseID='field4'ORDER BY b.data DESC

Thanks very much for your help in looking at this Kcarson. Your suggestions were key in creating this solution. Can I buy you a beer?Best Regards,Joel

Link to comment
Share on other sites

That was easier that we thought!  I figured out how to do the multiple joins and it works like a charm!  :) Thanks very much for your help in looking at this Kcarson. Your suggestions were key in creating this solution.  Can I buy you a beer?Best Regards,Joel

lol....beer is not necessary, but I am very glad I was able to help. That was easier than I had anticipated, I mostly write ad hoc queries, so they tend to stay small and less complex. Congrats to you on figuring out how to do it as well, since you did do most of the work :)
Link to comment
Share on other sites

By the way Joel, what happened to the other 7 possible repsonses?....that is one of the pieces that kept making me think it was going to be very hard....the fact that there were 11 possible responses.Well, I was just curious.

Link to comment
Share on other sites

By the way Joel, what happened to the other 7 possible repsonses?....that is one of the pieces that kept making me think it was going to be very hard....the fact that there were 11 possible responses.
You can continue expanding the query for more than 4 fields by adding a new JOIN for each one and then adding the extra conditions to the WHERE statement. The JOIN statements are pretty simple since they all take the same structure with only a minor change to note which instance of table1 is being referenced.
JOIN table1 n ON a.user = n.user AND a.ResponseID='field1'/* with a new n for each field you want to extract from the table. */

The complicated part is the WHERE statement which grows quickly with more fields. Each n.data must be not equal to every other n.data and each n.ResponseID must be equal to the desired field for that JOIN. So with each additional field you get several more conditionals:2 fields need 2 conditionals: a.data<>b.data and b.ResponseID='field2'3 fields needs 5: a<>b,a<>c,b<>c,b.ID='?',c.ID='?'4 fields needs 9: a<>b,a<>c,a<>d,b<>c,b<>d,c<>d,b.ID='?',c.ID='?',d.ID='?'5 fields needs 146 fields needs 20...11 fields needs 65 conditionals in the WHERE statement (I think that's right).So, you're right that it get's pretty unwieldly with too many different fields to work with and I have made a functional compromise. I'm going to stick with 5 and then allow the user to select a row to view in detail. Then I can use a much simpler query to pull that one set of responses by an individual userID and display them on a secondary page. So, I can go beyond 11 response fields now and display a subset in the initial table displayed. It will still work great for my purpose.Do I sound like a query expert yet? lol! Thanks again for the assist.Joel

Link to comment
Share on other sites

Do I sound like a query expert yet? lol!  Thanks again for the assist.Joel

Great explanation :) Yeah, that 65 thing is what made me thing all the other steps would have to be taken, but I am very glad you found a compromise for your situation so it did not have to turn into that....wouldn't it stink if you had to make a change to that query later :)Best of luck with everything else, and now that you are an expert, you can begin helping everybody else with their questions :(
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...