Jump to content

jgarcia

Members
  • Posts

    8
  • Joined

  • Last visited

jgarcia's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. 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
  2. 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
  3. 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!
  4. 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
  5. 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
  6. 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?
  7. 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
×
×
  • Create New...