Inner Join or something else ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner Join or something else ?

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-07-27 : 16:57:40

Not quite sure how to describe this, but surely its here-
Consider a table like this:
id name qty
1 bolt 60
1 nut 20
2 bolt 10
2 nut 23

To get

id item1 qty1 item2 qty2
1 bolt 60 nut 20
2 bolt 10 nut 23

looks like it might be an innerjoin

I have a more complicated query I am trying to redesign into smaller parts It no longer returns data because I think the data is very very large and the inner joins are bringing it down

Thanks !

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 17:21:04

[code]
SELECT id,
MAX(CASE WHEN Seq=1 THEN name END) AS item1,
MAX(CASE WHEN Seq=1 THEN qty END) AS qty1,
MAX(CASE WHEN Seq=2 THEN name END) AS item2,
MAX(CASE WHEN Seq=2 THEN qty END) AS qty2
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS Seq,*
FROM table
)t
GROUP BY id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-07-27 : 19:16:27

Wow - this is something new.
I mapped this to my data - the concept may have application, but following that pattern did not get the data needed. Let me explain further. The X values and Y values for me to do plotting are loaded vertically. Many of X values have only 200 or 300 'values' for that set and will have 1 of more sets of corresponding Y values. What separates them are 2 sets of foreign keys. The table is a whopping 12 GB of data that grows daily. One of the keys will only resolve itself by another table inner join but at this point I think I need to understand how to get the data for any Y aligned horizontally instead of vertically beneath X. I can send you an image of structure/data. But you may already know what I should be doing - I can easiy do the query and get the value for X on the set of values for my Y, but if I use for_key = someNumber and add OR for_key = someOtherNumber the data is stacked on top with twice as many rows ...Thanks

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 20:21:16

sorry i didnt get your explanation. please explain with some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-07-27 : 23:31:49

Here is the data for 1 unit - I would usually get multiple c_fkeys. But lets just consider one c_fkey
This is for XY_FKeys 5477 and 5480.

( this is vertically stacked )

c_fkey RecordNum XY_FKeys XYValues
46127 1 5477 -0.01349088
46127 1 5480 -0.01203436
46127 2 5480 0.08835517
46127 2 5477 16.48135
46127 3 5477 20.87038
46127 3 5480 0.1887588
46127 4 5480 0.2891624
46127 4 5477 21.75628
46127 5 5477 23.01993
46127 5 5480 0.389566
46127 6 5480 0.4899696

etc

The data as needed..

c_fkey RecNum X_FKey XValue Y_FKey YValue
46127 1 5477 -0.01349088 5480 -0.01203436
46127 2 5477 16.48135 5480 0.08835517
46127 3 5477 20.87038 5480 0.1887588
etc - here there are 1/2 as many rows because the 5477 belong to the Xvalues. In this particular case there are 236 XY pairs of data

With this format I can send the data into a plot program.

What kind of questions do you have to visualize my problem ?

Thanks

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-28 : 12:35:23

isnt this the same i gave suggestion for?


SELECT c_fkey,
       RecNum,	
MAX(CASE WHEN Seq=1 THEN XY_FKeys END) AS X_FKey,
MAX(CASE WHEN Seq=1 THEN XYValues END) AS XValue,
MAX(CASE WHEN Seq=2 THEN XY_FKeys END) AS Y_FKey,
MAX(CASE WHEN Seq=2 THEN XYValues END) AS YValue
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c_fkey,RecNum ORDER BY XY_FKeys) AS Seq,*
FROM table
)t
GROUP BY c_fkey,
       RecNum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-07-28 : 15:16:04

I made a mistake when I transposed the original pattern. The results I get are the correct number of rows, but the values and keys are NULL. I checked the data for this particular c_fkey. Must be something I am did. Thanks for checking back with me. ( Would be good to get recordNum sorted within C_fkey )

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-28 : 15:45:37

sorry still i'm not getting your explanation. I dont think sample data you posted is clear representation of your scenario. Unless you do that I dont think anybody can provide effective help. If you can post actual scenario i'll definitely try..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-29 : 00:05:25

please use suggestion exactly like what i gave you.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-07-29 : 13:07:05

Thank you very much !! ... but please read on

I did a query into a new table with just the data I wanted reorganized. ( C_FKey ='YF13027215' and (XY_FKeys=5477 or XY_FKeys=5480) And the query worked.

I now understand the meaning of Seq for

MAX(CASE WHEN Seq=1 THEN XY_FKeys END) AS X_FKey,
MAX(CASE WHEN Seq=1 THEN XYValues END) AS XValue,
MAX(CASE WHEN Seq=2 THEN XY_FKeys END) AS Y_FKey,
MAX(CASE WHEN Seq=2 THEN XYValues END) AS YValue

Seq = the column group...

But how do I further constrain both c_fkey or XY_Fkeys ?
In my original table I have multiple c_fkeys and XY_Fkeys.

like this:
SELECT c_fkey,
RecNum,
MAX(CASE WHEN Seq=1 THEN XY_FKeys END) AS X_FKey,
MAX(CASE WHEN Seq=1 THEN XYValues END) AS XValue,
MAX(CASE WHEN Seq=2 THEN XY_FKeys END) AS Y_FKey,
MAX(CASE WHEN Seq=2 THEN XYValues END) AS YValue
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c_fkey,RecNum ORDER BY XY_FKeys) AS Seq,*
FROM APartSame2 where c_fkey='yF1302715'and XYValues =5477 or XYValues=5480)t
GROUP BY c_fkey,
RecNum

using where I get 0 rows instead of 300.

Is that a limitation or should I downsize the data into a temp table first and then reorganize the data using the method you show ?

Thanks again !

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-29 : 15:01:35

shouldnt it be?


SELECT c_fkey,
RecNum,	
MAX(CASE WHEN Seq=1 THEN XY_FKeys END) AS X_FKey,
MAX(CASE WHEN Seq=1 THEN XYValues END) AS XValue,
MAX(CASE WHEN Seq=2 THEN XY_FKeys END) AS Y_FKey,
MAX(CASE WHEN Seq=2 THEN XYValues END) AS YValue
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c_fkey,RecNum ORDER BY XY_FKeys) AS Seq,*
FROM APartSame2 where c_fkey='yF1302715'and XYValues IN (5477,5480))t 
GROUP BY c_fkey,
RecNum

also i see only numerical values in posted data for c_fkey and you're passing it as alphanumeric value

is it correct?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-07-29 : 20:43:59

Hi Again !

The query example worked fine you gave me. I can get the whole table downselected ( or filtered )like this:

SELECT CSB_FKey,recordNum,
MAX(CASE WHEN Seq=1 THEN Stream_FKey END) AS X_Fkey,
MAX(CASE WHEN Seq=1 THEN Value END) AS Xvalue,
MAX(CASE WHEN Seq=2 THEN Stream_FKey END) AS Y_Fkey,
MAX(CASE WHEN Seq=2 THEN Value END) AS Yvalue
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY CSB_FKey, recordNum ORDER BY stream_fkey) AS Seq,*
FROM ( Select * from theHugeTable where CSB_FKey ='theKey' and ( Stream_FKey = 5477 or Stream_FKey = 5480)
) dtable)t
GROUP BY CSb_fkey, RecordNum

I still have a number of other challenges visakh16, but this was a great improvement. Thanks !!!!

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-29 : 20:47:12

quote:
Originally posted by andrewcw

Hi Again !

The query example worked fine you gave me. I can get the whole table downselected ( or filtered )like this:

SELECT CSB_FKey,recordNum,
MAX(CASE WHEN Seq=1 THEN Stream_FKey END) AS X_Fkey,
MAX(CASE WHEN Seq=1 THEN Value END) AS Xvalue,
MAX(CASE WHEN Seq=2 THEN Stream_FKey END) AS Y_Fkey,
MAX(CASE WHEN Seq=2 THEN Value END) AS Yvalue
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY CSB_FKey, recordNum ORDER BY stream_fkey) AS Seq,*
FROM ( Select * from theHugeTable where CSB_FKey ='theKey' and ( Stream_FKey = 5477 or Stream_FKey = 5480)
) dtable)t
GROUP BY CSb_fkey, RecordNum

I still have a number of other challenges visakh16, but this was a great improvement. Thanks !!!!

andrewcw

what are the other challenges?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-08-02 : 17:54:17

The query worked fine until...We have a field called attempt ((the actual data is a series of data used for potting on test runs). . The keys were aligned correctly as long as there was just 1 attempt. Now I have an example with 4 attempts and the keys and rows of data are not correct.Instead of 713 rows I ended up with 385 rows.

Here's the idealized format
Output for the plot routine should be similar to this:
C_FKEY or LN or both,ATTEMPT,RECNUM,X_KEY,X_value, Y_Key,Y_value
Heirachy/ grouping would have top layer, C_FKEY or LN ,attempt, then recordNum
C_KEY1, 1, 1, 5130, 0.3, 5131, 0.04
C_KEY1, 1, 2, 5130, 0.4, 5131, 0.04
C_KEY1, 1, 3, 5130, 0.5, 5131, 0.14
C_KEY1, 1, 4, 5130, 0.6, 5131, 0.24
C_KEY1, 2, 1, 5130, 0.3, 5131, 0.34
C_KEY1, 2, 2, 5130, 0.3, 5131, 0.4
C_KEY1, 2, 3, 5130, 0.3, 5131, 0.4
C_KEY2, 1, 1, 5130, 0.3, 5131, 0.4
C_KEY2, 1, 2, 5130, 0.3, 5131, 0.4
C_KEY2, 1, 3, 5130, 0.3, 5131, 0.4

Here's a sample of the data - first 2 columns are a truncation of value called CSB_FKEY and 2nd column is another higher level grouping. The XYKeys are the 3rd column, attempt is the 4th
and recnum is 5th with XY_values in the last (rounded for this purpose )- oh yes and I limited each set to only the first 4 recordnums. There were actually varing sample sizes between attempts 1-4.( 174,...185).73327116 4155 5259 1 1 -0.038
73327116 4155 5259 1 2 -0.038
73327116 4155 5259 1 3 -0.05
73327116 4155 5259 1 4 -0.11
73327116 4155 5259 2 1 -0.016
73327116 4155 5259 2 2 0.031
73327116 4155 5259 2 3 0.044
73327116 4155 5259 2 4 0.034
73327116 4155 5259 3 1 0.059
73327116 4155 5259 3 2 0.056
73327116 4155 5259 3 3 0.025
73327116 4155 5259 3 4 0
73327116 4155 5259 4 1 0.025
73327116 4155 5259 4 2 0.044
73327116 4155 5259 4 3 0.047
73327116 4155 5259 4 4 0.044
73327116 4155 5260 1 1 0.011
73327116 4155 5260 1 2 0.494
73327116 4155 5260 1 3 1.005
73327116 4155 5260 1 4 1.516
73327116 4155 5260 2 1 -0.088
73327116 4155 5260 2 2 -0.643
73327116 4155 5260 2 3 -0.192
73327116 4155 5260 2 4 0.302
73327116 4155 5260 3 1 -0.687
73327116 4155 5260 3 2 -0.159
73327116 4155 5260 3 3 0.341
73327116 4155 5260 3 4 0.818
73327116 4155 5260 4 1 -0.341
73327116 4155 5260 4 2 -0.912
73327116 4155 5260 4 3 -0.478
73327116 4155 5260 4 4 -0.005

Thanks

andrewcw

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-08-06 : 15:06:45

Figured this one out myself

SELECT C_FKey,LN,attempt,
MAX(CASE WHEN Seq=1 THEN Stream_FKey END) AS X_Fkey, 'XNAME' as IndepTransducer,
MAX(CASE WHEN Seq=1 THEN Value END) AS Xvalue,
MAX(CASE WHEN Seq=2 THEN Stream_FKey END) AS Y_Fkey,'YNAME' as Transducer,
MAX(CASE WHEN Seq=2 THEN Value END) AS Yvalue, recordNum
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY C_FKey, attempt,recordNum ORDER BY stream_fkey) AS Seq,*
FROM ( Select * from [server].[dbo].[someTable] where LN between 4130 and 4260 and ( Stream_FKey = 5259 or Stream_FKey = 5260)
) dtable
)t
GROUP BY LN,C_fkey,Attempt, RecordNum

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources