Sql Server Query For Self Join

Nov 18, 2006

Hi

I have following data in a table

Table_Test

ColA ColB ColC ColD ColE (Column Header)
1 NE A CM 5
2 NE A MU 7
3 NE A NO 9

I want to write a query which returns me resultset like this

ColB ColC ColD ColE ColD ColE ColD ColE (Column Header)

NE A CM 5 MU 7 NO 9

can any body tell me, what will be the query for this result set.

Regards
Mahesh

View 3 Replies

Query And Join Between 2 Databases On Same Server?

Nov 9, 2011

I am trying to query 2 SQL databases on the same server and inner joining together.

Databases: Goldmine_MIOA
Goldmine_GMBase

From the "Goldmine_MIOA" database I need to query contact1.* and from the "Goldmine_GMBase" database i need to query cal.

I would then like to inner join both these together.

View 12 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

Why Does My Query Timeout Unless Force Join To Hash Join?

Jul 25, 2007

I'm using SQL Server 2005.

A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).

The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).

If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.

If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.

So, this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC

and this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC

But this does't:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC

What should I be looking for here to understand why this is happening?

Thanks,

john

View 1 Replies View Related

SQL Server Equivalent For The Oracle Join Query

Nov 30, 2004

Hi,

Kindly give the SQL Server equivalent for the below Oracle query :

select *from t1, t2, t3 where
t1.t1col1 (+) = t2.t2col1 and
t2.t2col1 (+) = t3.t3col1

Thanks,
Sam

View 14 Replies View Related

?HLEP..Sql Server UPDATE INNER JOIN QUERY ?????..

Jul 23, 2005

Im using an ADP to connect to a SQL Sqever DB.In access it was really easy to sayInner join on table1 and table2 and update columnA from table1 withcolumnC from table2 where table1.key = table2.key and table2 columnB =1 and table2 columnD = 4I have tried all manner of beasts to get this thing to work..UPDATE dbo.GIS_EVENTS_TEMPSET FSTHARM1 =(SELECT HARMFULEVENTFROM HARMFULEVENTWHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))This almost works but ignors the 'HARMFULEVENT.UNITID = 1 ANDHARMFULEVENT.LISTORDER = 0' part which is really importantAny Help would be great....

View 4 Replies View Related

Outer Join Oracle Query Translate In Sql Server

Apr 16, 2004

Hi,
I have this oracle query with outer join situation. how can i convert it into sql server query.

SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
PRES_ITEM.RET_PERC
FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
AND CP.DB_CONTRACT = AE.DB_CONTRACT
AND CP.pc_code = 1

Any Help will be appreciated.

View 3 Replies View Related

SQL Server 2008 :: Query To Union Join Data

Jul 30, 2015

I have a query which wants to union join the data. no matter how many times I tried, I got an error. How to change my union query?

select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT
(Select distinct b.lev5
from bf_data a
inner join bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: Converting Inline Query Into Join

Oct 16, 2015

I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:

Original query:

SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:

[code]....

View 8 Replies View Related

SQL Server 2012 :: Correlated Query To INNER JOIN Or Window Function

Mar 31, 2015

I'm having some performance issues with a TSQL query. It's a complex statement but the main issue is the correlated query.

How can I convert this

SELECT TOP 5
(SELECT SUM(lt2.col3)
FROM dbo.MyTable2 lt2
WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) AS Result
FROM dbo.MyTable1 t1
... to an inner join or a sql2012 window function?

By the way, I just added the TOP 5 myself while testing. It's not in the main query.

View 9 Replies View Related

SQL Server 2014 :: Writing A Cross Join Query With One Table?

Jul 19, 2015

writing a cross join query with one table:

Cities(City_name, X_coordinate, Y_coordinate)

the result should be all combinations without reverse column returns

SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???

for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)

View 8 Replies View Related

SQL Server 2014 :: Column Store Query Reverting To Row Mode With CROSS JOIN

May 20, 2015

I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.

-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.

-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;

[Code] .....

View 6 Replies View Related

SQL Query Question - JOIN Or Not To JOIN

Jan 2, 2006

I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.

View 3 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses

Thank You

View 1 Replies View Related

DELETE Query - Can't JOIN. Need Sub-query?..

Mar 30, 2007

I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?

What I initially tried to do was:

Code:

DELETE FROM tblProductState
JOIN tblProduct
ON tblProduct.id_Product = tblProductState.id_Product
WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1

Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1

How can I do this without using JOIN. Use a sub-query? How?

Thanks

View 4 Replies View Related

Enquiry On Join 3 Query In 1 Query

Sep 29, 2007

Code:

SELECT ISNULL(count (agencyCandidate.JobID) ,0) as Total,
MAX(RecJobAds.PostedDt) as PostedDt,
MAX(RecJobAds.JobTitle) as JobTitle,
RecJobAds.JobId,
MAX (AgencyCandidate.AgencyId) as agentID,
MAX(RecJobAds.AdStatus) as status,
MAX(RecJobAds.CompanyId) as CompanyId,
MAX(RecJobAds.RecId) as RecId
FROM RecJobAds
LEFT JOIN AgencyCandidate
ON RecJobAds.JobId = AgencyCandidate.JobId
GROUP BY RecJobAds.JobId
ORDER BY Total ASC

i have the above query. but i would like to join in a third table, where my query is

Code:

select * from RecruiterMA where Activated = '1'.

anyidea in which place i can put the second query in the first query? thank you very much!

View 4 Replies View Related

Join First Query With A Query That Contains Unions

Jun 10, 2013

I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.

Query 1
SELECT
e1.File_NBR,
e1.Division,
e1.Department,
e1.Program,
e1.UNIT,
(e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,

[Code] .....

Query 2
SELECT
'Skill Rating' as Assessment_Type,
bs.File_NBR as ID,
bs.Skill_NBR,
bs.Best_Score as Score,
bs.Assesment_RND

[Code] .....

View 9 Replies View Related

T-Sql And X-Query Join

Nov 30, 2007

Hi, im searching for some help, here is my problem:i have an Appointments Table ex:Appointments AppointmentID : int ContactsList : xmlstruture of the ContactsList column:<ArrayOfContactsInfos> <ContactsInfos> <ContactID>1</ContactID> </ContactsInfos> <ContactsInfos> <ContactID>2</ContactID> </ContactsInfos></ArrayOfContactsInfos>And my Contacts Table:Contacts ContactID : int FirstName : varchar(200) LastName : varchar(200)What i want to do... is find the right query for retreive a list of Appointment based on the FirstName or LastName of a Contact in the ContactsList collumnis this possible to do a sort of join between the xml and the Contacts Table?and if yes how?Please can you help me?

View 1 Replies View Related

Query Inner Join With Top

Mar 5, 2008

Hello,There are 2 tables A(bot_id,bot_date,gomA,gomB) B(gom_id,hist_date, gom_name)
table B holds the names for gomA and gomB in tableA.I wish to select A table and inner join to it gom_name for gomA and gomB IDs.The problem begins when in table B there is multiple values for gom_id, with difference names.So, to retrieve the correct B.gom_id, there is 2 conditions. first bot_date>B.hist_date and after this, if we still gets multiple records then the top record will be selected (order by date)
Any ideas?

View 7 Replies View Related

Can't Join This Query

Jul 21, 2004

I really need some help. I'm going nuts. I been trying to get this to work all morning and i cant get it it work. the simple version is I am trying to join these two sql queries and make them one.

Code:

SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
WHERE news_id =41

SELECT CONCAT('users.user_fname', 'users.user_lname') *AS edit FROM users WHERE users.user_id=54

my previous attempts are

trouble with query.

I have a news table which has the ID of the original author and a column for the author who last edits the news article. both columns store the id of the author.

now when I'm editing the article I want to get the name of the author from the users table.

I have done a left join to get the first author (original author) but I'm having a real hard time trying to get the name of the author who last edited the record.

they both need to lookup the user_fname and user_lname fields from the users table.

my current sql is below

Code:

SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41

which gives me

Code:

news_id *news_subject *news_article *news_author *news_date *news_edited *edit_author *org_author *
41InterclubBunbury IC club has asked us all to attend a inter...771090247547109041836254Adam Green

Now how do i adapt it to get the name of the author to last edit the article? do I do a sub query another left join ???
every way I try doesn't work.

this is what I'm currently trying to get it with.

Code:

SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (

SELECT CONCAT( users.user_fname, " ", users.user_lname ) AS edit_author
FROM news
WHERE news.edit_author = users.user_id
)
WHERE news_id =41

Heres a different approach that I thought would work

Code:

SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (

SELECT concat( users.user_fname, users.user_lname ) AS edit
FROM users
WHERE users.user_id =54
) AS edit
WHERE news_id =41

but no i get this error

Code:

MySQL said:

#1064 - You have an error in your SQL syntax near '(
SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE' at line 4

I also tried

Code:

SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author, *(SELECT concat(users.user_fname, users.user_lname) AS edit from users where users.user_id=54) AS edit FROM news LEFT JOIN users ON news.news_author=users.user_id
WHERE news_id=41

but got this error

Code:

MySQL said:

#1064 - You have an error in your SQL syntax near 'SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE us' at line 2

can anyone help me with this query its been driving me up the wall all morning.

View 2 Replies View Related

Join Query

Jul 28, 2004

Hi,

Please help me in writing a join query.

I have three tables with three columns each. Now I want to retrieve data from all the three

tables as one.

Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points

These are related to three different divisions. So, on analysis we have get the no. of points

accumulated on a day. On any day the points can be in all three divisions or in any one or two

divisions. My database SQL Server 2000.

So, how to get this sort of output.

EmpidDatePts(DIv1) Pts(DIv2) Pts(DIv3)
V00107-24-2004NullNull25
V00207-24-200420Null25
V00307-24-2004Null30NUll
V00107-23-200415NullNUll
V00207-23-2004102525
V00107-22-2004Null1025

I'm badly in need of help. Any sort of help is appreciated.

M.L.Srinivas

View 3 Replies View Related

Join And Sum Query

May 30, 2012

how do I add in a 3rd and 4 table to the mix and get their sums and group by ...The top select works fine the lower one is what I was thinking is this.....

Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal
FROM dbo.Cust P
INNER JOIN
(SELECT
CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent,
Sum(Balance) as Bal
FROM dbo.CAsh
Where Accumulator='DP'
GROUP BY CustID) A ON P.CustID = A.CustID

The 2 new tables are ViewDis (X) - Sum (Distance)and ViewACC (Y) Sum Fields required Hours, Min, (Group by) Building and Date.

Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building
FROM dbo.Cust P
INNER JOIN

[code]....

View 14 Replies View Related

Self Join - Query Reg

Apr 8, 2008

I've a table like

Object ObjIdParentObjId
------ ----------------
Obj1 |101 |0
Obj2 |102 |101
Obj3 |103 |0
Obj4 |104 |102

If the 'ParentObjId' is 0 it means no more parent,

If I filter the Object value as Obj4, it should show the following rows

Obj4 |104 |102
Obj2 |102 |101
Obj1 |101 |0
Guide to me write a SQL for this situvation?

View 17 Replies View Related

Query JOIN

Jun 10, 2008

i have two tables field1 is the primary key:
table1
--------------------
field1|field2|field3
---------------------

table2
---------------------
field1|field4|field5
---------------------
what we need is to get the results based on field1 matching datas, but is also a requirement that if there is no matching data in field1 of table2 w.r to table1 we just need to return table1 datas with null values for table2 fields.

is that possible, i used inner, right joins but all in vain.

what I need is a output like this

output
---------------------------------------
field1|field2|field3|field4|field5
---------------------------------------

View 5 Replies View Related

Join Query

May 17, 2006

i hav 2 tables which contain a common field called ID.
Table A Table B
ID ID
a a
b b
c
d

Now i want results from Table A in such way that ID's Which are in table B do not get selected in table A.
how do i use join query for this

View 2 Replies View Related

Inner Join Query

Mar 15, 2007

Hi

I have two tables and one linking table they look something like this

<<<<<<<Application Table>>>>>>

AppID AppName
1 MS Word
2 Excel
3 SoftGrid

<<<<<<System Table>>>>>>>>

SysID SysName
1 System 1
2 System 2
3 Server 1

<<<<<<Link Table>>>>>>>>>

SysID AppID
1 1
1 3

How can i retirve the data so that it picks up and displays all the applications that are not on the the server as well as those that are

for example the output im trying to get is as follows from the data above.......

SysName AppName Y/N
System 1 MS Word Y
System 1 Excel N
System 1 SoftGrid Y
System 2 MS Word N
System 2 Excel N
System 2 SoftGrid N
Server 1 MS Word N
Server 1 Excel N
Server 1 SoftGrid N

i want to able to get a dynamically generated Y/N colunm

Im ok with doing innerjoin queries but the problem is how do i get the rest of the results that have no links to show that there it does not exist.

Thanks

View 3 Replies View Related

Sub Query In Inner Join

Jun 4, 2007

Hi,
I need to write a query to get the data in hierarchy from a table.
Like emp table where each employee(EMPID) has an manager and manager is also an employee(EMPID).
There could be 2 level of managers. Mean an employee who is a manager can also have manager.
So i need to write query which shows three columns with column1=toplevel manager, column2=secondlevel manager and column3=team member.

Any help will be appreciated.

Thanks,
Nadeem

View 7 Replies View Related

Join Query

Jul 12, 2007

I use the following query

select distinct safetyvideo2007.user_id,safetyvideoview.name
from safetyvideo2007,safetyvideoview where safetyvideo2007.user_id=safetyvideoview.user_id
order by safetyvideoview.name asc

this retrive records only where first table user_id= second table user_id

user_id......name
---------------------
p0088421Khan
p0038040Peter
p0031344Jones
....

table1 = safetyvideo2007 user_id colum
table2 = safetyvideoview user_id and name column

I want the it retreive records from safetyvideo2007 all records
if first table only have user_id column no name column available.

ie. show table safetyvideo2007 all user_id if second table safetyvideoview user_id does not found ? ie.
it found user_id in second table then ok otherwise it retreive
all records from safetyvideo2007.

like this.
user_id.......name
---------------------
p0088421Khan
p0038040
p0031344Jones
p0031345Martin
p0031347Wagner
p0031357
....
...

display all safetyvideo2007 user_id if second table safetyvideoview
user_id not found ?

regards
Martin

View 2 Replies View Related

Join Query Help

Jul 25, 2007

Hi,

I have a table with following fields..

tblEmployee
-----------

EmpID int,
EmpName char(20),
MgrID int

I want the name of all employees and names of correspoding managers..

Please help me as soon as possible..

Thanks in Advance..

Regards,
Asha Sivan

View 1 Replies View Related

Join Query Help

Sep 20, 2007

View 3 Replies View Related

Join Query

Jan 4, 2008

I have tab1 as:

Col1 Col2 Col3
1 2 Null

Then the Master as:

Col Name

1 One
2 Two
3 Three

I want the output as

Col1 Col2 Col3
One Two Null

Any help? I have been struggling since yesterday.

View 3 Replies View Related

Query Using Self Join

Jan 23, 2008

Table EMP
EMP_IDEMP_NAME
1A
2B

Table MgrID
EMP_IDMgr_ID
12

How to have the following result
EmpNameMgr_Name
AB

-Senthil

View 2 Replies View Related

Query Inner Join

Feb 20, 2008

Hi i have a query which i cant get to work,

i need to display categories and sub categories

Category 1
SubCat1
SubCat2

Category 2
SubCat1
SubCat2

but i am actually geting

Category 1
SubCat1
Category 1
SubCat2

Category 2
SubCat1
Category 2
SubCat2

any ideas what i need to do to this query?

select CT.Cat_ID, CT.Cat_Name, ST.SubCat_Name, ST.SubCat_ID
from dbo.Category_Table CT

LEFT JOIN dbo.SubCategory_Table ST ON ST.SubCat_Cat_ID = CT.Cat_ID

View 4 Replies View Related