Problem Joining Child Data (JOIN, Subquery, Or Something Else?)
Jun 21, 2006
The problem:
I'm updating a report to be "multi-language" capable. Previously,
any items that had text associated with them were unconditionally
pulling in the English text. The database has always been capable of
storing multiple languages for an item, however.
Desired output:
Given the test data below, I'd like to get the following results
select * from mytestfunc(1)
Item_Id, Condition, QuestionText
1876, NOfKids <= 10, This many children is unlikely.
select * from mytestfunc(2)
CheckID, Condition, QuestionText
1876, NOfKids <= 10, NULL
The current SQL for my UDF:
CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (
SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,
tblQuestionText.QuestionText
FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
tblQuestionText.Item_ID)
WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR
(tblQuestionText.LanguageReference IS NULL))
)
Test data:
CREATE TABLE [dbo].[tblCheck] (
[Item_ID] [int] NOT NULL ,
[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCheck] ADD
CONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR
[CreationDate],
CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED
(
[Item_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLanguage] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Priority] [int] NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Spoken] [bit] NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADD
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLanguage] ADD
CONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR
[CreationDate],
UNIQUE NONCLUSTERED
(
[Priority]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOCREATE TABLE [dbo].[tblQuestionText] (
[Item_ID] [int] NOT NULL ,
[LanguageReference] [int] NOT NULL ,
[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SameAs] [int] NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblQuestionText] ADD
CONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR
[CreationDate],
CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED
(
[Item_ID],
[LanguageReference]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
INSERT INTO tblCheck (Item_ID, CheckDescr)
VALUES(1876, 'NOfKids <= 10')
INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(1,1,'English', 1)
INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(2,2,'Espanol', 1)
INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)
VALUES (1876, 1, 'This many children is unlikely.')
Any tips or pointers will be appreciated. Thanks.
View 2 Replies
ADVERTISEMENT
Sep 24, 2007
I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:
select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?
I tried to create a function to get the latest sentence for a chargeID like the following:
select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?
thanks,
View 4 Replies
View Related
Nov 15, 2004
Hello everyone,
I'm stuck on something here. Any help would be great. This is a relational database question.
I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.
Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_SubCategories.SubCategoryName,
_SubCategories.SubCategoryName,
LinkName
FROM
_Links
INNER JOIN
_SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId
INNER JOIN
_SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable
Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:
Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_Links_Categories.CategoryName,
_Links_StatusCategories.StatusName,
LinkName
FROM
_Links
INNER JOIN
_Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId
INNER JOIN
_Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable
I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.
Table_Categories: CategoryId (Primary Key), CategoryName
Table_SubCategories: CategoryId, SubCategoryId (Primary Key), SubCategoryName
So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.
If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.
Alec
View 3 Replies
View Related
Dec 10, 2004
I have a query that produces the following results:
wbs2 wbs3 Name AmtBud BTD BillExt
100 100 Traffic Impact 10000 13563.75 0
140 Highway/Signal 0 0 0
140 100 Highway/Signal 0 0 0
and another query that produces the follwing results:
wbs2 FeeType
100 SC
140 HM&SC
150 HM
160 HM&SC
180 SC
190 SC
So, I want to join both queries to produce the ending result:
wbs2 wbs3 Name AmtBud BTD BillExt FeeType
100 100 Traffic Impact 10000 13563.75 0 SC
140 Highway/Signal 0 0 0 HM&SC
140 100 Highway/Signal 0 0 0 HM&SC
Here are my queries:
first query:
SELECT CASE WHEN WM_Template.WBS2 = '500' THEN '1750.00' + WM_Template.WBS2 ELSE '2000.20' + WM_Template.WBS2 END AS WBS2,
WM_Template.WBS3 AS WBS3,
CASE WHEN WM_Template.WBS2 = '260' THEN 'Pre Con Mtg / Inspections/ Punch List' WHEN WM_Template.WBS2 = '250' THEN 'All Environmental Permits & Approvals'
WHEN WM_Template.WBS2 = '150' THEN 'Project Admin / Clerical / Status Reports ' ELSE WM_Template.LongName END AS LongName,
(ISNULL(LB.AmtBud, 0) + ISNULL(EB.AmtBud, 0)) AS AmtBud, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND
LedgerAR.Period <= '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BTD, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND
LedgerAR.Period = '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BillExt
FROM PR LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '')
AND WM_Template.WBS2 <> '210' AND pr.wbs1 = '001-298'
GROUP BY WM_Template.WBS2, WM_Template.WBS3, WM_Template.LongName, lb.amtbud, eb.amtbud
second query
SELECT WM_Template.WBS2, CASE WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0')
THEN 'HM & SC' WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN 'HM' WHEN (SUM(isnull(LB.AmtBud, 0))
= '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0') THEN 'SC' WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0')
THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') THEN 'SC' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0))
= '0') THEN (CASE WHEN (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0') THEN 'HM' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND
(SUM(isnull(EB.AmtBud, 0)) = '0') THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') AND (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0')
THEN 'HM & SC' END) ELSE 'N/A' END AS FeeType
FROM PR LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '')
AND pr.wbs1 = '001-333'
GROUP BY WM_Template.WBS2
Any suggestions would be helpful.
Thank You.
View 2 Replies
View Related
Jun 12, 2007
Please let me know this......
i was asked this question during my evaluations at my office.
When do you use and when do u use a subquery.....?please lt me kow the ideal condition for this.
I was asked to answer a specific reason for the above question.
thank you....
there is one more interesting question coming up....
Trust in Technology mate .....'Not in Human Beings'
View 5 Replies
View Related
May 2, 2001
I have a query problem that baffles me.
I have one data table (tblPresultPeriods) and one lookup table (lkpStatus).
The query is:
select PPPeriodNumber as PeriodNumber, Max(PPstatus) as Status
from tblPresultPeriods
where ppmemberid = 133 and pptransseq > 0
group by ppperiodnumber
It returns (correctly):
PeriodNumber Status
------------ ------
A1 1
Q1 4
Q2 1
I want to do a join so that I get the lkpStatus.StatusDescription value to display.
The query is:
select PPPeriodNumber, Max(PPstatus)as status, StatusDescription
from tblPresultPeriods, lkpStatus
where tblPresultPeriods.PPstatus = lkpStatus.Statuscode
and ppmemberid = 133
and pptransseq > 0
group by PPPeriodNumber, PPstatus, StatusDescription
order by PPPeriodNumber, PPstatus, StatusDescription
but I get (not what I want):
PPPeriodNumber status StatusDescription
-------------- ------ --------------------------------------------------
A1 0 Initialize
A1 1 Enter Presults
Q1 0 Initialize
Q1 1 Enter Presults
Q1 4 Re-Review Presutls
Q2 0 Initialize
Q2 1 Enter Presults
because the of the additional group by & order by columns.
I assume this requires a sub-query, but I am baffeled.
Bobby
View 2 Replies
View Related
May 1, 2004
Hi,
Iwant to make a left join between two tables connected with a specific field
but I also want to have some criteria on the right table.
I want to display all the rows from the first(left) table and only the rows from the secod (right) table that match the join and the criteria.
However If I have criteria on the second table the join behaves like an inner join displaying only the rows that exist in both tables.
In MS Access instead of the right table I use a query in which I enter the criteria and then I connect it with a left join in a new query in order to get the right results. How can I do itin MSSQLServer without using Views?
Must I use a subquery with Exists and what is the right syntax in order to achieve this?
Best Regards,
manolis Perrakis
View 1 Replies
View Related
May 13, 2008
I know I can't do this but I dont know will work. any help is much appreciated. the code below in red will always return more than one value, and yes the ?? is actually data (dont ask i didnt do it).
I am basically coding this because of the ?? someone before me thought was a good idea.
inner join MHDDTest.dbo.Admin_tbl_Vendor f on
(case when len(b.vadr) > 0 then
(case when (b.vadr = '??') then
(select vendorNumber from MHDDTest.dbo.Admin_tbl_Vendor
where (substring(afunction,3,2) + object) = '42305' and substring(vendorNumber,1,5) = b.vend)
else
b.vend + '-' + b.vadr
end)
else
b.vend
end
= f.vendorNumber)
View 9 Replies
View Related
Jun 21, 2007
After working with Oracle (can I say that out loud here? ) databases for a couple of years, our company recently switched to SQL2005. So far I really like it, and the switch has been relatively easy for me. Except for one thing...
I'm trying to find out how to join a subquery on multiple columns. In Oracle it would like this:
select * from #temp1 where (id_1, id_2) not in (select id_1, id_2 from #temp2)
But this doesn't work in SQL-T. I've temporarily solved it by doing this:
select * from #temp1 where id_1+id_2 not in (select id_1+id_2 from #temp2)
I don't like concat solutions for joins though (it always feels 'unsafe' to me). Plus, I can't believe there's no way to solve this.
I've searched the FAQ and BOL but I just can't seem to find the solution. Please help. I feel stupid.
View 5 Replies
View Related
Apr 24, 2008
Hi, I have a long sql query and I have to left join two tables. The problem is that I need to do a subquery in the left join for the second table. Let me give an example.
My second table haves this data:
ID GAME CREATED
1 SOCCER 1-8-1980
1 BASKKET 1-9-2000
2 TENIS 1-10-2000
2 GOLF 1-12-2000
2 CARS 1-9-2010
3 BIKE 1-2-1930
My first table left joins the second one by the ID column. So, I want first to determine the max record of each ID of the second table. Ones I have the max record for each ID in table one i want to left join it with table A.
How do I have to build the subquery in the inner join? I believe is something like this:
SELECT * FROM
TABLE1
LEFT OUTER JOIN TABLE2 (SUBQUERY) TABLE2_ALIAS
ON TABLE1.ID = TABLE2_ALIAS.ID
Thanks in advance!
View 7 Replies
View Related
Jan 20, 2006
Setup:
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
In SSIS one of the possible solutions is to use a package which does the following:
OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT
The problem with this approach is that it's extremely slow for large datafiles (50M records each)
Questions:
1) In the procedure above could the SORT step be avoided?
2) Is there another approach to run cross-servers JOIN in SSIS?
Thank you
View 3 Replies
View Related
Aug 24, 2015
SELECT A.EmpId,A.IncidentDate
FROM EmployeePoints1 as A
WHERE IncidentDate=
(SELECT MAX(IncidentDate)
FROM EmployeePoints1
WHERE EmpId = A.EmpId) AND (DATEADD(day,28,DATEADD(WEEK, DATEDIFF(WEEK, 0,A.IncidentDate), 0)) < DATEADD(WEEK, DATEDIFF(WEEK, 0,GetDate()), 0)) AND (A.IncidentCode = 'I' OR A.IncidentCode = 'A')
LEFT JOIN EmployeeTotalPoints1 ON EmployeeTotalPoints1.EmpId = A.EmpId
I am trying to left join another table but I got
Incorrect syntax near the keyword 'LEFT'.
View 9 Replies
View Related
Sep 13, 2006
Hello Chaps,
I have got question about the performance of subquery and left outer join
My scenario is that I have two tables Customer Mater and address book.
Customer Mater and address book tables hold the record for both carrier and customer. and i want retrieve the record for customer and related carrier as well
i have two options
1. either use left outer join (i.e. Join Customer master to Address Book 2 times, 1 for customer and 1 for carrier) by:
Address book (as customer) Left outer Join Customer Master (on related Key)
AddressBook (as carrier) Left Outer Join Customer master (on related Key)
2.or use 1 join for customer and use subquery to retrieve carrier name (from Customer table) as I only need that one
Address Book AB (as Customer) Join Customer Master CM
Subquery: Select carrier_name From customer_master cm2 where cm2.carrier = ab.carrier
can any one tell one which is the best as far as performance is concern. My opinion is that the subquery is better becuase left outer join will have the overhread of extra columns... not pretty sure about it..
Any idea?
regards,
Anas
View 9 Replies
View Related
May 19, 2015
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. Â So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01Â 2015-12-31 AAÂ 2015-01-01Â 2015-03-31Â BBÂ 2016-01-01Â 2016-03-31Â CCÂ 2017-01-01Â 2017-03-31Â DDÂ 2017-01-01Â 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
[Code] .....
View 6 Replies
View Related
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
May 22, 2006
Hi all,
I got an error message 156, when I executed the following code:
////--SQLQueryParent&Child.sql---////////
Use newDB
GO
----Creating dbo.Person as a Parent Table----
CREATE TABLE dbo.Person
(PersonID int PRIMARY KEY NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
City varchar(25) NOT NULL,
State varchar(25) NOT NULL,
Phone varchar(25) NOT NULL)
INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone)
SELECT 1, "George", "Washington", "Washington", "DC", "1-000-1234567"
UNION ALL
SELECT 2, "Abe", "Lincoln", "Chicago", "IL", "1-111-2223333"
UNION ALL
SELECT 3, "Thomas", "Jefferson", "Charlottesville", "VA", "1-222-4445555"
GO
----Creating dbo.Book as a Child table----
CREATE TABLE dbo.Book
(BookID int PRIMARY KEY NOT NULL,
BookTitle varchar(25) NOT NULL,
AuthorID int FOREIGN KEY NOT NULL)
INSERT dbo.Book (BookID, BookTitle, AuthorID)
SELECT 1, "How to Chop a Cherry Tree", 1
UNION ALL
SELECT 2, "Valley Forge Snow Angels", 1
UNION ALL
SELECT 3, "Marsha and ME", 1
UNION ALL
SELECT 4, "Summer Job Surveying Viginia", 1
UNION ALL
SELECT 5, "Log Chopping in Illinois", 2
UNION ALL
SELECT 6, "Registry of Visitors to the White House", 2
UNION ALL
SELECT 7, "My Favorite Inventions", 3
UNION ALL
SELECT 8, "More Favorite Inventions", 3
UNION ALL
SELECT 9, "Inventions for Which the World is Not Ready", 3
UNION ALL
SELECT 10, "The Path to the White House", 2
UNION ALL
SELECT 11, "Why I Do not Believe in Polls", 2
UNION ALL
SELECT 12, "Doing the Right Thing is Hard", 2
GO
---Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table
SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID
GO
////---Results---//////
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
////////////////////////////////////////////////////
(1) Where did I do wrong and cause the Error Message 156?
(2) I try to get a Parent-Child table by using the LEFT OUTER JOIN via the following code statement:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
Can I get a Parent-Child table after the error 156 is resolved?
Please help and advise.
Thanks,
Scott Chang
View 9 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
Nov 7, 2007
OK so there is some data in an Oracle DB that I need to query and analyze. Unfortunately, the criteria for selecting/grouping the data is stored in a MS Sql Server DB. This cannot be changed.
SqlServerGroup Name ID# Item ConditionAAA123 1 a 1AAA123 2 a 1AAA123 3 a 1AAA123 4 a 2AAA123 5 a 2AAA123 1 b 3AAA123 2 b 4AAA123 3 b 3AAA123 4 b 4AAA123 5 b 3BBB123 1 a 1BBB123 2 a 1BBB123 3 a 2BBB123 4 a 2BBB123 5 a 2
OracleGroup Name ID# ValueAAA123 1 50%AAA123 2 55%AAA123 3 60%AAA123 4 80%AAA123 5 70%BBB123 1 35%BBB123 2 45%BBB123 3 50%BBB123 4 50%BBB123 5 80%
I need to be able to get this:Group Name Item Condition ValueAAA123 a 1 55%AAA123 a 2 75%AAA123 b 3 60%AAA123 b 4 67.5%BBB123 a 1 40%BBB123 a 2 60%
Any idea how I can get the data from these two DBs to talk to each other? Thanks.
View 6 Replies
View Related
Mar 19, 2007
I know similar questions have been asked but I wanted to try my luck that my issue is somehow different.
I am querying a database which has detail information (sales transactions) and is grouped by customer. I also have a table with one record for each customer with some historical sales information (summary information). The requirements for the report are to have the sums of the sales for each customer along with the historical data for that customer in the same row in the table. I haven't found a way to do this using one dataset and from what I've read, the current version doesn't support joining multiple datasets over a grouping field (customer).. or at all.
Any one have ideas?
View 1 Replies
View Related
Jun 6, 2008
Can anyone show me how to join 2 different data types in a SQL query. For example, I want to join two tables.
Table 1:
- Field 1 = ID (Varchar,10)
- Field 2 = Balance
Table 2:
- Field 1 = SSN (Numeric, 9)
Field ID should be = to SSN, but the data type is different. PLease help me to join them.
Thanks,
View 7 Replies
View Related
Oct 17, 2007
Hi,
I have multiple table with the same schema as follows
Table1
ID Date
---------------
1 D1
2 D2
3 D3
4 D4
Table2
ID Date
---------------
2 D21
3 D31
4 D41
.....
I just want to build a mater table out of these existing tables . I need a query that builds as faster as possioble as these tables might contains millions of records
The final Table Should be as follows
ID CatName Date
------------------------------
1 Name1 D1
2 Name2 D2
2 Name21 D21
3 Name3 D3
3 Name31 D31
4 Name4 D4
4 Name41 D41
Can any one provide me with the best performace query that results the above output
With Best Regards,
~Mohan
View 2 Replies
View Related
Oct 8, 2007
Hello,
I am using Visual studio 2005 and have such a problem. I have a report that takes most of its date from €œordersview€? that is linked to lots of other fields. The final report has fields such as product name, number, Date, quantity and actual quantity etc. This works fine, but the program that I get the data from can€™t keep all the data in itself, because it slows the time of its work enormously. So we made it so that all the data of orders that are completed ant older that 2 days would be copied to another table €œOrdersArchive€? and deleted from €œOrders€? table.
Now I need to make a report that takes data from €œordersArchive€? if its older that two days, and from €œorders€? if it is new. The fields are the same in both cases and I would like the data to appear in the same table so it could be grouped, sorted and evaluated by the same criteria. Is it even possible and if it is how should I do it?
For now I only managed to create 2 separate tables in the same report that take date from different datasets.
Regards
Darius
View 4 Replies
View Related
May 30, 2008
Dear all,
I have four different tables with three columns. The first two columns are equal for all tables. The third column is different:
Table I
COUNTRY | PRODUCT | SALES VALUE
AAA AAA 10
AAA AAB 10
Table II
COUNTRY | PRODUCT | SALES COST
AAA AAC 10
Table III
COUNTRY | PRODUCT | SALES MARGIN
AAB AAA 10
Table IV
COUNTRY | PRODUCT | SALES XXX
AAA AAA 5
What I would like to accomplish is to obtain a table where the records from these tables would be joined in a single table:
COUNTRY | PRODUCT | SALES VALUE | SALES COST | SALES MARGIN | SALES XXX
AAA AAA 10 0 0 5
AAA AAB 10 0 0 0
AAA AAC 0 10 0 0
AAB AAA 0 0 10 0
Can you advise me on what the best way to achieve this is? Thanks!
Kind regards,
Pedro Martins
View 4 Replies
View Related
Jul 25, 2014
Basically i have three Tables
Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
View 1 Replies
View Related
Oct 22, 2007
............child |parent|.............a1 |a | .............a2 |a |.............a11 |a1 |.............b1 |b |.............b11 |b1 |.............b111 |b1 |.............
Here is my table I want to get all childs of "a" ie {a1,a11,a2}I mean recursivelyie child,chid of child ,child of child of child ........etc up to any extentet the table containHow can i for mulate the select querry?
View 3 Replies
View Related
Dec 21, 2006
I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.employee table (empid, name)tasks table (taskid, empid, taskname, resolution)If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!SELECT e.empid, e.name, COUNT(t.ID) as 'tcount'FROM tasks t RIGHT JOIN employee e ON c.empid = t.empidGROUP BY e.empid, e.nameorder by 'tcount' desc
View 3 Replies
View Related
Jun 26, 2015
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
View 3 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Jul 6, 2015
I find that dynamic masking does not work on joining tables in SQL Server 2016 CTP2.1.
For examples, I create the following table:
CREATE TABLE [dbo].[HRM_StaffAppointment](
[StaffID] [nvarchar](11) NOT NULL,
[ApptSeqNo] [smallint] NOT NULL,
[ReportingDept] [nvarchar](10) NULL,
[Code] ...
Then I apply mask on StaffID and RankDesc.
alter [dbo].[HRM_StaffAppointment] alter column [StaffID] add masked with (function='default()')
alter [dbo].[HRM_StaffAppointment] alter column [RankDesc]
add masked with (function='default()')
When User A logged in and query on HRM_StaffAppointment, StaffID and RankDesc are perfectly masked. But User A can remove the masking using another table:
CREATE TABLE [dbo].[staffID](
[staffID] [nvarchar](255)Â
) ON [PRIMARY]
select a.*
from dbo.HRM_StaffAppointment as a
left join dbo.staffID as b
on a.StaffID = b.StaffID
It looks like a security hole to me, or I'm doing anything wrong?
View 4 Replies
View Related
Jun 24, 2015
I have a table with parent child relationship data.
DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '06/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)
SELECT * FROM @DATA
1. For U_ID = 1, we have two diffrent U_NM and for one U_NM we have 2 child data.  Need to show parent data order by U_ORD and need to show child data within their parent order by U_DT
2. For U_ID = 2, we don't have child data, hence need to show data order by U_ORD only
SELECT 1 AS U_ID, 'Plan' AS U_NM, '06/01/2015' AS U_DT, 1 AS U_ORD, NULL AS P_U_NM
UNION ALL SELECT 1, 'Cust Plan 2', '06/05/2015', 0, 'Plan' UNION ALL SELECT 1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'
UNION ALL SELECT 1, 'Design', '06/15/2015', 2, NULL UNION ALL SELECT 2, 'Plan', '06/20/2015', 1, NULL UNION ALL SELECT 2, 'Design', '06/25/2015', 2, NULL
View 3 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related