Help Needed With A Join

Apr 8, 2008

SELECT * FROM A INNER JOIN
B ON A.AId =
(SELECT B.BId
FROM B
WHERE (B.AId = 4))

I have two tables
Table A has several columns and B has just two columns
B.AId and B.BId.

I will pass B.AId as a parameter and that will return more than one B.BId values. Then I need to create a join where A.AId matches B.BId.

I tried the above SQL but it says subquery can't return more than one value. In my case it must return more than on value.
How can I construct the SQL query.
Please Help.
Thanks

View 3 Replies


ADVERTISEMENT

Inner Join And Union Help Needed

Dec 7, 2006

Ok, getting a little better at joins, but now need to take it two more steps.Main table tblEquipmentsFields: ID, EquipmentType, Location, StatusSecond Table tblEquipments_GroupFields: equipmentID, categoryID, subCategoryID (all are ints)I joined these two together and got the info back I needed:SELECT a.ID, a.Description, a.Status, a.Location, from tblEquipments ainner join tblEquipments_Group U on u.categoryID = a.EquipmentTypeand u.subCategoryID = @type Now, the a.status returns a number. What the status actually is is in tblStatus.Fields: statusID (int) , Status (varchar).So, the statusID matches a.Status.I need to get the text from tblStatus in the field Status and replace the a.Status (which is a number still) with the text.The other one, location, is the same situation.I've been trying to do another join and am at an end on this.Suggestions?Thanks!!Zath

View 2 Replies View Related

What Type Of Join Needed?

Feb 22, 2008

I need to find all of the records Table A with ID values that are not found in Table B.  What type of join do I need? Is there such a join?
Thank you

View 4 Replies View Related

Left Join Help Needed!

Dec 27, 2007

I’m attempting to use a Left Join to pull information from a table that contains my objectives and place in with information from a Summary table. I use one where clause per Left Join, that defines which rows from the objective table I want that left join to represent (for instance “(mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL )� ). I have 7 left joins and where statements.

The problem I’m having is that the where clause are removing all rows where the Left Join does not result in a match, despite the fact that I have the OR IS NULL in the where clause.

Can anyone help!

The full SQL is as follows:


SELECT DATE_DAY
, SEVERITY
, ENTRY_TYPE
, EQ_REGION
, EQ_MARKET_CLUSTER
, FORCE_STATUS
, NUM_CREATED
, NUM_OPEN
, NUM_AWAITING_DISPATCH
, NUM_OPEN_IN_DUE_DATE
, NUM_DISPATCH_TO
, DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO_G_L
, MET_SLA_DISPATCH_TO
, mdt.SLA AS SLA_MET_DISPATCH_TO
, mdt.SLA_G_L AS SLA_MET_DISPATCH_TO_G_L
, C_NUM_DISPATCH_TO
, C_DURATION_DISPATCH_TO
, C_SLA_DURATION_DISPATCH_TO
, C_SLA_DURATION_DISPATCH_TO_G_L
, C_MET_SLA_DISPATCH_TO
, mdt.SLA AS C_SLA_MET_DISPATCH_TO
, mdt.SLA_G_L AS C_SLA_MET_DISPATCH_TO_G_L
, NUM_DISPATCH_FROM
, DURATION_CREATE_TO_DISPATCH_FROM
, NUM_DISPATCH_TO_TO_FROM
, DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM_G_L
, MET_SLA_DISPATCH_FROM
, mdf.SLA AS SLA_MET_DISPATCH_FROM
, mdf.SLA_G_L AS SLA_MET_DISPATCH_FROM_G_L
, C_NUM_DISPATCH_FROM
, C_DURATION_CREATE_TO_DISPATCH_FROM
, C_NUM_DISPATCH_TO_TO_FROM
, C_DURATION_DISPATCH_FROM
, C_SLA_DURATION_DISPATCH_FROM
, C_SLA_DURATION_DISPATCH_FROM_G_L
, C_MET_SLA_DISPATCH_FROM
, mdf.SLA AS C_SLA_MET_DISPATCH_FROM
, mdf.SLA_G_L AS C_SLA_MET_DISPATCH_FROM_G_L
, NUM_CLOSED
, DURATION_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE_G_L
, MET_SLA_WIP_TO_RESOLVE
, wip.SLA AS SLA_MET_WIP_TO_RESOLVE
, wip.SLA_G_L AS SLA_MET_WIP_TO_RESOLVE_G_L
, DURATION_MTTR
, SLA_MTTR
, SLA_MTTR_G_L
, MET_SLA_MTTR
, mttr.SLA AS SLA_MET_MTTR
, mttr.SLA_G_L AS SLA_MET_MTTR_G_L
, NUM_RESOLVE_FD
, rfd.SLA AS SLA_RESOLVE_FD
, rfd.SLA_G_L AS SLA_RESOLVE_FD_G_L
, NUM_DISPATCH_NTF
, ntf.SLA AS SLA_DISPATCH_NTF
, ntf.SLA_G_L AS SLA_DISPATCH_NTF
, AR_NUM_MET_DUE_DATE
, ar.SLA AS AR_SLA_MET_DUE_DATE
, ar.SLA_G_L AS AR_SLA_MET_DUE_DATE
FROM (
SELECT DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END AS SEVERITY
, ENTRY_TYPE
, EQ_REGION
, EQ_MARKET_CLUSTER
, CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END AS FORCE_STATUS
, sum( NUM_CREATED ) AS NUM_CREATED
, sum( NUM_OPEN ) AS NUM_OPEN
, sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH
, sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE
, sum( NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO
, sum( DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO
, max( SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO
, max( SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L
, sum( MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO
, sum( C_NUM_DISPATCH_TO ) AS C_NUM_DISPATCH_TO
, sum( C_DURATION_DISPATCH_TO ) AS C_DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO ) AS C_SLA_DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS C_SLA_DURATION_DISPATCH_TO_G_L
, sum( C_MET_SLA_DISPATCH_TO ) AS C_MET_SLA_DISPATCH_TO
, sum( NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM
, sum( DURATION_CREATE_TO_DISPATCH_FROM ) AS DURATION_CREATE_TO_DISPATCH_FROM
, sum( NUM_DISPATCH_TO_TO_FROM ) AS NUM_DISPATCH_TO_TO_FROM
, sum( DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM
, max( SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM
, max( SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L
, sum( MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM
, sum( C_NUM_DISPATCH_FROM ) AS C_NUM_DISPATCH_FROM
, sum( C_DURATION_CREATE_TO_DISPATCH_FROM ) AS C_DURATION_CREATE_TO_DISPATCH_FROM
, sum( C_NUM_DISPATCH_TO_TO_FROM ) AS C_NUM_DISPATCH_TO_TO_FROM
, sum( C_DURATION_DISPATCH_FROM ) AS C_DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM ) AS C_SLA_DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS C_SLA_DURATION_DISPATCH_FROM_G_L
, sum( C_MET_SLA_DISPATCH_FROM ) AS C_MET_SLA_DISPATCH_FROM
, sum( NUM_CLOSED ) AS NUM_CLOSED
, sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L
, sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE
, sum( DURATION_MTTR ) AS DURATION_MTTR
, max( SLA_MTTR ) AS SLA_MTTR
, max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L
, sum( MET_SLA_MTTR ) AS MET_SLA_MTTR
, sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD
, sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF
, sum( AR_NUM_MET_DUE_DATE ) AS AR_NUM_MET_DUE_DATE
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-30,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND upper(EQ_REGION) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND upper(EQ_MARKET_CLUSTER) IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, ENTRY_TYPE
, EQ_REGION
, EQ_MARKET_CLUSTER
, CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END-- AS FORCE_STATUS
) t
LEFT JOIN CTS.dbo.NDC_SLAs mdt ON t.SEVERITY=mdt.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs mdf ON t.SEVERITY=mdf.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs wip ON t.SEVERITY=wip.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs mttr ON t.SEVERITY=mttr.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs rfd ON t.SEVERITY=rfd.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs ntf ON t.SEVERITY=ntf.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs ar ON t.SEVERITY=ar.SLA_SEVERITY
WHERE (mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL )
AND (mdf.SLA_NAME = 'Duration NDC to Field % Met SLA' OR mdf.SLA_NAME IS NULL )
AND (wip.SLA_NAME = 'Duration Dispatch Out % Met SLA' OR wip.SLA_NAME IS NULL )
AND (mttr.SLA_NAME = 'Total MTTR % Met SLA' OR mttr.SLA_NAME IS NULL )
AND (rfd.SLA_NAME = '% Resolved 1st Dispatch' OR rfd.SLA_NAME IS NULL )
AND (ntf.SLA_NAME = '% Dispatched NTF' OR ntf.SLA_NAME IS NULL )
AND (ar.SLA_NAME = '% Met Due Date' OR ar.SLA_NAME IS NULL )

View 1 Replies View Related

Help Needed In Join Query

Mar 25, 2008

hi,

this is my db tables:

groupallocation
groupid(primarykey)
groupname

systemallocation

systemid(primarykey)
systemname

deviceallocation

deviceid(primarykey)
devicename
sensorallocation

sensorid(primarykey)
sensorname
groupid(foreignkey)
systemid(foreignkey)
deviceid(foreignkey)

so i want to make a query in sensorallocation table should display as like folllows:

i want to make query to join the tables and display the following fields.

sensorid,sensorname,groupname,systemname,devicename

becos in sensorallocation table i do have only groupid,systemid,deviceid only but while displaying i need to display groupname,systemname,devicename

View 3 Replies View Related

Query Help Needed (JOIN)

Jul 20, 2005

I have two tables populated during the use of an application to loguser events and application states. They are named "EventTable" and"StateTable" and the structures follow:EventTable:ID EventTimeStep EventID-- ------------- ---------1 5 E12 22 E23 56 E3StateTableID StateTimeStep StateID-- ------------- -------1 1 S12 39 S2I want to perform a query that reports the StateID of the applicationat the time that each event was logged to the EventTable. The desiredoutput is:ID TimeStep EventID StateID-- -------- ------- -------1 5 E1 S12 22 E2 S13 56 E3 S2I have tried to create a query with an INNER JOIN where the value forthe StateID output field comes from the last row in the StateTableWHERE StateTable.TimeStep <= EventTable.TimeStep and where I use aGROUP_BY EventTable.ID to merge the following rows from the join:3 56 E3 S13 56 E3 S2However, the closest I can get is a query that gives me the wrongstate when applying the GROUP BY clause3 56 E3 S1I also think that the queries I have written is slow and inefficient.Is there a better way to perform this query or is my database designfatally flawed?Thanks,Adam Nemitoff

View 2 Replies View Related

Help Needed With Merge Join

Jan 10, 2008

I am trying to use the merge join example in the following link. To import new records only.

http://www.sqlis.com/311.aspx

The problem is that for some unkown reason the join is not woeking correctly. One of the records is incorrectly showing a NULL on the output. This would indicate that it would be a new record, but it is not it already exists in the new table.

I created a dummy table in SQL and executed the same join and I always get the right answer. What the heck could be wrong?

For example. Table A has 20 records Table B has 3 records. Table B has the new records I want to import into Table A. The package runs corectly the first time, only importing the 3 new records. Then the next time the package runs it shows 1 of the 3 records as being new still, and tries to import the record causing a PK error. Adding a watch to the MERGE output shows that the one record has a NULL on the join.

Please help this is driving me nuts.

View 10 Replies View Related

SQL Query JOIN Needed? Too Advanced For Me

Sep 5, 2006

THIS LOOKS LIKE A LOT BUT I JUST WANTED TO GIVE ALL INFORMATION NEEDED FOR YOU TO EASILY ASSIST ME.  I am not good with SQL so please bare with me. I will give a dumbed down table for us to work with:timestamp          -   DATA1 - DATA2 - MAXVALUENEEDED - DATA32006/8/12 12:00       123         125             1289                      121 2006/8/12 12:05       124         128             1291                      1232006/8/15 12:00        130        128              1190                     1242006/8/15 1:00          131        127             1196                      123  The columns labeled DATA do not really matter.  The only important ones are timestamp and MAXVALUENEEDED but I do need the query to return all columns. What the query has to do: The query needs to find the MAX of MAXVALUENEEDED for a given DAY.  Example Output- 2006/8/12      124   128   1291   1232006/8/15      131   127   1196   123As you can see the MAX values were grabbed for each given day and the unique records were returned on a per day basis. My current SQL Query is:  SELECT CONVERT(varchar, timestamp, 101) AS Time, CONVERT(int,LV50AEPM_AMPS_A_VAL0) AS AMPS_A, CONVERT(int,LV50AEPM_AMPS_B_VAL0) AS AMPS_B, CONVERT(int,LV50AEPM_AMPS_C_VAL0) AS AMPS_C,
CONVERT(int,LV50AEPM_KVA_TOT_VAL0) AS KVA_TOTAL, CONVERT(int,LV50AEPM_KW_TOT_VAL0), CONVERT(int,LV50AEPM_VLL_CA_VAL0), CONVERT(int,LV50AEPM_VLL_BC_VAL0), CONVERT(int,LV50AEPM_VLL_AB_VAL0)
FROM Table_1 T1
WHERE CONVERT(int,T1.LV50AEPM_KVA_TOT_VAL0) = (SELECT MAX(CONVERT(int,T2.LV50AEPM_KVA_TOT_VAL0)) FROM Table_1 T2 WHERE CONVERT(varchar,T1.timestamp,101) = T2.timestamp)
ORDER BY Time ASC The problem is it only returns the max value for one day instead of all days.  Can anyone help me correct this?  Thanks in advance for your help. 

View 34 Replies View Related

Related Tables: Help Needed With JOIN Query

Oct 16, 2006

Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Transact SQL :: Additional Restriction On Inner Join Needed

Sep 28, 2015

I have two employee tables called EmpA and EmpB.Each table has the same attributes of Employee ID and Email address.I do an inner join on email address like this:

select * from EmpA
inner join EmpB on EmpA.email = EmpB.email
where EmpB like '%@mydomain.com'

I now want to modify the above where I want to output rows such that

 EmpA.employeeid <> EmpB.employeeid

View 9 Replies View Related

Problem With SQL Server Left Outer Join: Help Needed

Nov 1, 2004

I am getting different results with LEFT outer join operator and *= operator. With *= I am getting the expected results. Can anyone look at SQL and tell what I am doing wrong?

SQL with Left Outer join operator:

select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat LEFT JOIN CurrentWeekFinMetrics on (WeeklyMetricsFormat.Line# = CurrentWeekFinMetrics.Line#)
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
order by CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.Line#


SQL with *= operator
select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat , CurrentWeekFinMetrics
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
AND (WeeklyMetricsFormat.Line# *= CurrentWeekFinMetrics.Line#)

For Left outer join operator, I am getting 54 rows, *= I am getting 69 rows.

Thanks and Regards
mb

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

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

Apr 17, 2007

There is a table called "tblvZipCodes" that contain a zipcode of all cities, area code that are located in that zip code.

The problem I have with the inner join is that there are more than 1 cities in one zipcode code. Is there a way to just return only the 1st row and not return the rest of the rows from the tblvZipCodes in the INNER JOIN query?

Thanks..


Code:

SELECT TOP 100 PERCENT dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model, dbo.tblPurchaseRaw.ModelType,
dbo.tblPurchaseRaw.Color, dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.County, dbo.tblvZipCodes.State,
dbo.tblvZipCodes.AreaCode, dbo.tblvZipCodes.Region, dbo.tblaAccounts.Name, dbo.tblaAccounts.PhoneOne, dbo.tblaAccounts.AccountID,
dbo.tblPurchaseRaw.AcceptedID, dbo.tblPurchaseRaw.Series, dbo.tblPurchaseRaw.BodyStyle, dbo.tblaAccounts.WebSite,
dbo.tblaAccounts.SalesEmail, dbo.tblPurchaseRaw.EmailTo, dbo.tblPurchaseRaw.PhotoURL, dbo.tblPurchaseRaw.Mileage,
dbo.tblPurchaseRaw.RawID, dbo.tblvRegions.Name AS RegionName, dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.Style,
dbo.tblPurchaseRaw.StockDate
FROM dbo.tblPurchaseRaw INNER JOIN
dbo.tblaAccounts ON dbo.tblPurchaseRaw.AccountID = dbo.tblaAccounts.AccountID INNER JOIN
dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = dbo.tblvZipCodes.ZIPCode INNER JOIN
dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
WHERE (CONVERT(char, dbo.tblPurchaseRaw.StockDate, 101) <> '01/01/1900') AND (dbo.tblPurchaseRaw.SoldRawID IS NULL) AND
(dbo.tblPurchaseRaw.AcceptedID <> - 10) AND (dbo.tblPurchaseRaw.AcceptedID <> - 1)
ORDER BY dbo.tblvZipCodes.ZIPCode

View 14 Replies View Related

Inner Join To Outer Join Problem

Mar 1, 2008

hello, i am running mysql server 5 and i have sql syntax like this:
select
sales.customerid as cid,
name,
count(saleid)
from
sales
inner join
customers
on
customers.customerid=sales.customerid
group by
sales.customerid
order by
sales.customerid;
it works fine and speedy. but when i change inner join to right join, in order to get all customers even there is no sale, my server locks up. note: there is about 10000 customers and 15000 sales.
what can be the problem?
thanks,

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

Use ANSI Join Or Old Style Join?

May 20, 2006

Hi,Just curious. Would you use ANSI style table joining or the 'oldfashion' table joining; especially if performance is the main concern?What I meant is illustrated below:ANSI Styleselect * from a join b on a.id = b.idOld Styleselect * from a, b where a.id = b.idI noticed that in some SQL, the ANSI is much faster but sometimes, theold style looks much better.It's ridiculous to try out both styles to see which is better wheneverwe want to write an SQL statement.Please comment.Thanks in advance.

View 1 Replies View Related

Performance Between Standard Join And Inner Join

Jun 28, 2007

Hello, everyoneI have one question about the standard join and inner join, which oneis faster and more reliable? Can you recommend me to use? Please,explain me...ThanksChamnap

View 12 Replies View Related

Self Join Outer Join Question

Oct 10, 2007

Given a table of building components e.g. floors, walls, etc, etc:

create table component_multiplier_table
(

system_code char(4),
system_component_code char(3),
function_code char(4),
component_multiplier dec(7,6)
)

Where function_code is the function of the area e.g. Auditorium, Classrom, etc, etc. And not all components are available for all functions e.g. Carpeting is available for Classrooms but not Power Plants or Warehouses.

I need to self join the above table to itself on system_code and system_component_code and find out which rows are missing from each side.

A query that I've been banging away at with no success is:

SELECT c1.*, c2.*
FROM [dbo].[component_multiplier_table] c1 FULL OUTER JOIN [dbo].[component_multiplier_table] c2
ON (c1.system_component_code = c2.system_component_code) AND (c1.[system_code] = c2.[system_code])
WHERE c1.function_code = '2120' AND c2.[function_code] = '2750' AND (c1.[system_code] IS NULL OR c2.system_code IS NULL);

I added the is null conditions, no joy. I've tried every flavor of outer join w/o success.

Could any T-SQL gurus out there help me figure out how to do this in a set before I start coding

DECLARE crsr CURSOR

Thanks.

View 7 Replies View Related

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans

Apr 16, 2008



Anyone know why using

SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of

SELECT *
FROM a LEFT JOIN b
ON a.id = b.id

generates a different execution plan?

My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".

Any enlightenment is very appreciated.

Thanks

View 5 Replies View Related







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