SQL Server 2012 :: Multiple Level Join On Same Table?
Oct 10, 2015
i have below queries each select is fetching records at one level. Is there a way i can write single query to get to nth level (recursion) instead joining same table 10 times (i don't know in some cases there is may be next level) I stopped at 10th level now. In below example i gave only two levels.
SELECT Distinct
a.Col1 AS EmpID,
a.Col1 AS EmpID,
a.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case
[code].....
View 1 Replies
ADVERTISEMENT
Jun 18, 2014
I came across this structure today and haven't seen it before:
SELECT blablabla
FROM T1
FULL OUTER JOIN T2 ON
T1.Col1 = T2.Col1
AND T1.Col2 = T2.Col2 ON
T3.Col1 = T1.Col1
AND T3.Col2 = T1.Col2 ON
T4.Col1 = T1.Col1
AND T4.Col2 = T1.Col2
I can follow along until the second "ON".
View 9 Replies
View Related
Sep 1, 2015
DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))
INSERT INTO @Tab1
SELECT 1, 'A', 'B', 'C'
UNION ALL
SELECT 2, 'D', 'E', 'F'
UNION ALL
[Code] ....
Need query to get the Result as below
PSPS_Col1PS_Col2NPS1NPS1_Col1NPS1_Col2NPS2NPS2_Col1NPS2_Col2
ANULLNULLBNULLNULLCXYZ123
DAAA111ENULLNULLFNULLNULL
GNULLNULLHNULLNULLINULLNULL
JBBB222KNULLNULLLNULLNULL
By using case statement we can retrieve the data, but looking for alternate way to do it.
View 4 Replies
View Related
Oct 12, 2014
I am trying to enable CDC feature in SQL Server 2012 Enterprise Edition ( 11.0.2424.0).The command to enable CDC for Database worked fine, but the command for enabling CDC on Table failed.Command used for enabling CDC on table is as follows.
USE DatabaseName
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'TableName',
@role_name = NULL,
@supports_net_changes = 1
Following is the error message got
Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 623...Could not update the metadata that indicates table [dbo].[TableName] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database DatabaseName to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_ jobstep_ internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.
View 4 Replies
View Related
Apr 29, 2014
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
View 7 Replies
View Related
Jul 20, 2005
Thanks in advance for you help.SQL Server 2000I have a complex join, but a simple example will do the trick so...table1 (placement records, possibly many per case,highest ID beingmost recent)----------placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Numbertable2 (Organizations Table, Many Placements can be at 1 organization)----------OrganizationID(pk) * OrgName * OrgTypetable 3(Case Table, each Case can have many placements)----------CaseID(pk) * StaffName * CreationDatenow my query...SELECT t1.placementID,t1.caseID,t2.OrgNameFROM table1 as t1INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationIDWHERE exists (select distinct max(placementID),CaseIDfrom t1 group by caseID)GROUP BY t2.OrgName,t1.PlacementID,t1.CaseIDmy results-------------placementID CaseID OrgName1 1 oneOrg2 1 two org3 1 three org4 2 another org5 3 yet another orgmy desired results------------------PlacementID CaseID OrgName3 1 three org4 2 another org5 3 yet another orgas you can see i get all records, but what i want is to see only thelast placementID for each case so i dont want duplicate caseID but Ido need the orgName, and yes the query works correctly without the orgname but as soon as i add orgName to the select statement I getduplicate CaseID's, How do i eliminate duplicate CaseID's and get onlythe MAX(placementID) for each Case and the OrgNameplease advise, getting desperate.thanks again so much for the help
View 1 Replies
View Related
Jan 7, 2014
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
The structure of the periods table is as follows:
[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL
The stored procedure is currently:
USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
View 1 Replies
View Related
Feb 6, 2015
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
View 6 Replies
View Related
May 12, 2014
I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.
i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records
I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below
SELECT
a.[No_],
a.[Name],
a.[Currency Code],
a.[Salesperson Code],
b.[Sales Code],
[code]....
View 4 Replies
View Related
Feb 19, 2015
I need to create a table from these 2 tables and want all the data. Table 1 has 15000 records and table has around 1000 records.
I have 2 tables.
SELECT [UniqueID]
,[Company]
,[CustID]
,[CustomerName]
,[FiscYr]
[Code] ....
View 5 Replies
View Related
Sep 22, 2014
I am trying to pull in columns from multiple tables but am getting an error when I run the code:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.BOC" could not be bound.
I am guessing that my syntax is completely off.
SELECT
b.[PBCat]
,c.[VISN] --- I am trying to pull in the Column [VISN] from the Table [DIMSTA]. Current Status: --Failure
,a.[Station]
,a.[Facility]
,a.[CC]
,a.[Office]
[Code] ....
View 2 Replies
View Related
Apr 24, 2014
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
View 9 Replies
View Related
Dec 6, 2013
I know how to check for a sinle vlaue but how do I chekc to see if multiple values exist. I need to check for certain email addresses from a list that I have.
Let us say I ahve 3 email addresses, I want to check for all of them in a table and for eevery email address that is present I want to print something like "You email address is XXX" and if one of those 3 is not found my results should look like
"You email address is XXX"
YYYYY not found
"You email address is ZZZZ"
I'm attaching some TSQL that I tried on [AdventureWorks2012].[Person].[EmailAddress]
/****** Select ALL if where an email address is present in the list ******/
SELECT EmailAddressID,EmailAddress
FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN
(
'ken0@adventure-works.com', --1
'terri0@adventure-works.com', --2
[Code] ....
-- Test to see if a single email address is present
IF EXISTS
(
SELECT EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN ('25rob0@adventure-works.com')
)
BEGIN
SELECT 'Email address is presnt'
[Code] ....
When I check multiples using EXISTS it works as per its design and says YES even if a single item is present.
View 4 Replies
View Related
Jan 23, 2014
I am trying to pivot table DYNAMICALLY but couldn't get the desired result .
Here is the code to create a table
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
[Code] .....
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
View 1 Replies
View Related
Nov 17, 2014
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
View 4 Replies
View Related
Dec 9, 2014
My xml is in below format:
DECLARE @MyXML XML
SET @MyXML = '<RS>
<R id="6330">
<WF id="71445">
<WFS id="12790"> <fname>John12790</fname> </WFS>
[Code] ....
I need to insert into table with TSQL query:
My output should be similar below:
John12790 12790 71445 6330
Eric 12791 12790 71445 6330
John12793 12793 71446 6331
Eric12794 12794 71446 6331
otherwise i need to write huge c# code to execute such thing..
View 2 Replies
View Related
Feb 12, 2015
I am looking a script which allow me add single coilumn to multiple table of my database.
For Example :-
I am having 4 table
1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table
Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.
Alter table EMP
Add Rowchecksum varbinary(8000)
View 1 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Oct 22, 2014
I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.
CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)
[Code] .....
The results I am looking to achieve are:
PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4
Is there an SQL statement that can be used to accomplish this?
View 1 Replies
View Related
May 13, 2015
I created a CTE which finds a subset of records from a table
I then ran a SELECT statement against the same table as
SELECT * FROM TABLE
EXCEPT (SELECT * FROM CTE)
Is it possible to add another EXCEPT statement after the CTE EXCEPT statement to cover a condition not incorporated in the CTE definition?
View 9 Replies
View Related
Jan 29, 2014
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
View 9 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Oct 26, 2015
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
View 2 Replies
View Related
Jan 1, 2014
I am struggling with joining on the same table and cannot seem to get my head around the solution.
I have three tables as follows:
Patients Samples Results
UniqueID UniqueID
Surname LabNumber LabNumber
Forename (etc.) Sampled Code
Result
The code is the name of the test and the result is just that. Not all patients will have the same set of results. What I'd now like to do is pull out a CSV file of all results from 2013 including where the result is null. The format I am looking for is along the following lines:
Sex, Age, AKIN2, AKIN7, RIFLE,
The AKIN2, AKIN7 and RIFLE are the codes in the result table. I have tried OUTER JOINS but it seems to only pull out those records that exist (example below). I need to specify the result codes I am interested in otherwise the output could be enormous but this then does not pull out null values (a NULL value is an important as an actual value).
SELECT
Samples.Patient_ID AS ID,
Samples.LabNo As LabNo,
Patients.Sex As Sex,
DATEDIFF(year, Patients.DoB, Samples.Sampled) AS Age,
Samples.Source as Source,
[Code] ....
View 2 Replies
View Related
Oct 10, 2006
Ok, this is what I am trying to do:,
1) I am trying to get the number of employees that has completed all their
online training within 10 days of hire
2) All the employees that are has no exception(no pre-service training) and
has completed their checklist within 10 days
3) all the exception(pre-service) employees that has completed their
training within 70 days.
I have these tables, hipaa2006, hipaa101, hipaa201_inputs,
domesticviolence, securityawareness, securityawareness2006,
civilrights_input, and people_first_data.
People_first_data contains all the employees we have in our database, it
has empl_pfid col, but does not have last_modified col which all the other
tables has.
I have this queries:
1) select distinct(count(empl_pfid))
from people_first_data
left outer join tbl_domesticviolence on people_first_data.empl_pfid =
tbl_domesticviolence.employee_pfid
left outer join tbl_hipaa101 on
people_first_data.empl_pfid=tbl_hipaa101.employee_pfid
left outer join tbl_hipaa201_input on people_first_data.empl_pfid=
tbl_hipaa201_input.employee_pfid
left outer join tbl_hipaa2006 on
people_first_data.empl_pfid=tbl_hipaa2006.employee_pfid
left outer join tbl_securityawareness on
people_first_data.empl_pfid=tbl_securityawareness.employee_pfid
left outer join tbl_securityawareness2006 on people_first_data.empl_pfid=
tbl_securityawareness2006.employee_pfid
where people_first_data.empl_pfid = '639846'
and tbl_domesticviolence.last_modified is not null
and tbl_hipaa101.last_modified is not null
and tbl_hipaa201_input.last_modified is not null
and tbl_hipaa2006.last_modified is not null
and tbl_securityawareness.last_modified is not null
and tbl_securityawareness2006.last_modified is not null
2) ( I tried union, but i only wanted one number)
select count(last_modified) as date1 from tbl_hipaa2006
union
select count(last_modified) as date2 from tbl_hipaa101
Your help will be highly appreciated.
View 1 Replies
View Related
Nov 5, 2004
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
Thx
View 12 Replies
View Related
Dec 16, 2004
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1]
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7]
WHERE
[table4].[field2]=[table1.field2]{this is an inner join}
[table4].[field2]=[table2.field2]{this is an inner join}
[table4].[field2]=[table3.field2]{this is an inner join}
[table4].[field2]=[table5.field2]{this is an inner join}
[table5].[field3]=[table6.field2]{this is an inner join}
[table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
View 2 Replies
View Related
Oct 8, 2014
I have a table that looks like below.
DECLARE @Tree TABLE
(
NAME VARCHAR(100),
[LEVEL] INT
)
INSERT INTO @Tree
SELECT 'ABCD', 1
UNION
SELECT 'ABBBCDD', 2
UNION
SELECT 'AABBCCDD', 2
UNION
SELECT 'AAAABBBBCCCCDDDD', 3
My question is based on the Level, I want to append that many _(underscores) before the name.
Eg : for level 3 I want the name as ___AAAABBBBCCCCDDDD.
I was thinking to insert this into a temp table and call a while statement based on the number.
View 2 Replies
View Related
Jul 15, 2014
I have table A
|account | Unmort |
| A |10.000.000 |
and a Table B
|account| Jenis | Nominal | Unmort |Total|
-------------------------------------------
| A | 021 | 200.000| - | - |
| A | 028 | 3.200.000| - | - |
| A | 023 | 7.200.000| - | - |
how to update to be like this??
|account| Jenis |Nominal | Unmort |Total |
| A | 021 |200.000 | - |200.000 |
| A | 028 |3.200.000 | 2.800.000 |400.000 |
| A | 023 |7.200.000 | 7.200.000 | 0 |
for this type of account number jenis 021 Field Unmort Fill set= 0 and Field Total must not be a minus...
View 3 Replies
View Related
Oct 21, 2014
I have 2 tables that I need to merge let me explain. I have a range of product ID's that have a product grouping of * meaning all product groups. So I have a table with products and one with around 100 groups
ProdID ProdGrp
-------- ---------
11 *
12 *
ProdGrp ProdGrpDesc
--------- ---------------
A Prod Group A
B Prod Group B
C Prod Group C
I need a table which looks like the below but I have no joining mechanism
ProdID ProdGrp
-------- ---------
11 A
11 B
11 C
12 A
12 B
12 C
View 3 Replies
View Related
Jun 9, 2015
I have standard SQL query for clients, but sometimes a client does not have a specific table.
How do I deal with that? (creating the table is not an option)
SELECT
A.Col1
,A.Col2
,B.Col3
--> Col 50
FROM
TableA A Join
TableB B ON A.ColA = B.ColA
Suppose in some cases TabelB does not exist. How do I make sure the rest of the query runs okay and does not result in an error?
View 4 Replies
View Related
Jul 23, 2005
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8
View 3 Replies
View Related