|
Thanks a lot Simon_Whale
But if I have one then one criatria to compare incase I also have a.id2 = b.id2..please suggest
|
|
|
|
|
the ON is what joins the two tables together is the second condition is more to restrict the results then you would put that in the WHERE clause.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Records of TABLE2 which are not avaliable in TABLE1
select * from table2 where ColumnName not in (select ColumnName from table1 )
TABLE1 which are not avaliable in TABLE2
select * from table1 where ColumnName not in (select ColumnName from table2 )
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
Hi
Is it possible to have 2 foreign keys that ae thesame in one table ?
Create table Product
(
productID int PK
)
Create table Category
(
CategoryID int PK ,
productID1 int fk references Product (productID) ,
productID2 int fk references Product (productID)
)
modified 31-Jan-12 6:30am.
|
|
|
|
|
Yes, although your table name is misleading and it looks like a design nasty to me.
Do you ALWAYS have EXACTLY 2 products in the Category (name seems wrong) table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
What you are asking is possible; however, be sure that your design is right. Off the top of my head, I thing a Category can have 1 or several products.
If that is the case, then I would have my tables as follows
Product(ProductID, CategoryID, ProductDesc,...)
Category(CategoryID, CategoryDesc,......)
Category ID would be a foregn in the product table
|
|
|
|
|
What if the product can belong to more than one category? (as is true in a real world situation).
|
|
|
|
|
Based on the question, it would be impossible to tell.As you can see my answer was conditional. Of course if there were many products for category and vice-versa, then a many-to-many relationship would exist which would be decomposed to two one-to-many relationships. The idea was to get the author of the question to think about their design carefully. I was not saying the solution must necessarily be what wrote. 
|
|
|
|
|
As has already been answered above, yes, it is possible. But your design is flawed. It accomodates only 2 products in a category. What if a category has more than two products?
In a real world situation, a category can have hundreds of products and a product can be classified under multiple categories. You should create what is called a bridge table to address this design question.
Create table Product
(
ProductID int PK
)
Create table Category
(
CategoryID int PK
)
Create table CategoryProductBridge
(
CategoryID int FK references Category (CategoryID),
ProductID int FK references Product (ProductID)
)
|
|
|
|
|
You're rather presumptuous. Although there are situations in which products and category have a many to many relationship, there are many real world situations in which a product belows to one category.
|
|
|
|
|
Only issue is the tables are on different servers.
What would be the best way? I tried looking at BULK INSERT, looks like you can insert into a file.
I tried - insert into A select * from B. It is taking for ever, after almost 3 hours of running the query, I had to kill it.
Any thoughts/ideas would be appreciated.
|
|
|
|
|
You've not mentioned which DB this is for, or if it is for two different vendors perhaps. But I would think for table replication involving 84 million rows, you should be investigating table export/import operations associated with the databases. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Apologise! It is sql server 2005.
|
|
|
|
|
|
Is this a one-time operation? If so, I'd simply backup the database, restore it on the other server, and do a "select into" in the night.
If the tables need to be in sync, I'd probably go for replication.
Bastard Programmer from Hell 
|
|
|
|
|
Backup and restore sounds like a good idea.
Why would I "select * into" again?
Also, I read that "select * into" is faster than "insert into select * from"?
I guess it would one way, since there will no indexes on the table, right?
thanks a lot!
|
|
|
|
|
vanikanc wrote: Why would I "select * into" again?
Also, I read that "select * into" is faster than "insert into select * from"?
I guess it would one way, since there will no indexes on the table, right?
I haven't compared them, just a habit of dumping new data in it's own table. If you want to speed up the copy further, then try to have the "source"-database on another diskdrive then the "destination" database.
Bastard Programmer from Hell
|
|
|
|
|
Problem is with the details.
Is this a one time operation? Is the target under load? Will there need to be a catch up phase (data in source system that did not get moved the first time.)
|
|
|
|
|
If it's going to be a regular operation I open a Connection to each database, use a DataReader to read from one, and ExecuteNonQuery to INSERT into the other. A major benefit of this technique is that it allows the app to periodically log its progress (and speed in rows per minute) so you're not flying blind.
This technique also allows you to catch and ignore selected Exceptions if you like and not have the whole process die.
|
|
|
|
|
Two seperate questions, but apply to the same query, so here goes:
SELECT ProdID, ProdTitle, CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName) AS Author FROM Products LEFT OUTER JOIN ...
This works and returns each item with multiple "author" like:
ProdID Prod Title Author
123 Title 1 John Doe
123 Title 1 Jane Doe
465 Title 2 John Doe
456 Title 2 Jane Smith
But what I want is:
ProdID Prod Title Authors
123 Title 1 John Doe, Jane Doe
465 Title 2 John Doe, Jane Smith
So I add a Group Concat to the Author and i get one line and a BLOB in the AUTHORS
SELECT ProdID, ProdTitle, GROUP_CONCAT(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName)) AS Authors FROM Products LEFT OUTER JOIN...
Question #1 is why does this not work.
Question #2 is how can I use the resulting "Authors" in the WHERE clause against a search string.
Edit 1 ref Question 2: I realize I can redo the concat in the where clause, and that is a fine answer if there are no other options, I just thought you could use the newly created "column" in the where clause directly. Seems cleaner then doing the code twice.
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
modified 29-Jan-12 19:09pm.
|
|
|
|
|
You're not stating what database you're using or what error message you're getting.
GROUP_CONCAT needs a GROUP BY clause in the query. It's unclear if you have one.
|
|
|
|
|
Sorry! Thought the Pre Tags listed the language... Using MySQL.
The group by was the error. I also ended up converting back to chars... For anyone's help, here is the code:
SELECT ProdID, ProdTitle, GROUP_CONCAT(convert(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName), CHAR(24))) AS Authors FROM Products LEFT OUTER JOIN ... GROUP BY ProdID
The second question is more of a best practice I guess. Is there a way to then use the "Authors" list in the where clause? I want the whole list returned, but only with matches to a search string. I know I can duplicate the entire GROUP_CONCAT(convert(CONCAT... but it would be nice if it was only once. Also, will the server actually run those twice? as that seems inefficient.
Thanks again!
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
|
|
|
|
|
The answer to #2 is:
HAVING Authors LIKE %SearchString%
Thanks for the help...
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
