What Type Of Join Needed?
Feb 22, 2008I 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
ADVERTISEMENT
Help Needed With This Type Of Min/Max Queries
Sep 7, 2007priceStockID productID supplierID price
1 1 1 100
2 1 2 110
3 2 1 10
4 2 3 20
5 3 1 30
6 3 2 20
7 3 3 15
8 4 1 40
how can I get the priceStockID for the lowest price of each product in the most efficient way
the result table should be like below
priceStockID productID supplierID price
1 1 1 100
3 2 1 10
7 3 3 15
8 4 1 40
Help Needed With A Join
Apr 8, 2008SELECT * 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
Inner Join And Union Help Needed
Dec 7, 2006Ok, 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 RelatedLeft Join Help Needed!
Dec 27, 2007I’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 )
Help Needed In Join Query
Mar 25, 2008hi,
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
Query Help Needed (JOIN)
Jul 20, 2005I 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 RelatedHelp Needed With Merge Join
Jan 10, 2008I 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.
Help Needed For Identifying DML Trigger Event Type
Jun 19, 2008Hi,
I have a query regarding DML Triggers.
I have created a trigger ON a table AFTER Insert, Update staement. In the trigger's body I am making a call to a stored procedure. In the procedure, I want to know the Trigger invoked due to which event : insert or Update as I have to handle bith the cases differently.
But I am not able to find out any SQL server function or property which can distinguish between INSERT and Update events of a trigger.
Waiting for the needful.
Help Needed For Identifying DML Trigger Event Type
Jun 19, 2008Hi,
I have a query regarding DML Triggers.
I have created a trigger ON a table AFTER Insert, Update staement. In the trigger's body I am making a call to a stored procedure. In the procedure, I want to know the Trigger invoked due to which event : insert or Update as I have to handle bith the cases differently.
But I am not able to find out any SQL server function or property which can distinguish between INSERT and Update events of a trigger.
Waiting for the needful.
SQL Query JOIN Needed? Too Advanced For Me
Sep 5, 2006THIS 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.
What Type Of Permission Needed To Call ListJobs() Function
Apr 30, 2007I'm working on Application that requires me to check and display status of reports running on report server. My application calling ListJobs() function of Job class part of Reporting Services Web Service API. When i run my application i'm getting insufficient previleges error. So i need to find out what type of permission i need to excute ListJobs().
This is very important part of my app. Please help me out.
Thanks,
Viral Patel
Related Tables: Help Needed With JOIN Query
Oct 16, 2006Hi 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 RelatedTransact SQL :: Additional Restriction On Inner Join Needed
Sep 28, 2015I 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
Urgent Help Needed: How To INSERT Into A Field Having UniqueIdentifier Data Type
Jan 8, 2008I am new at ASP.net and I am having problems inserting data using C# in ASP.netI have created a table named "Profile" in the MS sql server database named "MyDataBase". There is a field named "ID" that has data type 'uniqueidentifier'.I am confused how to INSERT data into this data field. I have used MS Access and MYSQL in which there is an option of auto increment so we don't a unique identifier for each record.Please tell me what can I do to If I want to have a uniqueidentifier for each new record I INSERT in the "Profile" table of MS sql server database.While trying to insert, I get following errorsCannot insert the value NULL into column 'ID'and I don't know how I can insert something in this field that is of value type unique identifier.Please help me I will be very thankfull of you.
View 5 Replies View RelatedProblem With SQL Server Left Outer Join: Help Needed
Nov 1, 2004I 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
Type Of Join ?
Jan 9, 2008I have a table tblRooms and a table tblBookings. They are joined by RoomRef. There may or may not be a record on tblBookings for a room. I want my view to list all the rooms and show a blank line if there is no entry in tblBookings for that room. I've tried changing the join to lis all records from tbl rooms but it doesn't work.
SELECT *
FROM dbo.tblRB_Bookings RIGHT OUTER JOIN
dbo.tblRB_Rooms ON dbo.tblRB_Bookings.BK_RoomRef = dbo.tblRB_Rooms.RM_RoomRef
WHERE (dbo.tblRB_Bookings.BK_DateRequired = CONVERT(DATETIME, '2008-01-10 00:00:00', 102))
eg
Room1 Booking 1.00 to 2.00
Room2
Room3 Booking 11.30 to 12.30
Room3 Booking 3.00 to 4.30
Room4
Room5 Booking 10.00 to 3.00
Plenty Of Records To Choose, What Type Of JOIN?
Aug 9, 2007I have 100k + records in table usr_table and I want to select all of them that do not have the same ID as the ID's indicated in table Usr_Type_Data
Select * From usr_table As t1 Join Company_Sales.dbo.Usr_Type_Data As t2 ON t2.user_id = t1.user_id Where CustomerTypeId <> 7
I get 0 returned.
If I change it to:
Select * From usr_table As t1 Join Company_Sales.dbo.Usr_Type_Data As t2 ON t2.user_id = t1.user_id Where CustomerTypeId = 7
I get all records from table Usr_Data_Type returned.
Do I need to specify a special Join type?
Or perhaps change the T-SQL around?
SQL Server 2008 :: Specific Type Of Join For 3 Tables
Jul 3, 2015I have 3 tables as per following:
orddet
OrderProductQtyOrd
1 Item1 20
2 Item1 10
3 Item2 10
4 Item1 5
4 Item2 5
ordhead
OrderDate
110/06/2015
205/07/2015
307/06/2015
415/08/2015
product
ProductdescMinQty
Item1This is 110
Item2This is 220
I want to pull only the 1 line for minqty for an item as follows
OrderProductQtyOrddate minqty
1 Item1 20 10/06/2015 10
2 Item1 10 05/07/2015
3 Item2 10 07/06/2015 20
4 Item1 5 15/08/2015
4 Item2 5 15/08/2015
Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language
Apr 30, 2008Hello
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
Incorrect Syntax Near 'm'. An Expression Of Non-boolean Type Specified In A Context Where A Condition Is Expected, Near 'type'
Jun 3, 2008This is nutty. I never got this error on my local machine. The only lower case m in the sql is by near the variable Ratingsum like in line 59.
[SqlException (0x80131904): Incorrect syntax near 'm'.An expression of non-boolean type specified in a context where a condition is expected, near 'type'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +269 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 view_full_article.btnRating_Click(Object Src, EventArgs E) +565 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746</pre></code>
Here is my button click sub in its entirety:
1 Sub btnRating_Click(ByVal Src As Object, ByVal E As EventArgs)
2 'Variable declarations...
3 Dim articleid As Integer
4 articleid = Request.QueryString("aid")
5 Dim strSelectQuery, strInsertQuery As String
6 Dim strCon As String
7 Dim conMyConnection As New System.Data.SqlClient.SqlConnection()
8 Dim cmdMyCommand As New System.Data.SqlClient.SqlCommand()
9 Dim dtrMyDataReader As System.Data.SqlClient.SqlDataReader
10 Dim MyHttpAppObject As System.Web.HttpContext = _
11 System.Web.HttpContext.Current
12 Dim strRemoteAddress As String
13 Dim intSelectedRating, intCount As Integer
14 Dim Ratingvalues As Decimal
15 Dim Ratingnums As Decimal
16 Dim Stars As Decimal
17 Dim Comments As String
18 Dim active As Boolean = False
19 Me.lblRating.Text = ""
20 'Get the user's ip address and cast its type to string...
21 strRemoteAddress = CStr(MyHttpAppObject.Request.UserHostAddress)
22 'Build the query string. This time check to see if IP address has already rated this ID.
23 strSelectQuery = "SELECT COUNT(*) As RatingCount "
24 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
25 strSelectQuery += " AND ip = '" & strRemoteAddress & "'"
26 'Open the connection, and execute the query...
27 strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString
28 conMyConnection.ConnectionString = strCon
29 conMyConnection.Open()
30 cmdMyCommand.Connection = conMyConnection
31 cmdMyCommand.CommandType = System.Data.CommandType.Text
32 cmdMyCommand.CommandText = strSelectQuery
33 intCount = cmdMyCommand.ExecuteScalar()
34 intSelectedRating = Int(Me.rbRating.Text)
35 conMyConnection.Close()
36 'Close the connection to release these resources...
37
38 If intCount = 0 Then 'The user hasn't rated the article
39 'before, so perform the insert...
40 strInsertQuery = "INSERT INTO tblArticleRating (rating, ip, itemID, comment, active) "
41 strInsertQuery += "VALUES ("
42 strInsertQuery += intSelectedRating & ", '"
43 strInsertQuery += strRemoteAddress & "', "
44 strInsertQuery += articleid & ", '"
45 strInsertQuery += comment.Text & "', '"
46 strInsertQuery += active & "'); "
47 cmdMyCommand.CommandText = strInsertQuery
48 conMyConnection.Open()
49 cmdMyCommand.ExecuteNonQuery()
50 conMyConnection.Close()
51 Me.lblRating.Text = "Thanks for your vote!"
52 Comments = comment.Text.ToString
53
54 If Len(Comments) > 0 Then
55 emailadmin(comment.Text, articleid)
56 End If
57 'now update the article db for the two values but first get the correct ratings for the article
58 strSelectQuery = _
59 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
60 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
61 conMyConnection.Open()
62 cmdMyCommand.CommandText = strSelectQuery
63 dtrMyDataReader = cmdMyCommand.ExecuteReader()
64 dtrMyDataReader.Read()
65 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString)
66 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString)
67 Stars = Ratingvalues / Ratingnums
68 conMyConnection.Close()
69 'Response.Write("Values: " & Ratingvalues)
70 'Response.Write("Votes: " & Ratingnums)
71
72 UpdateRating(articleid, Stars, Ratingnums)
73 Else 'The user has rated the article before, so display a message...
74 Me.lblRating.Text = "You've already rated this article"
75 End If
76 strSelectQuery = _
77 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
78 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
79 conMyConnection.Open()
80 cmdMyCommand.CommandText = strSelectQuery
81 dtrMyDataReader = cmdMyCommand.ExecuteReader()
82 dtrMyDataReader.Read()
83 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString)
84 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString)
85 Stars = Ratingvalues / Ratingnums
86 If (Ratingnums = 1) And (Stars <= 1) Then
87 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote"
88 ElseIf (Ratingnums = 1) And (Stars > 1) Then
89 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote"
90 ElseIf (Ratingnums > 1) And (Stars <= 1) Then
91 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes"
92 ElseIf (Ratingnums > 1) And (Stars > 1) Then
93 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes"
94 End If
95
96 'Response.Write(String.Format("{0:f2}", Stars))
97 'Response.Write("Values: " & Ratingvalues)
98 'Response.Write("Votes: " & Ratingnums)
99 If (Stars > 0) And (Stars <= 0.5) Then
100 Me.Rating.ImageUrl ="./images/rating/05star.gif"
101 ElseIf (Stars > 0.5) And (Stars < 1.0) Then
102 Me.Rating.ImageUrl = "./images/rating/05star.gif"
103 ElseIf (Stars >= 1.0) And (Stars < 1.5) Then
104 Me.Rating.ImageUrl = "./images/rating/1star.gif"
105 ElseIf (Stars >= 1.5) And (Stars < 2.0) Then
106 Me.Rating.ImageUrl = "./images/rating/15star.gif"
107 ElseIf (Stars >= 2.0) And (Stars < 2.5) Then
108 Me.Rating.ImageUrl = "./images/rating/2star.gif"
109 ElseIf (Stars >= 2.5) And (Stars < 3.0) Then
110 Me.Rating.ImageUrl = "./images/rating/25star.gif"
111 ElseIf (Stars >= 3.0) And (Stars < 3.5) Then
112 Me.Rating.ImageUrl = "./images/rating/3star.gif"
113 ElseIf (Stars >= 3.5) And (Stars < 4.0) Then
114 Me.Rating.ImageUrl = "./images/rating/35star.gif"
115 ElseIf (Stars >= 4.0) And (Stars < 4.5) Then
116 Me.Rating.ImageUrl = "./images/rating/4star.gif"
117 ElseIf (Stars >= 4.5) And (Stars < 5.0) Then
118 Me.Rating.ImageUrl = "./images/rating/45star.gif"
119 ElseIf (Stars >= 4.5) And (Stars <= 5.0) Then
120 Me.Rating.ImageUrl = "./images/rating/5star.gif"
121 End If
122 dtrMyDataReader.Close()
123 conMyConnection.Close()
124 End Sub
If you want to reduplicate the error, click over here and try to submit a rating:
http://www.link-exchangers.com/view_full_article.aspx?aid=51
Thanks for helping me figure this out.
Float Type Steals My Decimal Points And Money Type Kills My Query
Mar 28, 2008Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type
Dec 14, 2007I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?
Sqlbulkcopy Error : The Given Value Of Type SqlDecimal From The Data Source Cannot Be Converted To Type Decimal Of The Specified
Apr 16, 2008Hi,
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability
we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected
we need to store values as is
Error:
base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range.
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
at MS.Internal.MS
COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default);
DataRow dr;
DataTable dt = new DataTable();
DataColumn dc;
try
{
dc = dt.Columns.Add("Availability", typeof(decimal));
€¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal
€¦€¦€¦.
}
bulkCopy.DestinationTableName = "dbo.[Tbl_Fact_App_Server_AvailPerMinute]";
bulkCopy.WriteToServer(dt);
thx
Implement Time Interval Type In Form Of User Defined Type
Dec 7, 2011Implement time interval type in the form of a user defined type in SS2k8r2? Specifically an interval type described in the book Temporal Data and the Relational Model by C. J. Date at all. As an example, an interval is below:
1/4/2006:1/10/2006
which would mean the time period from 1/4 to 1/10.
Bulk Insert Task Failing On Data Type Conversion For A Destination Column Of Type Bit
Jul 6, 2006I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package3.dtsx" finished: Failure.
Msg 6522, Level 16, State 2, Line 1: System.InvalidCastException: Conversion From Type 'SqlBoolean' To Type 'Boolean' Is Not Val
Mar 6, 2008I created a function called Temperature in VB to be used as a UDF in SQL2005. I get the error listed below. Any thoughts?
CREATE FUNCTION Temperature(@FluidName SQL_variant, @InpCode SQL_variant, @Units SQL_variant, @Prop1 SQL_variant, @Prop2 SQL_variant)
RETURNS Float
AS EXTERNAL NAME Fluids_VB6.[Fluids_VB6.FluidProperties.Fluids].Temperature
Then ran function:
select dbo.temperature('R22','t','e','225.6','0')
Got this:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'Temperature':
System.InvalidCastException: Conversion from type 'SqlBoolean' to type 'Boolean' is not valid.
System.InvalidCastException:
at Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(Object Value)
at Fluids_VB6.FluidProperties.Fluids.Setup(Object& FluidName)
at Fluids_VB6.FluidProperties.Fluids.CalcSetup(Object& FluidName, Object& InpCode, Object& Units, Object& Prop1, Object& Prop2)
at Fluids_VB6.FluidProperties.Fluids.CalcProp(Object& FluidName, Object& InpCode, Object& Units, Object& Prop1, Object& Prop2)
at Fluids_VB6.FluidProperties.Fluids.Temperature(Object FluidName, Object InpCode, Object Units, Object Prop1, Object Prop2)
Thanks
Buck
Conversion From Type 'SqlInt32' To Type 'Integer' Is Not Valid]
Jun 30, 2007Hi all,
I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine.
The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings.
Any suggestions.
Best Regards
Wafi Mohtaseb
Conversion From Type 'DBNull' To Type 'String' Is Not Valid
Mar 7, 2008Hello Friends
How are you?? Friends i am getting problem in SQL Server 2005. I am deployng web application on production server as well as Databse also. In production server i inserted new field in all tables which is rowguid and its type is uniqueidentifier. The default binding for this field is newsequentialid(). In some pages it works ok but in some places it generates error like 'Conversion from type 'DBNull' to type 'String' is not valid'. Can anybody help me to solve this problem. Its urgent so plz reply me as soon as possible. I'll be very thankfull to you. Thanks in Advance.
Regards,
I Really Need A Debate! Type - Attributes Vs Super Type - Sub Types
Apr 22, 2004I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!
Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:
tbAutombbileCategories
CategoryID | Category
-------------------------------
1 | Car
2 | Truck
3 | Motorcycle
tbAutomobileAttributes
AttributeID | fkCategoryID | Attribute
-------------------------------------------
1 | 1 (car) | Doors
2 | 2 (truck) | Cab
3 | 2 (truck) | Capacity
tbAutomobile
VIN | Category | Make | Model
-------------------------------------
1 | 1 | Honda | Accord
2 | 2 | Ford | F150
tbAutomobileAttributeValues
fkVIN | fkAttributeID | Value
---------------------------------
1 | 1 | 2
2 | 1 | 0
2 | 2 | 1000
Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?
tbCategories
CategoryID | Category
--------------------------
1 | Cars
2 | Trucks
tbAutomobile (Super Type)
VIN | fkCategoryID | Make | Model
-------------------------------------
1 | 1 |Honda | Accord
tbCars
fkVIN | Doors |
-----------------
1 | 2
tbTrucks
fkVIN | Cab | Capacity
---------------------------
2 | 0 | 1000
Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.
Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...
Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.
E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?
tbTrucks
fkVIN | Cab | Capacity | fkCapID
--------------------------------------
2 | Y | 1000 | 1
tbCaps
CapID | Vendor | Price | et....
Any thoughts at all? I thought this would have been a pretty damn hot topic!
Mike B
Connection Type Limitations When Using XML Data Type In SQL Task
Jul 23, 2007I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:
SELECT
(
SELECT
MT.MessageId 'MessageId',
MT.MessageType 'MessageType',
FROM MessageTable MT
ORDER BY MT.messageid desc
FOR XML PATH('MessageStatus'), TYPE
)
FOR XML PATH('Report'), TYPE
For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error:
<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>
Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections?
Thanks
Walter
Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation
May 22, 2015I have two xml source and i need only left restricted data.
how can i perform left restricted join?
Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?
Oct 8, 2015I 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.