thevink Posted June 15, 2009 Share Posted June 15, 2009 (edited) Hey, I have this table, example Code Value Amount Client DDD 1 1 2 DDD 2 2 22 EEE 1 2 2 EEE 2 1 22 etc... My codes are between 5-20, and we would like to construct an SQL that puts the codes in columns instead of rows ie. Client DDD_Value DDD_amount EEE_Value EEE_Amount ..... 2 1 1 1 2 22 2 2 2 1 etc. Now this can easily be done by JOINs, however with the amount of joins it might need the sql takes much longer(large database). Any other options, or am I stuck using joins? Edited June 15, 2009 by thevink Link to comment Share on other sites More sharing options...
0 IceBreakerG Posted June 15, 2009 Share Posted June 15, 2009 Looking at that, for simplicity sake, using a join would be much simpler. Since it's just a return query (i think it's just a return query), using a join wouldn't take too long to perform. In my last project I had a query that had like 6 joins, and it ran about as fast as just returning 1 thing without a join. SQL is very good at that. Link to comment Share on other sites More sharing options...
0 Knad Posted June 15, 2009 Share Posted June 15, 2009 I agree with IceBreaker, MySQL is very good at handling joins. So unless you have a reason other than speed, then I would use them :) Link to comment Share on other sites More sharing options...
0 thevink Posted June 15, 2009 Author Share Posted June 15, 2009 Well, as I have found out, the entries have termination codes within them. I want to pull out all entries that are not terminated (value of 1) but if I put this restriction in the join itself the SQL doesnt complete in any reasonable time, and if I put these restrictions in the WHERE statement I get an incomplete listing. Link to comment Share on other sites More sharing options...
0 IceBreakerG Posted June 15, 2009 Share Posted June 15, 2009 Can you post your SQL statement for this query along with the table structure of the tables you're using? Link to comment Share on other sites More sharing options...
0 thevink Posted June 15, 2009 Author Share Posted June 15, 2009 No I can't, but I can make something similar. 2 tables: person table and code table (person table not important) Code Table: PID CODE VALUE AMOUNT ACTIVE ...(more columns, but these are the important ones) 1 AAA 1 1 0 1 BBB 1 1 1 1 AAA 1 1 1 2 BBB 2 2 1 etc... So the SQL with the joins would be something like this SELECT p.fname, p.lname, a.Value as AAA_Value, a.Amount as AAA_Amount, b.Value as BBB_Value, b.Amount as BBB_Amount, ...etc FROM person p LEFT OUTER JOIN code a ON p.ID = a.PID and a.Active = 1 LEFT OUTER JOIN code b ON p.ID = b.PID and b.Active = 1 etc... The SQL is of course a lot more complex than this. As well, I cannot change the structure of tables, do stored procedure, etc to help out, it has to be strictly an SQL statement. (My idea was to just do a data dump of the data and then parse the results with a macro in excel or anything but that was turned down) Link to comment Share on other sites More sharing options...
0 IceBreakerG Posted June 16, 2009 Share Posted June 16, 2009 Hmmm, looking at that query, that's actually about how I would do it to be honest. How many rows do you think are in that table? If it's substantial (like over 100,000), then the query is probably going to be a little slow anyway since it has to go through so much data. The fact that you can't use a stored procedure though seems a little strange. In fact, the way you did that query is exactly how I did the one I was talking about earlier. I'm not an expert in SQL though, so someone with more experience may be able to provide a more optimized way of doing this. Link to comment Share on other sites More sharing options...
0 thevink Posted June 16, 2009 Author Share Posted June 16, 2009 Hmmm, looking at that query, that's actually about how I would do it to be honest. How many rows do you think are in that table? If it's substantial (like over 100,000), then the query is probably going to be a little slow anyway since it has to go through so much data. The fact that you can't use a stored procedure though seems a little strange. In fact, the way you did that query is exactly how I did the one I was talking about earlier. I'm not an expert in SQL though, so someone with more experience may be able to provide a more optimized way of doing this. Yeah, the number is well over 12 mil, and the reason that I can't use a stored procedure is because my boss does not want to go through IT (they take 3 weeks just to do something as simple as this) Link to comment Share on other sites More sharing options...
0 James Rose Posted June 19, 2009 Share Posted June 19, 2009 Another thought about speed could be the need for one or more indexes on the tables. But IceBreaker is right, SQL (Oracle, MySQL, etc) are fastest with joins as opposed to multiple queries for each item. I have worked for Wells Fargo and another company that had tables with millions of rows. The indexes make all the difference. (Well, that and good hardware) Best of luck. Link to comment Share on other sites More sharing options...
Question
thevink
Hey,
I have this table, example
My codes are between 5-20, and we would like to construct an SQL that puts the codes in columns instead of rows
ie.
Now this can easily be done by JOINs, however with the amount of joins it might need the sql takes much longer(large database).
Any other options, or am I stuck using joins?
Edited by thevinkLink to comment
Share on other sites
8 answers to this question
Recommended Posts