| Author |
Topic |
|
lars_toreh
Starting Member
29 Posts |
 Posted - 2009-11-28 : 12:15:39
|
Hey guysI`m using SQL Server 2005,and have a problem with tables.I have set up all the data in the tables,and opening all fanes,than I do a CROSS JOIN,and when the query comes up,the tables got 1000 items instead of 10,what can be wrong?Sorry if my post doesn`t make sense,not sure how to explain otherwise.Here`s a picture of my screen: Thanks in advanced |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-28 : 13:31:02
|
Yes - your CROSS JOIN joins all entries in all tables without looking at any relations.Without relations between your tables that's all senseless.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-28 : 13:40:38
|
| as per reqmnt how should data be related? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lars_toreh
Starting Member
29 Posts |
Posted - 2009-11-28 : 14:12:51
|
| Thank you guys :)My problem is when I choose Add table and I choose these tables and press add,CROSS JOIN comes up instead of INNER JOIN.But if I understand this correctly that`s because I haven`t added relationships between the tables?How do I do that?Thanks again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-28 : 17:05:26
|
| I don't use the GUI to create my queries, so I'm not sure if the missing relationships are the reason why the GUI is choosing CROSS JOIN or not. It sounds very plausible. I add my relationships via ALTER TABLE, which has examples in BOL. I don't have SSMS in front of me right now, but I know you can add relationships from the GUI too just need to dig around a bit.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
lars_toreh
Starting Member
29 Posts |
Posted - 2009-11-28 : 17:18:02
|
| Thanks Tara :)I`m just using the GUI now in the start,but when I`m going to get specific data from the tables I`m going to use queries.I know relationships is in that menu somewhere :)I`ll try it and report back how it goes. |
|
|
|
lars_toreh
Starting Member
29 Posts |
Posted - 2009-11-28 : 17:46:31
|
| I get a problem when I try to add relationships."The new relationship must have at least one pair of related columns". I tried to add two tables with columns with a equal name,but no luck,so what else can it mean?Does it mean it has to have two equal columns per table?or something else? Thanks in advanced |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lars_toreh
Starting Member
29 Posts |
Posted - 2009-11-28 : 19:52:46
|
I`ll try my best.Here are the three tables:   To find relationships you have to be in Design view,and right mouseclick.Here`s the menu. Trying to link one of the tables to the other. And here is the error I get. Sorry for the lousy screenshots,I hope you guys can get something out of it anyway.Thanks again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lars_toreh
Starting Member
29 Posts |
Posted - 2009-11-29 : 07:48:52
|
| I`ll try again,sorry I`m a n00b on this subject.Table 1 - name: Tittel_Emne_ISBN_Utgitt Columns: Tittel, Emne, ISBN, UtgittTable 2 - name: ForfatterID_Fornavn_Etternavn Columns: ForfatterID, Fornavn, EtternavnTable3 - name: ISBN_ForfatterID Columns: ISBN, ForfatterIDLet me know if you need to know more.I gave the tables the same name as the columns,so it was easier to remember which columns are in which tables.I`m not sure which columns I want associated with each other,I want the 3 tables connected somehow,but what would be the most natural columns to associate and connect the different tables with?Thanks again :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-29 : 11:59:27
|
| I wouldn't name your tables that way. Table names should reflect what kind of data is stored in them and not the column names. But I digress.It appears you want to related ISBN in Table1 and Table3, and also ForfatterID in Table2 and Table3.ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Table3] FOREIGN KEY([ISBN])REFERENCES [dbo].[Table3] ([ISBN])GOALTER TABLE [dbo].[Table3] WITH CHECK ADD CONSTRAINT [FK_Table3_Table2] FOREIGN KEY([ForfatterID])REFERENCES [dbo].[Table2] ([ForfatterID])GOI'm not clear which table has ISBN and ForfatterID as PKs, so I guessed in the scripts. You may need to correct as the PKs need to match.PK stands for primary key. Those must be setup to create the FKs, so maybe that's why it is erroring for you.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
lars_toreh
Starting Member
29 Posts |
Posted - 2009-12-02 : 10:00:57
|
| Thank you Tara :)I have changed the names of the tables and done some more tables and columns,and it works like a charm.However I have encountered an error.I`m allmost done,I`m going to add the results from some of the tables to webpages.I have used SQL Server to make the tables as you know,and have exported the database yesterday(Thuesday) to Visual Web Developer and managed to get all the data to the gridviews,and have only the specific queries left as I mentioned above.But today when I tried to start SQL Server,it wouldn`t open,and I got the same errormessage when I`m in VWD and on gridview,clicking new connection,adding the database there and pressing OK.Here`s the error message:A network-related or instance-specific error occured while establishing connection to SQL Server. The server was not found or was not accessible.Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server.)Thanks in advancedBest wishes Lars T. |
|
|
|
|
Need help!