|
I need to Find un identical rows from two table based on combination of 3 columns. Both table has same structure. Using SQL Server 2005
Ie Table1 and Table2 to has the columns Type,ID,Parent
1. Query the rows from Table1 which are not matching in Table2 with respect to columns Type,ID,Parent (ie. Need not required the rows which are matching combination of Type,ID,Parent in both table)
2. Query the rows from Table2 which are not matching in Table1 with respect to columns Type,ID,Parent (ie. Need not required the rows which are matching combination of Type,ID,Parent in both table)
Please help!!
|
|
|
|
|
Here's[^] some nice basic info about joins and how to do them. It even has pictures
|
|
|
|
|
Did you try to write query before you ask here? In your previous question you took good answers which are valid for your question which you are asking.
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
I have a table with this records(GroupId,ParentGroupId,GroupName).
i want to show a group with its full path, from main parent to it self
like this: red cherry: red cherry>>cherry>>fruit
i write a Procedure that give me its parents names,but i want to have a prosedure that append the parents name and give me one string
i want to do this for all records in that table
thanx for u answers
|
|
|
|
|
What you need is a common table expression[^] which is recursive processing of a table.
This may not be correct as your structure seems to be rigid (you know how many level to the top) and therefor you simply do a bunch of inner joins.
Select *
From Fruit
Inner join Category on Category.CategoryID = Fruit.CategoryID
Inner Join level2.ID = Category.Level2.ID
...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear Experts,
I am using MS SQL server 2005. I want to Query the records that are not same in two different table.
ie., I have 2 different tables TABLE1, TABLE2 which are having same columns. (and most of the row are same)
Q1 : I want to query the records of TABLE2 which are not avaliable in TABLE1
Q2 : I want to query the records of TABLE1 which are not avaliable in TABLE2
Needless to compare the primay key.
Thanks in advance!!
|
|
|
|
|
SELECT a.*
FROM table1 a left outer join table2 b
ON a.id = b.id
WHERE b.id IS NULL
That will show you a list of records that are not in table2.
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
|
|
|
|
|
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!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
