• 0

[MSSQL] Avoiding joins


Question

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 by thevink
Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

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

  • 0

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

  • 0

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

  • 0

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

  • 0

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

  • 0

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

  • 0
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

  • 0

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

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.