Joining views - SQL Server Forums

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

Joining views

Author

Topic

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 12:36:04

So Im not really sure what type of SQL I am even using...the boss just said the other day "we need a SQL query" and no one here knows it at all... So this is being built off of the microsoft query in office 2007.

Anywho, the problem is I have two queries that arent at all related, but I need to consolidate them into one download, apparently sharepoint (which is where this workbook is ultimately going) will only allow for one refreshable area.

I made the two connections into views, and I figured I could just FULL JOIN them together in another query and end up with one download of data with everything I need. BUT, when I did that it prompted an error saying the only types of joins allowed were left right inner and exception.

So...I would like to add a column to each download with just a number increasing by 1 for each row, that way i can join them by that and everything will be fine....so...how do I do that?

thanks for any help!!

~G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 12:46:27

without knowing what rdbms you're using we cant suggest much. any ways looks like what you need is cross join if they're unrelated. would you please post what's sample output format?

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 13:19:05

perhaps this would help clear it up

Name ID Dealer
Alice 1233 honda
Bob 5467 chrysler
Cindy 1378 dodge
Dave 546 infinity
.
.
.

this needs to be joined to a table with far fewer records that has a summary of how many deals were done in a day.

Month Count
jan 8
feb 10
march 13
april 14
.
.
.

so the output desired would be

Name ID Dealer Month Count
Alice 1233 honda jan 8
Bob 5467 chrysler feb 10
Cindy 1378 dodge march 13
Dave 546 infinity april 14
.
.
.

As you can see this is sort of odd, since there is no relationship between the first and second tables. Alice didnt buy a deals in jan...I just need all that information so I can play with it in excel.

is there some way to do this?

let me know if there is something else you need to know.

THANKS!!!

~G

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 13:20:18

sorry the format isnt very good on that, the first table is three columns: name, id, dealer

the second is two columns: month, count

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 13:23:53

it also might help to know that the second table is created by grouping by date...

CREATE VIEW COUNTER AS

SELECT COUNT(APPMSTP.APFNDD) AS COUNTS, APPMSTP.APFNDD

FROM APPLIB.APPMSTP APPMSTP

WHERE (APPMSTP.APFNDD Between 20080701 And 20090631)

GROUP BY APPMSTP.APFNDD

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:36:24

how can you identify which count belongs to whom?

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 13:41:52

thats the thing, they don't belong to anyone, they are unrelated, there is no reason for them to be in the same table, the only reason Im putting them together is because of a technical issue with sharepoint that only is allowing one querry. So its like you had a table of customers with their names and how much they bought, and right next to it you have a column with january, february, march, etc with how many sales we had that month.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:44:24

then what meaning does it make in putting them together?

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 13:46:59

there is no meaning in them being put together, the relationship across the row will break down once it gets to the other part. but thats ok, because i just need all the data somehwere in the spreadsheet, I can pick it out with excel.

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 13:49:10

alright, can this be done: i have a view created as written above called COUNTER.

Can I add a column to that with the numbers 1,2,3,4,5... all the way down to the last record?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:57:07

you can merge them into one but cant always guarantee order of merging ie. which row goes to which row

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 14:00:52

thats totally fine, how do I go about merging?

CPSRiskAnalyst
Starting Member

9 Posts

Posted - 2009-06-24 : 15:23:51

Nevermind, I got it, thanks for the help