Complex Self Join

Oct 16, 2006

This is a very complex query and i have tried everything with no sucess.

I'm having 3 Tables,

Orders which is having fields like CustomerID, ManufactureID, MerchID :- These all ID Fields (around 6) are foreign key of Contacts and Address Tables.

Address Table is having AddrID(Primary Key), ShortName

Contacts is having ContID (Primary Key), AddrID (Foreign Key), PersonName

I want to retreieve info in a single query which can return a single row with following columns :-

OrderNo, CustomerName, ManufactureName, MerchName etc. (all 6 columns) by joining these 3 tables



can anybody help in this.

View 5 Replies


ADVERTISEMENT

Complex Join

Sep 7, 2003

table_a has patient_id, tran_id and other fields a,b,c
table_b has patient_id,tran_id, key_id
and other fileds d,e,f
table_a patien_id + tran_id is unique
table_b patient_id + tran_id is not unique, could be duplicated.

I have to create a query which will retrieve fields from table a a,b,c and fields d,e from table b where
table a. patient_id + tran_id =
tableb.patient_id + tran_id and table_b.key_id is the min key_id for that patient_id + tran_id.

I should retrieve just one record.

How would I be able to do that?

Please help!!

Thanks

View 2 Replies View Related

SQL Complex Join

Aug 26, 2005

Hey all,
I have 3 tables: 1 table with Module information (1 quiz-like part of an Exam), 1 table with the relationships between Modules and Exams (ratios) and 1 table with User information, that contains the Exam number, Module number, and score. I am trying to get a full list of modules' names for 1 exam, and the user's score for those modules they taken. All this would pertain to 1 single Exam. I am only getting records returned for those modules that the user has taken; I want the full list with NULL values for the scores of modules the user has not taken. Hope that made sense, here is my attempt:


Code:


SELECT ExamModules.Name, ExamUsers.Score
FROM ExamToModule LEFT OUTER JOIN
ExamModules ON ExamToModule.ModuleID = ExamModules.ID LEFT OUTER JOIN
ExamUsers ON ExamToModule.ModuleID = ExamUsers.MID AND ExamToModule.ExamID = ExamUsers.EID
WHERE (ExamToModule.ExamID = 1) AND (ExamUsers.UserName = 'dizzle')



In this case the user is Dizzle and the Exam's ID (primary key) is 1. I've changed LEFT to RIGHT and FULL, they all return the same few records. Help?

View 4 Replies View Related

Complex Join, I Think.

Jun 27, 2007

I need a little help and I hope I can write a clear description of the problem. I have to tables, customers and custrate. The customer table simply has a custid of other misc info. The custrate contains an entry for each rate for any particular customer (1 customer to many rates). However the rates are suppossed to come in pairs. For example, if a customer has a rate id of 120 then the customer should also have a corresponding custrate record with a rate_id of 200. If the customer has a rate_id of 130, then the customer should have a corresponding custrate record containing rate_id 300, and so on and so forth. How do I find the customer id with are missing the corresponding rate? Here's an example or what I'm trying to explain.customerid | name1 | fred 2 | tom3 | eric 4 | fredcustratecust_id | rate_id1 | 1201 | 2002 | 1302 | 3003 | 1204 | 130I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!

View 8 Replies View Related

Complex Join

Apr 23, 2008

If i want to do another join with this select join query, how would I do this I tried many different way, but keep failing or not getting the right answer.

I want to Inner Join tbSucess with the query below. Means that only return the result if the history does exists in tbSucess.

And

In a seperate select query.
I, I want to get only the records which are not found in tbSuccess . So how would I join those two.





Select r.History, LastName, FirstName, Convert(varchar(10), Pay, 101) as Pay,
from #rpt r inner join #history h on r.History = h.History
order by r.History, pay

View 8 Replies View Related

Need Help On Complex Join Syntax ???

Mar 3, 2008

 Hi guys, I'm still new around here and still a noob for sql. Can you give me some example for some joins from the easy ones to the most complex and all of it kinds, I heard that there's a lot of different kind of join in sql. You can give me some link or maybe some code examples for me, I appreciate all kinds of help . Thanks for all your help. Regards. 

View 3 Replies View Related

Complex INNER JOIN Tables

Nov 3, 2004

I have a couple tables that I am exporting data from.

I have one table (call it TABLE1) that contains abbreviations instead of actual words.

For example instead of having the full word "New York City", the column would just contain "NYC".

In another table (call it TABLE2) it has all the abbreviations and the actual words.

So in TABLE2 one column name is FieldName, another Abbreviation and another is Value.

So on a web site, to display that actual name I do something like this


Code:


SELECT CityBoxlabel AS City
FROM TABLE1 INNER JOIN
TABLE 2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') = CityBox.[Value]
WHERE CityBox.FieldName = 'City'




This is working great except that some of these columns contain more than one abbreviation such as "NYC,WDC"
which would stand for "New York City" and "Washington DC"
The items that have more than one abbreviation are not being pulled across because my query is looking for an "NYC,WDC" in TABLE2 to INNER JOIN on but it won't be in there. But "NYC" and "WDC" by themselves are in there.

I tried messing with the INNER JOIN statement by saying something like
TABLE2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') IN CityBox.[Value] INNER JOIN

But it wasn't allowing that.
I can write a program to do this, but I would rather keep it in T-SQL.

Hope this wasn't too confusing!
Any help is greatly appreciated!!

View 5 Replies View Related

Join 2 Complex Queries To 1

Jul 20, 2005

hi thereanyone had an idea to join following 2 queries to 1????----- QUERY 1 ---------------------------------------------SELECT TMS_CaseF_2.Name AS TCDomain_0,TMS_CaseF_3.Name AS TCDomain_1,TMS.CaseF.Name AS TCFolder_2,TMS_CaseF_1.Name AS TCFolder_3,TMS.TestCase.Name AS TCName_4,TMS_TestCase_1.Name AS TCName_5,TMS.LogFolder.Name AS PlannedLogFolder_6,TMS.Log.Name AS PlannedLog_7,TMS.CaseResult.RecordedCaseName AS TCRecordedName_8,TMS.TestPlan.Name AS Plan_9FROM((((((((((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.Log)INNER JOIN TMS.TestCase ON TMS.CaseResult.TestCase =TMS.TestCase.UID)LEFT JOIN TMS.CaseF ON TMS.TestCase.Parent = TMS.CaseF.UID)LEFT JOIN TMS.TestCase AS TMS_TestCase_1 ON TMS.TestCase.Parent =TMS_TestCase_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_1 ON TMS_TestCase_1.Parent =TMS_CaseF_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_2 ON TMS_CaseF_1.Parent =TMS_CaseF_2.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_3 ON TMS.CaseF.Parent =TMS_CaseF_3.UID)INNER JOIN TMS.TestPlan ON TMS.TestCase.TestPlan = TMS.TestPlan.UID)WHERE (((TMS.LogFolder.Name) Like 'TR1%')AND ((TMS.Build.Name)='Planning_VD10A'))ORDER BY TMS.CaseF.Name,TMS_CaseF_1.Name,TMS.TestCase.Name,TMS_TestCase_1.Name;------------------------------------------------------------------ QUERY 2 ---------------------------------------------SELECT TMS.CaseResult.RecordedCaseNameFROM ((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.LogWHERE (((TMS.LogFolder.Name) Like 'VD%')AND ((TMS.Build.Name)='VD10A IT_APP'));

View 1 Replies View Related

Complex Conditional Join

Apr 27, 2008

Table A
Registation_key discharge_dt

1001 7/15/07
1002 6/4/07
1003 7/30/071



Table B
REgistration_key ID end_dt value
1001 879 07/11/07 1
1001 888 07/15/07 2
1002 777 06/01/07 5
1002 778 06/01/07 2
1002 779 06/30/07 6
1003 234 07/31/07 3
1003 234 07/31/07 1
1003 235 08/01/07 7


I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B

option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID

option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID

option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id



so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.


so the output should look like


Table B
Registration_key ID end_dt discharge_dt value

1001 888 07/15/07 7/15/07 2
1002 778 06/01/07 6/4/07 2

1003 234 07/31/07 8/1/07 7






How can I do this conditional join ?

View 6 Replies View Related

SQL Server 2008 :: Inner Join With Complex Condition

Mar 23, 2015

I have Two tables @master and @child

Master Table :

MasterID EntryNumber BranchId IsstockIn
1 1 1 1

2 1 1 0

Child Table:

CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4

My Query:

Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)

My Result:

EntryQty
10
20
-5
-4

Expected Result:

10
20

View 6 Replies View Related

Complex/annoying SELECT/JOIN Query

Feb 8, 2008

I have a very confusing/complicated query that I am trying to run and getting not the results that i want.

Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables.
When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.



Select t1.*, t3.field2, t3.field3
FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6

I suspect there is plenty wrong with this query - where should I start correcting it?

View 10 Replies View Related

Subject: How To Join A Table With Other (result) Tables ? Complex !

Aug 25, 2005

Here is the situation

Table 1 : tbl_documents

docIDdocName
1aaa
2bbb
3ccc

Table 2 : tbl_Rating

ratIDratingdocID
131
251
321
432

The queary I need is to display the result in this form. must be like this

docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0

NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”

PLs help me ?

Thx

View 3 Replies View Related

Complex SSIS Lookup/Merge Join Using NK And Dates

Feb 7, 2006

I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:

Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey

-- Brian

View 3 Replies View Related

Complex Join Replication Filtering Is Not Working In 3.5 Beta

Sep 11, 2007

Hi,

I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:

SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30
After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.

View 5 Replies View Related

Transact SQL :: Aggregate Function Causing Error In Complex Join

May 13, 2015

I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn. 

select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+

[Code] .....

I think I probable need to include a group by but can't figure out the correct syntax.

View 12 Replies View Related

Complex DB Search Forms (Store Proc Vs. Complex Where)

Nov 12, 2003

I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.

Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.

I build clauses like this (i.e., 4 fields shown):

SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )

My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.

View 7 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

Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation

May 22, 2015

I have two xml source and i need only left restricted data.

how can i perform left restricted join?

View 2 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

I was writing a query using both left outer join and inner join.  And the query was ....

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
        (Production.Products AS P
         INNER JOIN Production.Categories AS C

[code]....

However ,the result that i got was correct.But when i did  the same query using the left outer join in both the cases

i.e..

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';

The result i got was same,i.e

supplier     country    productid    productname     unitprice    categorynameSupplier QOVFD     Japan     9     Product AOZBW    97.00     Meat/PoultrySupplier QOVFD    Japan   10     Product YHXGE     31.00     SeafoodSupplier QOVFD     Japan   74     Product BKAZJ    10.00     ProduceSupplier QWUSF     Japan    13     Product POXFU     6.00     SeafoodSupplier QWUSF     Japan     14     Product PWCJB     23.25     ProduceSupplier QWUSF    Japan     15    Product KSZOI     15.50    CondimentsSupplier XYZ     Japan     NULL     NULL     NULL     NULLSupplier XYZ     Japan     NULL     NULL     NULL     NULL

and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.

View 5 Replies View Related

Warning - The Join Order Has Been Enforced Because A Local Join Hint Is Used

Dec 23, 2014

I have two select statements, in between select statement taking UNION ALL . I need to avoid the error

Warning: The join order has been enforced because a local join hint is used.

View 9 Replies View Related

'Left Outer Merge Join' Failing To Join Valid Row

Aug 10, 2007

Scenario:

OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC


OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC

These two tasks are followed immediately by a MERGE JOIN

All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)

result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...


result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...

All other rows are joining as expected.
Why is it failing for this one row?

View 1 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

Select Command - Left Join Versus Inner Join

Aug 9, 2013

Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?

View 4 Replies View Related

Merge Join (Full Outer Join) Never Finishes.

Jun 5, 2006

I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?

View 3 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

Page 2 - Within The INNER JOIN, How To Limit The Row To 1 Row Inside The INNER JOIN?

Apr 24, 2007

Awesome! I don't alway get the email notification of whoever reply to the posting. I think it only work after I log off of the forum.

Scott

View 2 Replies View Related

Changing From Implicit Join To Explicit Join

Dec 24, 2013

We are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.

Below is my Implict join query

INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow , #RIF_TEMP0 prevrow

[Code] ....

and below is explict join query

INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow
ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)

[Code] ....

the count returned from both the queries is different.

I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.

View 7 Replies View Related

Right Join Returns Same Results As Left Join

Feb 5, 2015

Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.

select Contact.firstname, Contact.lastname, Account.[Account Name]
from Contact
right join Account
on Contact.[Account Name] = Account.[Account Name]
where Contact.[Account Name] = Account.[Account Name]

View 3 Replies View Related

How To Join 3 Tables Using Left Or Right Join Keyword?

Aug 17, 2007

Hi guys,

I'll appreciate any help with the following problem:

I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.

1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02

2. Master table TBCODE contain 5 records:

rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5

3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR

rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR



I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.

Right Join method:


SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE

RIGHT JOIN TBLOC C ON A.LOC = C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE



When I use Non-ASNI method it work:



SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C

WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE

Result:

LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL


Please Help.

Thanks.






View 3 Replies View Related

Super Join - Is Merge Join The Answer?

Nov 7, 2006

Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.

This is the simple sql (no join on the tables):

select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2

But how to do this in SSIS?

Thanks - Ken

View 2 Replies View Related

How Would You Convert A Hash Join Into A Merge Join?

May 6, 2008

I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS

View 3 Replies View Related

Complex SQL (for Me)

Apr 26, 2006

Hello,

Lets look at this table :

CREATE TABLE [dbo].[TableHisto](
[Id] [int] NOT NULL,
[Week] [nvarchar](50) COLLATE French_CI_AS NULL,
[Project] [int] NOT NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifiant d''enregistrement' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date de l''enregistrement' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Week'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Projet de référence' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Project'


It is a table where i store projects week reports.

I want to make a request to display a table with project ID in Row, Weeks in columns and either TableHisto.id or Null value in cell.

I use SQL 2005. Thanks for any help

View 9 Replies View Related

Please Help With Complex (for Me) Sql Statement

Sep 3, 2006

I need some help on how to structure a sql statement. I am creating a membership directory and I need the stored procedure to output the Last Name, First Name (and if married) Spouse First Name. Like this Flinstone, Fred & Wilma All members are in one directory linked by two fields. [Family ID] all the family members have the same family id and then there is a Family position id that shows if they are the Husband, Wife or Kids. I have no problem with this part select (LastName + ',' + FirstName) as Name, [Phone 1] as Phone, [Unit Name] as WD, [Street 1] as Street, SUBSTRING(City,1,3) as City, SUBSTRING(Postal,1,5) as Zipfrom Membership Where [HH Order]=1 Order By LastName ASC Could someone help me on how to display the " & Spouse FirstName " as part of the name field only if there is a spouse [HH Order]=2 for the current [Family ID]????

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved