Join Not Getting Desired Result?
Jun 16, 2015
I have a right join query where i have a table where all employees attendance entry are registered and another table where pay master table where al employees details.
I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
means for selected date i need all the employees details suppose 10 employees who punched or not with empty values even he resigned.
I used right or left join it gives all dates value but when i select particular date i get only the punched details.
View 5 Replies
ADVERTISEMENT
Dec 9, 2005
I am trying to select records based upon last name
WHERE (Last_Name BETWEEN 'A%' AND 'C%')
When I run this I get only last names starting with A and B--no C. ?!?
This is confusing to me....I ran it with lastname >= 'A%' and lastname <='C%'and it returned only names starting with B. Why does SQL ignore the "="
I hope this isnt obvious :/
View 6 Replies
View Related
Feb 4, 2008
Hi,
I want to write a query that would display data from the database on the basis of ethnicity in the last 3 years. Not only that, but I also want to see the number of starters, retention and achievement
e.g.
Year 05 Year06 Year 07
Ethnicity 1 Starters
Retention
Achievement
Ethnicity 2 Starters
Retention
Achievement
The problem is that there are multiple ethnicities and I cannot write separate queries for each of them and there are 3 years and I cannot write separate queries for them either. E.g. if i have 5 ethnicities and 3 years, in current situation, I am ending up with writing 15 queries for each of them because I can't write a cross tab query for these.
When I do write the query as follows:
create table #temp_et (
[Year]int NOT NULL,
[Ethnicity] varchar(20),
[Starts]float(4),
[Success]float(4),
[Retention]float(4),
[Achievement]float(4)
)
--Year 05/06
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum([pvstart]) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '05/06'
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
--Year 06/07
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum([pvstart]) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '06/07'
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
--Year 07/08
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum(pvstart) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 ELSE convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvcomp) = 0 THEN 0 ELSE convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '07/08'--Expected end year
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
SELECT
(CASE [Year] WHEN 05 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 05 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 05 THEN [Achievement] ELSE 0 END) AS [Achievement],
(CASE [Year] WHEN 06 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 06 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 06 THEN [Achievement] ELSE 0 END) AS [Achievement],
(CASE [Year] WHEN 07 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 07 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 07 THEN [Achievement] ELSE 0 END) AS [Achievement]
from #temp_et
This is the closest I could get to the output as in the end I want to put it into a report in Visual Studio .Net i.e. Linking that report through a stored procedure in SQL Server. If I copy all this code to a SP, and dont put the data in a temporary table, the SP will only return the top rows and not the data from year 06/07 and 07/08.
All i can think of is creating a crosstab query, which I cant. So help would be much appreciated.
P.S. The data for Ethnic Group is coming from a view and that is joined which I forgot to mention here in the code.
View 2 Replies
View Related
May 16, 2005
I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.
Thanks in advance!!
View 6 Replies
View Related
Jul 23, 2005
Hello group!i have a table "group_code" wich relates the names ofnt-(domain)-groups to codes. now i want use the stored procedurexp_logininfo (asking for the group-membership of the current user) tojoin the result to "group_code". then i must use the new result (thecode) to join against other tables.i know now, that i cant join results of SPs against tables. may be thata UDF with a table result is the correct approach. but i have no ideahow to wrap the xp_logininfo in a UDF.a other way can be to do in a UDF the same thing like the xp_logininfo.then this UDF should deliver at least the nt-(domain)-groups in wichthe current-user is a member.Is there anybody who can give me the code for that?many thanx in advance.Karl
View 1 Replies
View Related
Jul 23, 2005
I'm a quantitative securities analyst working with Compustat data(company fiscal reports and pricing feeds).My coworker came across a problem that we fixed, but I'd like tounderstand 'why' it was happening and just don't get it yet.Here's the starting query (reduced to simple prefixes):----INITIAL-----declare @mthDate datetimeset @mthDate = (select max(datadate) from t)declare @wkDate datetimeset @wkDate = (select max(datadate) from z)Select...fromzleft join a on a.idA = z.idA and a.idB = z.idBand a.datadate = z.datadateleft join b on b.idA = z.idA and b.idB = z.idBand b.datadate = @mthDateleft join c on c.idA = z.idA and c.idB = z.idBand c.datadate = @mthDateleft join d on d.idA = z.idA and d.idB = z.idBand d.datadate = z.datadateleft join e on e.idA = z.idAand e.datadate = @mthDateleft join f on f.idA = e.idA and f.datadate=e.date2left join g on g.idA = e.idA and g.datadate=e.date2left join h on h.idA = z.idAleft join k on k.ticker = z.tickerleft join m on m.idA = z.idA and m.idB=z.idBwherez.datadate = @wkDate<..some other expression filters...>and k.ticker is null----END INITIAL-----------As you can see 'z' is the main table that things are linked to viaouter joins (our security master). Table 'k' has a list of securitiesthat we wish not to have results for.There are 77 entries in table k and 4933 in table z for that giventime. We'd expect 4856 to be in this, but no. it's 4400, and then thenext time you run it (no changes whatsover) it's 2312, and so on.Every time you execute you get a different record count.My thought/and fix was to move the (k.ticker) predicate out of thewhere clause and get a differenced set from z using NOT EXISTS:-----AMENDED---------------from(z where not exists(select * from k where k.ticker=y.ticker)) yleft join a on a.idA = y.idA and a.idB = y.idBand a.datadate = y.datadateleft join b on b.idA = y.idA and b.idB = y.idBand b.datadate = @mthDateleft join c on c.idA = y.idA and c.idB = y.idBand c.datadate = @mthDateleft join d on d.idA = y.idA and d.idB = y.idBand d.datadate = y.datadateleft join e on e.idA = y.idAand e.datadate = @mthDateleft join f on f.idA = e.idA and f.datadate=e.date2left join g on g.idA = e.idA and g.datadate=e.date2left join h on h.idA = y.idAleft join k on k.ticker = y.tickerleft join m on m.idA = y.idA and m.idB=y.idBwherey.datadate = @wkDate<..some other expression filters...>------------------------And this works. It's stable now.I'm hoping someone here can help me up the wisdom curve by explainingto me 'why' the recordset kept changing before.My guess is that the cost-based optimizer was resorting the outer joinsand handing back different sets as a result, but i want to understand,and thought i'd come to this group for help.I appreciate your time and look forward to replies.Greg McIntire
View 7 Replies
View Related
Sep 18, 2007
I am currently studying Transact SQL and playing around with queries from a sample database. Recently I created the following query.
USE MemtrackSQL
SELECT m1.MemberID, m1.Surname, m1.FirstName, m1.DateOfBirth
FROM tblMember m1 JOIN tblMember m2
ON m1.FirstName = m2.FirstName
WHERE m1.MemberID <> m2.MemberID
This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
5 O'Grady Patrick 1975-09-23 00:00:00.000
7 Greenfield Lynne 1955-07-26 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
Any help in explaining where I have gone wrong here would be greatly appreciated.
Cheers
View 3 Replies
View Related
May 4, 2015
Required Output
Country Category
Internet Sales Amount
Internet Order Count
[code]....
I need to perform a SQL kind of Cross join to get the Total Count as a column along side as there are .net code and json structures defined that expects output in a certain format.
View 2 Replies
View Related
Mar 24, 2002
Hi all,
I faced a problem, I have two tables - part and partmaster
part : part_no, part_qty (no key)
partmaster : part_no, part_description (primary key : part_no )
I want to select table part.* and partmaster.part_description.
(run on mssql 2k)
select a.*, b.part_description
from part a, partmaster b where a.part_no *= b.part_no
I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok.
Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok.
can anyone tell me the reason?
Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq.
It does work!
Regards,
Simon
View 1 Replies
View Related
Feb 22, 2007
SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name
FROM Image, JobTag, Tag, Job, Tagcat
WHERE Tag.TagID = JobTag.TagID
AND Image.JobID = Job.JobID
AND Job.JobID = JobTag.JobID
AND TagCat.TagCatID = Tag.TagCatID
ORDER BY Image.ImageID, Tag.SortOrder
I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?
View 1 Replies
View Related
May 7, 2012
I am using SLQ Server 2008 R2. The database was designed by another company.
I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.
Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.
I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.
My SQL is:
select distinct (a.client_id), a.cli_last AS Last_Name,
a.cli_first AS First_Name, a.cli_middle AS Mid_Init,
b.city AS City, b.county AS County, b.state AS State
from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b
on a.client_id = b.client_id
inner join ECBH.dbo.tbl_client_insurance c
on a.client_id = c.client_id
inner join ECBH_TEST.dbo.tbl_GEF_County d
on b.county = d.COUNTY_NAME
where c.plan_id = 4
order by a.cli_last, a.cli_first
Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?
View 5 Replies
View Related
Aug 27, 2014
So if an item is a bottle, it will have a unique BottleKey and Null for CaseKey and if an item is a Case, it will have a unique CaseKey and Null for BottleKey. However, the PrimaryIDs are the same for both the bottle and case. I get the results to look like this:
Bottlekey CaseKey PrimaryID
4754 NULL ABC-234
NULL 5465 ABC-234
.... .... .......
Is there a way to get the result sorted by Primary ID?
So that the rows are halved from what we have in the table above and the results look like
PrimaryID CaseKey BottleKey
ABC-234 5465 4754
View 1 Replies
View Related
Aug 25, 2005
Here is the situation
Table 1 : tbl_documents
docIDdocName
1aaa
2bbb
3ccc
Table 2 : tbl_Rating
ratIDratingdocID
131
251
321
432
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0
NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”
PLs help me ?
Thx
View 3 Replies
View Related
Jan 20, 2005
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
Thanks in advance
greg
View 8 Replies
View Related
Aug 18, 2015
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
inner join
ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID
where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
where r1.HazMat='No' order by ProductID
View 9 Replies
View Related
Jun 1, 2015
I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.
CREATE TABLE Salesman_Product
(
SalesManID int,
ProductID int
)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4) Â
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4)Â
[code]....
View 6 Replies
View Related
Dec 3, 2015
I have 3 tables:
Â
TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3
View 5 Replies
View Related
May 28, 2015
I have sales data in SSAS cube and mapping data in RDBMS table. I want to apply join to get result into SSRS report.
Here we should get data of yesterday from time dimension of cube.
Time is in [Time].[FiscalYearHierarchy].[Fiscal Day].&[2015-05-28T00:00:00] format.
View 4 Replies
View Related
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View 21 Replies
View Related
Apr 18, 2015
I have a question with a row number. Below I have 2 columns that I want to Put a row number with.
ID. Quantity DesiredRowNumber
123 1. 1
123. 1 1
123. 1. 1
123. 3 2
123. 3. 2
[code]...
How would I get the desired row number.
View 1 Replies
View Related
Mar 8, 2007
Dear Friends, I've created one table, with the following attributes.
everything is ok, but while retrieving data, the question numbers should be starting from 1,2.......... but i'm not getting that result. please suggest me to the output like that.
--create table question_code_level (qno int,code varchar(10),level varchar(10),question varchar(500),option1 varchar(200),option2 varchar(200),option3 varchar(200),option4 varchar(200),option5 varchar(200))
select * from question_code_level
1htmlbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
3c#expertwhat is clr?somethingsomethingsomethingsomethingsomething
4c#intermediawhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
6vbbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
7htmlexpertwhat is clr?somethingsomethingsomethingsomethingsomething
8sqlserverintermediawhat is clr?somethingsomethingsomethingsomethingsomething
10oraclebeginerwhat is clr?somethingsomethingsomethingsomethingsomething
11javabeginerwhat is clr?somethingsomethingsomethingsomethingsomething
12javaexpertwhat is clr?somethingsomethingsomethingsomethingsomething
13sqlserverbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
--select * from question_code_level where code='c#' and level='beginer'
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
Vinod
View 7 Replies
View Related
Mar 25, 2008
Hi all,
The query below, as it stands, does a search for strings like 'Fema', that exist in the word 'Female', and returns 'female'. Now, if the word 'Acc' stands on it's own, I want to know how to return it as 'Accessories' in the result set. And yes, I've tried the obvious (When 'Acc' Then 'Accessories') but that returns a Null instead of 'Accessories'!!
Here's the query:
SELECT CategoryID, Category, CASE substring(category, 1, 4)
when 'fema' then 'female' WHEN 'Male' THEN 'Male' WHEN 'Gift' THEN 'Female' END AS CategoryGroup
FROM dbo.InventoryCategory
Thanks all,
Jaybee.
View 4 Replies
View Related
May 6, 2008
Hello,
I am trying to get results in one row from the following function but all records does not come from the following function.
I have 9 records of the same empid but results not showing all records. Can anybody help me to get all records.
CREATE FUNCTION dbo.GetBenefString5
(
@Empid INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ret VARCHAR(8000)
SELECT @ret = ''
SELECT
@ret = @ret + CASE WHEN LEN(@ret) > 0 THEN ',' ELSE '' END + FName + ' ' + Benefittype + ' ' + BenefitPercentage
From Beneficiary
Where Empid = @Empid
RETURN @ret
END
SELECT
Empid,
dbo.GetBenefString5(Empid)
FROM pf25eaton_work.dbo.eaton_chr_benef_05052008
where EmployeeNumber='4500498'
GROUP BY Empid
Result from the above function query:
Empid ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1773 CHARLENE OLIF 33 ,CHARLENE EADD 33 ,CHARLENE ELIF 33 ,TIMOTHY EADD 33 ,TIMOTHY
(
Records are in the table:
FName Benefittype BenefitPercentage
---------------------------------------- ----------- -----------------
CHARLENE OLIF 33
CHARLENE EADD 33
CHARLENE ELIF 33
TIMOTHY EADD 33
TIMOTHY ELIF 33
BRADLEY ELIF 33
TIMOTHY OLIF 33
BRADLEY OLIF 33
BRADLEY EADD 33
Desired Result in one row:
Charlene OLIF 33, Charlene EADD 33, Charlene ELIF 33, TIMOTHY OLIF 33, TIMOTHY EADD 33, TIMOTHY ELIF 33, BRADLEY OLIF 33, BRADLEY EADD 33, BRADLEY ELIF 33
View 10 Replies
View Related
Apr 10, 2008
Hi,
I have a query that I am trying to run, but I discovered I am not getting the desired results.
This query here goes across the tables as seen below. One thing that I notice about this query as I am trying to figure out the source of the problem is that it is bringing back the same results even when I change the @userID parameter.
As long as the @userID I pass exists in "tblUserDetails" then we get results back. When it doesnt exist we dont get any records.
Basically to explain whats going on. I am passing the userID of the person who has "photocomments"
Since the table "tblPhotoComments" doesnt have a column that represents who the photocomment was for, we must join
tblExtraPhotos.counterID = tblPhotoComments.photoCounterID
This way we can get details of the photo, including the owner (tblextraphotos.userID)
Any help is much appreciated..
thanks again,
mike123
CREATE PROCEDURE [dbo].[select_Photocomments_ManageMy]
(
@userID int
)
AS SET NOCOUNT ON
SELECT TOP 500 photoCommentID, PC.active, comment, commentFromID,
UD1.nameOnline as commentFrom_NameOnline, PC.commentDate, PC.photoCounterID
FROM tblPhotoComments PC
JOIN tblUserDetails UD1 on PC.CommentFromID = UD1.userID
JOIN tblExtraPhotos EP on EP.counterID = PC.photoCounterID
JOIN tblUserDetails UD2 on EP.userID = UD1.userID
WHERE UD2.userID = @userID AND deletedByRecipient = 0
ORDER by PC.commentDate DESC
CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL
)
GO
CREATE TABLE [dbo].[tblExtraPhotos](
[counterID] [int] IDENTITY(1,1) NOT NULL,
[photoID] [tinyint] NOT NULL,
[userID] [int] NOT NULL,
[photoDate] [smalldatetime] NOT NULL,
[caption] [varchar](50) NULL,
[status] [tinyint] NOT NULL
GO
CREATE TABLE [dbo].[tblPhotoComments](
[photoCommentID] [int] IDENTITY(1,1) NOT NULL,
[photoCounterID] [int] NOT NULL,
[CommentFromID] [int] NOT NULL,
[Comment] [varchar](1000) NOT NULL,
[commentDate] [smalldatetime] NOT NULL,
[Active] [tinyint] NOT NULL,
[deletedbySender] [tinyint] NOT NULL,
[deletedbyRecipient] [int] NOT NULL,
[IP] [varchar](15) NOT NULL
) ON [PRIMARY]
GO
View 5 Replies
View Related
Aug 11, 2006
Gopinath writes "Let us consider we have the following data in a table,
Column1 Column2
John orange
John apple
John Grape
Steve orange
Steve watermelon
Steve pineapple
in the above table, i want to query and get output in the below format
Column1 Column2
John orange
apple
Grape
Steve orange
watermelon
pineapple
is it possible? if yes, kindly let me know the query.
Thanks in advance.
Regards,
Gopi."
View 2 Replies
View Related
May 5, 2008
Query:
Select convert(CHAR(45),surname+','+fname) Name from beneficiary
Output from the above query:
Name
---------------------------------------------
BICKFORD ,ROSA
KOCH ,ERIC
I am desired Results as follows: Please help
Name
---------------
BICKFORD,ROSA
KOCH,ERIC
View 8 Replies
View Related
Jan 16, 2007
hi,
I fired the replication command sp_replcmds on my publisher and it returned the fillowing output.
"Another log reader is replicating the database."
can anybody out there explain why is that so and how can i get the output.
Thanks in advance
Jacx
View 1 Replies
View Related
Aug 31, 2007
Hi all,I have a SQL job where I do the following -I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() - DECLARE @intDoc INTDECLARE @xmlDoc VARCHAR(8000)IF(SELECT COUNT(*) FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable)) > 0BEGINSET @xmlDoc = (SELECT * FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable) FOR XML RAW) SET @xmlDoc = '<TABLE>' + @xmlDoc + '</TABLE>'
PRINT @xmlDocEXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlDoc--INSERT INTO CleanTable(C1, C2, C3, C4, C5)
SELECT C1, C2, C3, C4, C5, C6 FROM OPENXML(@intDoc,'/row',1)WITH (C1 INT, C2 CHAR(3), C3 CHAR(3) , C4 FLOAT, C5 INT)EXEC sp_xml_removedocument @intDoc
END
ELSE Output that I get is - <TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE>(0 row(s) affected) SO "PRINT @xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply If anyone has any better method to do it, inputs are welcome. Thanks
View 3 Replies
View Related
May 23, 2008
Hi Guys,
Below is example of the current structure of table1 I have at run time:
------------------------
GroupName Resourcename Week1 Week2 ..cont.. dynamically
Associates A1 0 80 ......
Assocaites A2 20 40 ......
Associates A3 50 100 ......
Principal P1 20 100 ......
Principal P2 0 0 ......
Principal P3 0 100 ......
------------------------
I want to change the above to something like below table2:
---------------------------
GroupName Status Week1 Week2 ....cont
Associates Assigned 2 3
Associates NotAssigned 1 0
Principal Assigned 1 1
Principal NotAssigned 2 1
---------------------------
I will try to explain how I am deriving table2 from table1. I have to count the number of Resource name against each Groupname for a particular week column(Weeki i 1 to n dynamic) where value of Week column is 0, then use this numbber against NotAssigned and the complementary number to be stored as Assigned.
The table formaating is lost in HTML view but just consider any gaps between fields as next column value.
Am I clear in what i am asking , if not please ask me.
Any help will be highly appreciated.
View 2 Replies
View Related
Mar 27, 2014
In order to ask my question on a SQL query I will use a simplified version of a table I was struggling with...
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦BMW
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦FIAT
So my question is: how can I select only SHOPs which have no CARS variety (e.g. a variety =1)?
In other words I am looking for a Query that would give me this outcome:
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
...because WEST HAMPSTEAD CARS, selling Mercedes only, has a CARS variety equal to 1 as well as Camdem Motors which sells only FIAT.
I tried with this query:
SELECT DISTINCT SHOP, CARS
FROM CAR_SELLERS
GROUP BY SHOP, CARS
HAVING COUNT(CARS) = 1
But it doesn't work.
In addition I also would like to know for example how to create a similar query for a different desired CARS variety (e.g. 2, 3...)
How to do the tabs or attach images in the Post New Topic Message box... I wanted to attach a screen shot of the table but I was not able to and so, in order to post my question, I prepared a simplified version of the table plus I had to do the tabs/spaces manually with the symbol (¦)
View 5 Replies
View Related
Sep 6, 2007
hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.
as an example
nsty nsly percentage
total: 15 7 X
A: 5 4 25%
B: 5 3 66%
C: 5 0 N/A
the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?
am i on the right track by researching filters and expressions, or is this a matter for the query
thanks in advance for any help
View 2 Replies
View Related
Jun 27, 2006
I have the following SELECT statement attached to a dropdown box:
SELECT [Workshop] FROM [Workshops] WHERE Workshop <> (SELECT Workshop FROM Workshop_Registration WHERE FullName = @FullName AND Completed = @Completed) ORDER BY [Workshop]
I am trying to get all workshops (50 or more) from the WORKSHOPS table that the logged in user is not already registered for. This works perfectly as long as the student is registered for at least 1 class. It populates the dropdown with all of the other classes. If they aren't registered for a class then it doesn't list any classes. The problem is definitely the subquery, but how do I make it to where if the subquery doesn't return any results (student not registered for anything), I get all of the workshops in the dropdown? Any help is appreciated!
MikeD
View 2 Replies
View Related
Jan 25, 2008
Hello,I have a problem the scenario is :I have data in an excel file and now I am reading data from that file and insert that data into sql database. this is well.but the problem is that I have few fields with date time data in excel sheet. In my database I have varchar type data type for these data columns.I want to read these data columns from the excel sheet and insert only time into the data base.how can I do this I am using the following line of code for selelcting only time from the excel file. string qry = "Select CONVERT(CHAR(5),datetime,114) from [" + objStr[0] + "];";this gives me error message.help me to read the data from excel file and insert it into the sql table in desired format. Thanks in advance, junior
View 1 Replies
View Related
