I really enjoy answering questions which I receive from either comments or Email. My passion is shared by SQL Server Expert Imran Mohammed. He frequently SQL community members by answering their questions frequently and promptly.
Sachin Asked:
Following is my scenario,
Suppose Table 1 and Table 2 has same column e.g. Column1
Following is the query,
1. Select column1,column2 From Table1
2. Select column1 From Table2
I want to find common records from these tables, but i don’t want to use Join clause bcoz for that i need to specify the column name for Join condition. Will you help me to get common records without using Join condition? I am using SQL Server 2005.
Imran Mohammed Replied:
If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records.
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.
SELECT column1, column2
FROM table1
WHERE column1 IN
(
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
)
To do this, make sure your column1 is unique and do not have duplicate records.
This is good answer. INTERSECT is new operator in SQL Server which gives you similar answer without using JOIN. I have previously written article where I have compared INTERSECT with INNER JOIN I suggest that all user read that article for further clarity.
SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN
I would appreciate my readers input about this article and if you know any alternative method, it will be interesting to see them.
Reference : Pinal Dave (https://blog.sqlauthority.com)
97 Comments. Leave new
sir i want dname and ename from emp and dept
without using join .the result set is (ename,dname)
Why do you want to do without a join? If the dept table has empid column, you can do this
select (select dname from dept where empid=t.empid), ename from emp as t
hi,
I want to know how to find out a record from a database if we dont know the table name.
eg. find out the record in which (field name / Attribute) city = ‘hyderabad’ in mydatabase.
Follow this post. This is exactly what you want
sir, how to local db upload in my server Db
You can restore it in server. or you can attach the database files too
Hello Guna,
There is many way to store your data base into your local database.
1.Restore your database
2.Copy paste your database.
3.Attach your database.
hope you get your answer..
On the same note, I have three (3) tables A, B and C, each with same two columns Column1 and Column2, however I suspect that data inside the three tables may differ and I need to find the common data to the three tables, what would be the easier way to accomplish that?
I was thinking: join A and B and put the result on a temp table X
then join A and C and put the result on a table Z
finally join X and Z and get my result.
I believe there is a flow on that logic but I cannot point it out.
Any response much appreciated.
Quest:- I have four tables with a unique identity column and some more columns.i want to get all the columns into one row based on the id column.
1) Data can be available in all the tables
2)if not available in one of the tables the corresponding columns should show null or zero value.
for ex:-tab1 , tab2 , tab3 , tab4 consists of three columns including unique id column..
but data should come in one row…
Result should be like this:-
ID, tab1.col1,tab1.col2,tab2.col1,tab2.col2,tab3.col1,tab3.col2,tab4.col1,tab4.col2
please help me this is a bit urgent.
Hi sir i have a question related to sql query. i m developing a project on vb.net and in this i have to insert, update and delete and retrieve data in two tables in this both table have a equal column name that is File_Name. then how i perform this plsss. reply me.
waiting for ur response
Plzz.
Hi sir i have a question related to sql query. i m developing a project on vb.net and in this i have to insert, update and delete and retrieve data in two tables in this both table have a equal column name that is File_Name. then how i perform this plsss. reply me.
waiting for ur response
Plzz.
Hi Sir i am new to your blog and sql also.i want to merge three tables.is it possible to merge three tables?.
table1
———–
id name
1 xyz
2 xyz
tale2
——–
id
1
2
table3
——–
id
1
2
i want to merge id=2 in id=1 and it update all child table.like,
tb1 tb2 tb3
id name id id
2 xyz 2 2
2 2
table1
id name
2 2
———–
table2
id
2
2
———–
table3
id
2
2
hi all,
i’m new to sql and i want to merge to tables:
Table1: ddate1 Table2: ddate2
table1 contains 2 data with 01/01/2012 while table 2 contains 4 data with 01/01/2012. Whenever i use left or right outer join the last data in table 1 repeats itself until it reaches 4 data. how can i prevent that? i use code: select ddate1, ddate2 from table1 left outer join table2 on ddate1 = ddate2.. Any idea on how to make my result like this:
ddate1 ddate2
01/01/2012 01/01/2012
01/01/2012 01/01/2012
null 01/01/2012
null 01/01/2012
hi all i have a doubt in sql server
I have two tables like below
table 1:
name number
a 2
b 4
c 2
d 1
table 2:
name number
a 4
b 3
f 5
k 1
result table1
a 6
b 7
c 2
d 1
f 5
k 1
if column name is same for two tables that no should be add and store in first table table and the record with that name is not exist in first table that record should be add to first table.
I am trying to got that but i didn’t got that .
Plz send me that query if any body knows
select name,sum(number) as number from
(
select name, number from table1
union all
select name, number from table2
) as t
i have a table A
id startno endno
1 1 3
2 3 5
3 2 3
and another table b
No
1
2
3
4
5
want to have resultset :
id No.
1 1
1 2
1 3
2 3
2 4
2 5
3 2
3 3
how is it possible in sql? or we should use in sp with cursor??
declare @t table(id int, startno int, endno int)
insert into @t
select 1, 1, 3 union all
select 2, 3, 5 union all
select 3, 2, 3
declare @number table(i int)
insert into @number values (1),(2),(3),(4),(5)
select id, i from @t as t1 cross join @number as t2 where i between startno and endno
order by id,i
Dear Dave & all
There is two tables, 1st table contains one row for each id and 2nd table contains 20 rows for the respective id of 1st table, I need to show the 2nd table columns along with 1st table columns with repeat ids.
Thanking you
Kishor
hi, i am new user to ur blog . my proble is that
i have table thier is to column id and value like that
id value
1 a
1 b
1 c
2 e
2 f
3 g
i am want to out put like that
1 a,b,c
2 e,f
3 g
Search for FRO XML in this blog. You will find a relevent post
hello sir, i have created 2 forms one is to add the quantity of stock in my company and another is to dispatch the stock for particular users. when i dispatch the stock means that amount of stock should be automatically decreased in the stock view form. also if i add some other stocks means the form should be automatically updated. how can i do this in sql server 2005?
wow thats great idea…Thanks
How would if find records in table A (column name Proj_id) that are in another table B (column_name Proj_id) where B.Proj_id starts with A.Proj_ID
The result would be all records in A with the matching records from B (but not exact), the results might look like the following (note A will have parent like values,while B will have an exact match to A, but also children that I want to include in the results)
A.Proj_id B.Proj_id
ABC ABC
ABC ABC.000
ABC ABC.001
ABD ABD
ABD ABD.000
ABD ABD.001
and so on
Hello Please help,
I have a database I am working on with 2 tables, Employees and Job_Title. I need to find the minimum and maximum salary of all employees that are exempt, and all the ones who are non-exempt. This requires me to pull from both tables, because the field Salary is in the Employee table, and the Field Status is in Job_Title. I have gotten the following query to return all data from both tables for the employees that are exempt, and I am assuming that it will work in reverse, if I change exempt to non-exempt, but I am not sure how to proceed in order to find the maximum salary based on this query’s results??
Select * From Employee, Job_Title
Where Status = ‘exempt’
I was thinking like
Create Table
(Select * From Employee, Job_Title
Where Status = ‘exempt’)
and then take the Max (Salary) from that, but that give errors….
Please give me any advice possible to complete this goal, and thanks in advance!!
i have a problem i want to know query for this problem
in one table field are e-id,e-name,e-salary and in second table have the same fields.
now i have to find the e-id of these tables who has max salary.
in one table have data(1,2,3,4,5,6,) and in second table have(4,5,6,7,8,9)
using query i want this output (1,2,3,4,5,6,7,8,9)
thank you.