Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused. I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item. A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:Select * FROM ItemsINNER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID IN (10, 13, 17)But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull out ONLY items that have a AttributeID of 10 AND 13 AND 17.Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :SI've also tried:Select * FROM ItemsINNER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID = 10 AND AttributeID = 13 etcBut obviously that won't work! :s
Hi all, I am trying to create a diagram for our database, during the creating, I create some of the relationships which were not there(basically our original database is not relational database, that's why I am doing it) So sometimes I have to chage data type in order to create a relationship for the coloumns in different tables. i.e. change char(16) to varchar(7) (I checked the field that make sure all the data in this field is <= 7 characters)
But when I saved the diagram, there is an error message that state: Errors were encountered during the save process. Some of your database objects are not saved on your diagram.
'agent' table saved successfully 'VisitUSA' table - Unable to create relationship 'FK_VisitUSA_agent'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VisitUSA_agent'. The conflict occurred in database 'CMC', table 'agent', column 'AgentCode'.
What does that mean? is it caused by some of the agentcode data in VisitUSA table which is not in agent table? Thanks! Betty
Hi i am trying to use this query to pull all the publications stored in the database and all the authors contributing to that publication (1 to many relationship). I am trying to use a sub query so that i can display the results on one row of a gridview (including a consecutive list of all the authors). However i am recieving this error: Incorrect keyword near the word SET. ?
Maybe i need to add a temp column in the sub query to pull all the related authors for a single publication - but i dont know the sql for this? can anyone help?
Thanks SELECT ISNULL(Publication.month, '')+ ' ' + ISNULL(convert(nvarchar, Publication.year), '') as SingleColumn, Publication.publicationID, Publication.title FROM Publication WHERE Publication.publicationID IN (SELECT (convert(nvarchar, Authors.authorName)) FROM Authors INNER JOIN PublicationAuthors ON Authors.authorID = PublicationAuthors.authorID) AND Publication.typeID IN (SELECT PublicationType.typeName FROM PublicationType INNER JOIN PublicationType ON Publication.typeID = PublicationType.typeID
I have cubes that hold quite a few calculations and so creating Excel pivot table views from it take a long time. This is even true for Excel 2007.
Now I wonder if it would be possible to write back all the calculation results to a relational table - maybe one that exactly matches the report format - so creating another report would be much faster?
SSRS seems to be a way to go but it does not speed up my Excel case.
I read about write-back in ROLAP and MOLAP but I don't think any of these concepts help me to really speed up my reports.
The closest thing I was able to find so far, which besides seems to do exactly what I want is Microsoft's new PerformancePoint 2007. It's just it seems overkill for my projects and the price is at $20K.
I believe saving prediction query results to relational tables is possible (the BI studio does it!). I am not clear on how to do this w/o the BI studio, which means if I write a DMX query and want to store its output to a relational table, how do I do it?
hello, I am beginner for asp.net and sql server. I used Sql server manegement studio full version and I exported my aspnetdb which was created by VS2005 to my host sql server. I have a question: relational tables are not relational no longer. I noticed that when I created database diagram. what is wrong by exporting? thanks for your helps...
I have a membership database and a profile database with a userid column in both. The foreign key is in the profile database where i want a persons profile details to go like location,occupation and what not. How do I make it so when a new user registers a userId is created in the profile database that matched the userid in the membership database so i can query out profile details based on a registered members userid?
Table1 contains ID,Name Table2 contains ID,Marks,Foreign Key ie Primary key of the table Table1.
Give me an information how to create these two tables in SQLServer (I know how to create a table but i don't know how to create a table which includes Foreign Key.)
Then using Dataset i want to display the records as Name,Marks which are stored in two tables.
I have studied that in ADO Join query and record set object is used but it gives a problems and it is not good when we want to transfer the data between two applications or pages but dataset solves all those problems.
what is the difference in making relational databases in SQL vs. Access? Do you need to make them if you will be using ColdFusion? What do the other fields in minor tables need to be in order to connect them to a major table when it will all be based on a number that is in the major table? i'm kind of confused on this issue...Thanks!
I created the third table to hold the employeerenewal dates, I did this because I cant have repeating vaules in the primary table. I am just making sure that my course of action is the correct one. The purpose of the Employeerenewals is to give the users an indication that a certain renewal has been processed, because in the past there was several hundred that were not processed. The problem was that the users had no way of knowing this. What I was thinking was having the primary table calculate a renewal date based off the finalsuit and show the results in the Employeerenewals with a yes of no drop down. Now the yes or no drop down box will be give the users their red flag if the renewals have been processed or not.
Hi I am developing a website current and i have a database that seems to be growing very rapidly.. I would like to ask if anyone knows of any good tutorials/examples on relational databases? Also does anyone know of any good database design tutorials/examples?
If it helps i am using Microsoft Visual Web Developer
My question: How to have an unknown number of authors returned for a list of books, with the multiple authors being returned on the SAME row as each book
My table structure:
Table A - Books ------------------- BookID | BookName ------------------- 1 | Book One
Hi,I have a very simple question.In what cases are relational databases necessary?Are they really necessary in cases where only asingle type of query is to be performed based on one uniquefield or can we just put all fields together in a single databaseand just access them through that unique field?
Hi,This question has been bothering me for some time. A lot of peopleseem to "think" XML is the king of data problems, and I've heard thatnext version of SQL Server is going to have a strong XML flavor,meantime, I seem to get the impression that a large number ofhard-core relational model gurus do not seem to be that impressed withXML (technical value of this extra layer seems to be limited whilebusiness value might be substantial for instance, more software work,more disk space requirement etc. etc.). What's your take on this?Generality or specifics, all welcome. One specific question is, howcan XML supplement relational model?Thanks.
I have a few questions for you guys. I have a client application that can be offline or online. While offline, records can be added and need to be later synced to production.
I will use rda to pull the table down, and this is working fine. Now what if I have multiple tables where I want a foreign key relationship?
With rda I can only pull down one table at a time from everything I've read. Now say create a constraint after pulling the two or more tables down. While in offline mode I create a new record on two seperate tables with foreign key/primary key relationship.
When I do the push to the server will it automatically update the foreign key reference (locally) to the right one on the production server? Or will I get a duplicate primary key error? On the production server the primary key will be different because of the identity. This is very important because I will have multiple clients.
Hi, not sure if this is the right forum for this question. I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know.... Nick
I am just now starting the switch from .NET 1.1 to .NET 2.0. I really like the new way of using the SQLDataSource and setting up Views declaratively as opposed to doing it all in code, which brings me to my question.In some of my applications I have a single Stored Procedure return multiple result sets to a single DataSet where I have a DataRelation set up. Then I can have nested DataGrids that use the GetChildRows() method to filter the results to display the hierarchical data. I would like to do something similar with the SQLDataSource and GridViews but haven't found a way to get multiple result sets.One thought I had was to create a Strongly Typed Dataset and then use the ObjectDataSource object, but I still didn't see a way to get child rows out of the datasource. I've seen an example that uses a <FilterParameter> to get nested data, but there is an extra trip made to the server for each parent item as it just put an extra parameter in the WHERE clause of the query.
This is a bit of a trivial question, since I know how to actually create the code in SQL. Hope this is the right section to post it in too, since it sort of concerns design I guess..
My problem is how to I represent the following SQL query in relational algebra:
SELECT * FROM Staff;
Since I'm using SELECT, I assume I use selection to represent it, so would it be as "ó(Staff)" [using the sigma symbol, in case it doesn't show]? But as I'm not specifying a predicate since I wish to select all fields in Staff, would it be simply represented as "Staff"?
I appreciate any help... it's been baffling me for a while
Hi Please help for this simple problemDTS Transfer or any other method?I have Customer_Order Table and customer_Order_Details Table.For OrderID = 1, I have 3 rows of Order Details.I want to transferCustomer_Order Table for OrderID 1 in DTS, the system should transferOrder Table as well as Order details table Rows for ORderID =1.How to customize in DT or is there any way to get this data to transferfrom source DB to Dest DB?KAMAL KUMAR V--Posted via http://dbforums.com
I understand the basics for doing an insert. Their seems to be many ways of achieving the same thing in .net. I have used the tableadpaters and sqlcommand functionality to achieve this. My one question is how foregin keys should be created in associated tables. EG so if i create a new record entry in one table and the primary key for that entry is a foreign key in another table, do I need to call 2 table adapters, or run 2 sqlcommands(taking the primary key from the first traction and use this in the next transaction)?
Any help or direction for some good tutorials on this would be much appreciated.
Is there a good, common way to migrate data from relational tables into hierarchical json?
I am asking this because, basically, I prefer not to write a lot of procedural code.
I am currently working with MS SQL Server db and I’ll soon need to do the same with My SQL db. I use java and java db connectivity for access. It would be great if any of these two db’s can somehow assemble the data server-side.
set @v_dbquery = char(39)+'SELECT [ProspectAlternateKey], [FirstName], [LastName], [MaritalStatus], [Gender], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [HouseOwnerFlag], [NumberCarsOwned] FROM [dbo].[ProspectiveBuyer]'+char(39);
set @v_query = 'SELECT [TM_Cluster].[Bike Buyer], t.[ProspectAlternateKey], PredictProbability([TM_Cluster].[Bike Buyer]) From [TM_Cluster] PREDICTION JOIN OPENQUERY([Adventure Works DW],@v_dbquery) AS t ON [TM_Cluster].[Marital Status] = t.[MaritalStatus] AND [TM_Cluster].[Gender] = t.[Gender] AND [TM_Cluster].[Yearly Income] = t.[YearlyIncome] AND [TM_Cluster].[Total Children] = t.[TotalChildren] AND [TM_Cluster].[Number Children At Home] = t.[NumberChildrenAtHome] AND [TM_Cluster].[House Owner Flag] = t.[HouseOwnerFlag] AND [TM_Cluster].[Number Cars Owned] = t.[NumberCarsOwned]' -- print @v_query
set @full_query = 'select * from openquery (DMserver,'+char(39)+ @v_query +char(39)+')' ;
I've not found much guidance so far about the pros and cons of mining OLAP cubes vs their underlying fact and dimension tables. Can anyone offer advice in this area, or point me to more info about this?
What is the best approach for storing hierarchical data in a database? For example, if I need to store a tree menu system, how would I do that allowing for the most normalization within the database, using the least number of queries/resources when pulling the data out, and using the least amount of overhead both in storage and retrival?
I have a flat file table that describes crash data in SQL Server.It contains vehicle information.I would like to know if anyone knows a SQL statement that could go fromthistable= eventsCRASHID | VEH1_TYPE | VEH2_TYPE | VEH_3TYPE-------------------------------------------555555 | CAR | TRUCK | VANTOCRASHID | VEH_TYPE | VEH_NUMBER--------------------------------555555 CAR 1555555 Truck 2555555 VAN 3Any Ideas? I am relitively new at this and can only see how it could bedone by creating multiple tables and appending them.Any help that could create the end selection in one query would begreat.Thanks,Chuck
Dear friendsI am conducting a survey on Relational Database usage and would likeyour help. The study is part of my MBA Dissertation.Could you kindly spare 5 minutes to take part in this survey?http://FreeOnlineSurveys.com/rendersurvey.asp?id=120816ThanksRajeev
Hello everyone, this is my first post here so hopefully I am not asking a common question.
I am trying to create a flat dataset in SQL 2005. Basically I run a query and I get multiple rows for the same primary key. The query I am running is quite large and has several different tables connected to it, here is a small sample of what it looks like...
Typeid(Primary Key) Individual Address
1 Sam 912 Ave. J
1 Sam 913 Ave. Q
1 Sam 914 Ave. R
2 Mike 1000 Ave. O
3 Jill 1001 Ave. O
I want it to kind of look like this
TypeID Individual Address_1 Address_2 Address_3
1 Sam 912 Ave. J 913 Ave. Q 914 Ave. R
2 Mike 1000 Ave. O
3 Jill 1001 Ave. O
As I said before, this query is pretty big, and has several variables like Address where multiple rows are being taken by one Primary Key.
If it is not possible to do this in SQL 2005, is there a program that may be able to? Right now we are using SPSS as sort of a bandaid... we run the query in small portions like the one in the sample and then restructure the in sections but this takes several hours.
Anyways, thanks for any help that you may be able to provide.
