A Simple Group By Query Seems Impossible
Jul 20, 2006
I've been fighting with this all day there has to be an easy way to do this in a single query:
FOR EACH fac_id, get the INSPECTION_ID of the earliest start_date:
INSPECTION_IDFAC_IDSTART_DATE
3007200406280045662004-07-07
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003020600314582003-02-05
30072003012100214802003-03-25
30072003012100314802005-02-02
20082003123000114362003-12-30
30072004061600615662004-08-26
30072004061600115662001-08-26
30072004061600515662002-08-26
30072004061600215662003-08-26
30072004061600315662004-08-26
30072004061600415662006-08-26
30072004050400415692004-09-10
DESIRED OUTPUT
INSPECTION_IDFAC_IDSTART_DATE
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003012100214802003-03-25
20082003123000114362003-12-30
30072004061600115662001-08-26
30072004050400415692004-09-10
Am i missing something...
any help would be greatly appreciated...
View 1 Replies
ADVERTISEMENT
Jul 20, 2006
I've been fighting with this all day there has to be an easy way to do this in a single query:
FOR EACH fac_id, get the INSPECTION_ID of the earliest start_date:
INSPECTION_IDFAC_IDSTART_DATE
3007200406280045662004-07-07
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003020600314582003-02-05
30072003012100214802003-03-25
30072003012100314802005-02-02
20082003123000114362003-12-30
30072004061600615662004-08-26
30072004061600115662001-08-26
30072004061600515662002-08-26
30072004061600215662003-08-26
30072004061600315662004-08-26
30072004061600415662006-08-26
30072004050400415692004-09-10
DESIRED OUTPUT
INSPECTION_IDFAC_IDSTART_DATE
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003012100214802003-03-25
20082003123000114362003-12-30
30072004061600115662001-08-26
30072004050400415692004-09-10
View 2 Replies
View Related
Jun 13, 2006
have a table with sale_id, date, sales_person_id
i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.
How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005?
How many sales person had atleast 25 sales each year 2003-2005
View 1 Replies
View Related
Mar 12, 2008
First of all, here's a description of the three tables involved in the query:
clients
id
name
services
id
name
clients_services
id
client_id
service_id
As you can imagine, I have a list of clients and each client can have several services assigned to him.
So, I have trying to come up with a query that would allow me to do display the following information inside a DataGridView without the use of any additional code:
I would like to be able to display all the services included in the services table and then, depending on the client that is currently selected, to have each of the services display a checkbox in the DataGridView showing the services currently assigned to that particular client. Something like this:
The services table has the following entries: Research, Consulting, Development, Data Protection and Design
The selected client id is "2"
That client has the following services assigned to him: Development and Design
The DataGridView should then display:
[ ] Research
[ ] Consulting
Development
[ ] Data Protection
Design
Is this possible without writing any code?
View 5 Replies
View Related
Apr 26, 2005
I have a table with traffic citations. I simply want to select records where one license plate number appears multiple times in the table, and has a customer id of null in at least 1 record and customer id of NOT null in at least 1 other record.
I know this is simple, but obviously this query does NOT work:
SELECT VEHICLE_LICENSE_NUMBER
FROM CITATIONS
WHERE (CUSTOMER_ID IS NULL AND CUSTOMER_ID <> NULL)
GROUP BY VEHICLE_LICENSE_NUMBER
Any advice is much appreciated! Thank you so much for your time.
View 4 Replies
View Related
Jul 20, 2005
Suppose the following tables:parent------parentidaddressphone
View 1 Replies
View Related
Jul 4, 2007
I did:
select city, count(*) from classifieds_address group by city
You would think it would distiguish between "Koh Phangan" and "Koh Phangan ". No!!! It shows them as one row in SQL!!! Is this SQL bug?
Aberdeen 1bayombong 1Brooklyn 4Castle Peak Bay 1Causeway Bay 2Chiang Mai 1Hong Kong 1Hong Kong Wan Chai 1Hong Kong Island 10Hong Kong Sheung Wan 1Karachi 1Koh Phangan 21Koh Samui 37Koh Tao 9Kowloon 13Kowloon, Tsim Sha Tsui 1Lantau Island 1Mid-Levels 3New Territories 2New York 3North Point 1Oligar 1Tsim Sha Tsui 1Wan Chai 5Yau Ma Tei 1Yau Ma Tei, Kowloon 1
View 5 Replies
View Related
Apr 2, 2007
In Sql Server
Code Snippet
CREATE TABLE t_contact
(
Id uniqueidentifier,
FirstName nvarchar(50),
LastName nvarchar(50),
TaskId uniqueidentifier
)
GO
CREATE TABLE t_task
(
Id uniqueidentifier,
Start datetime
)
GO
INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');
INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')
INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')
GO
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
GO
Result (Expected)
2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1
Result In Sql CE (UnExpected)
2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3
Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.
Thanks Adam
View 3 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Dec 5, 2007
Hi, I'm working in a web project.
In our lab, all the PCs are installed with SQL Server Management Studio, and the codes of the site are left by the previous batch of programmers.
Here is briefly how our web works, the web will call the sql server providing a username (userA) and password (aaaaaa) to log into the database, then the web can connect to the database to do various functions like select, insert, update and delete.
Currently anyone can go to thier SQL Server Management Studio on thier PC to edit various things like names and columns of tables by logging in as userA.
But, we now only want a small number of users to have the ability to change things in the database. What are some ways we can do that?
Something that I've thought of is that only allow userA to log in and do functions like select, insert, update and delete and do not have the ability to edit things like names and columns of tables. Have a userB with password only known to me that can have full control of the database which I have done.
View 2 Replies
View Related
May 7, 2001
I have a table that has three fields:
CATEGORIES
----------
catID
description
parentCatID
Some categories have sub-categories. Therefore, some entries will have null
for parentID while others will point to another category.
I need to select all of the categories, but have them returned in the
correct parent/child order (parents BEFORE children). I can't seem to get this to work correctly. Is this not possible or am I just being stupid?? :)
Thanks for any assistance.
Mike V.
View 5 Replies
View Related
Jan 12, 2005
Hi! all
I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure
CREATE PROCEDURE [update_Alarm_1]
declare @datetime1 ?????
declare @datetime2 ?????
[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 0 ] = @datetime1
[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 1 ] = @datetime2
[SELECT date_part( 'minute', datetime1 - datetime2 )] = @result
AS UPDATE [Time_Result]
SET [Comment] = @Comment_2
WHERE
( [result] = @result)
Thanks in Advance
View 1 Replies
View Related
Jan 12, 2005
Hi! all
I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure
CREATE PROCEDURE [update_Alarm_1]
declare @datetime1 ?????
declare @datetime2 ?????
[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 0 ] = @datetime1
[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 1 ] = @datetime2
[SELECT date_part( 'minute', datetime1 - datetime2 )] = @result
AS UPDATE [Time_Result]
SET [Comment] = @Comment_2
WHERE
( [result] = @result)
Thanks in Advance
View 3 Replies
View Related
Aug 24, 2005
I've created a database : hotelselect * from hotel worksselect * from hotel where dpt='5' doesn't work : generate a errorAnyone has a explanation ?create table hotel ([hoteID] int IDENTITY (5000,1) NOT NULL,[dpt] ntext NOT NULL) ON [primary]
View 3 Replies
View Related
Sep 1, 2004
We currently have a pull transactional subscription running from a remote 'PublicationServer' in another country to our local server.
After restarting our fire wall we get the following error message:
"The process could not execute '{call sp_MSget_subscription_guid(16)}' on 'PublicationServer'."
This message is followed by another error message: "Another distribution agent for the subscription(s) is running.", although nothing was changed to the configuration and no SQL processes were started or restarted.
When we restart the distribution job manually it runs as if nothing happened.
Does anyone know what exactly caused the failure, and how to prevent it ?
Philip
View 1 Replies
View Related
Aug 21, 2007
Hello everybody,
I'm trying to create a merge between 2 tables, the first coming from Oracle and the second resident on SQL 2005. The result I'd like to retrieve would be a table composed from the original data contained in the SQL table, update with new or changed rows coming from Oracle (the 2 tables are identical, I'm building an update job..). The final table will be the same table used for data source.
I setup connections and everything seems to works, but when I link the Merge block with the 2 sources blocks, double clickin on the Merge Block, I notice that every SQL table's column is set to "ignore".
When I try to change it, SSiS says:
Failed to set property "OutputColumnLineageID" on input column ...
What's wrong? I've set IsSorted property to True and each column with the righ position...
I've also another question: actually my tables hasn't a "last update" column, so I think the merge "block" can't just update the SQL existing rows but it will add only newest rows. Is that right? How can I easily implement an "update check" in your opinion?
Thanks in advance and sorry for my bad english
View 1 Replies
View Related
Jul 23, 2005
Hello there!I am working with MS SQL Server 2000.I have a table function that takes an integer parameter and returns atable, and I can successfully use it like this (passing a literalas a parameter):SELECT * FROM MyTableFunction(1)or like this (passing a variable as a parameter):DECLARE @i AS intSELECT @i = 10...SELECT * FROM MyTableFunction(@i)Now, if I have another function -- a scalar function that returns aninteger, I cannot find a way to use it to specify the parameter valuefor the first function. For example, when I writeSELECT * FROM MyTableFunction( dbo.MyScalarFunction() )SQL Server issues the following complaint:Incorrect syntax near '.'.I am really perplexed: what I am doing wrong?Interestingly, if I re-write the second snippet asDECLARE @i AS intSELECT @i = dbo.MyScalarFunction()...SELECT * FROM MyTableFunction(@i)everything works just fine; however, this trick cannot be used as aworkaround because I need to pass result of one function as a parameterto another inside a view's code -- I cannot declare variables and writeany procedural code...Any ideas, especially workarounds, would be greatly appreciated.Thank you,Yarik.
View 2 Replies
View Related
Sep 23, 2005
Hi all,
I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.
The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)
The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.
Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?
I want my SYMBOL tag to be the actual SYMBOL for the stock fund.
Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.
Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!
Thanks!
View 6 Replies
View Related
Sep 21, 2006
Hello,I am using SQL Server 2000 with SP4. I have a database with two fullbackups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30PM. Is there a possible way to do a point in time restore to 4:30 PM,that is between two full backups?When I try to use the transactional log backup that is taken at 5:30, Ican never specify a time before 5:00 PM. Is the transaction logtruncated at each full backup? If so, even if you take transactionallog backup every ten minutes, and full backups every once in a while,there will be some point in time which cannot be recovered to, namelythe time between a transactional log backup and a full backup. Take alog backup at 4:50, and full backup at 5:00 and you can never recoverto 4:55, can you?Any insight on the topic will be appreciated,Regards,M. Baris Caglar
View 3 Replies
View Related
Aug 31, 2006
I hope anyone can help me with this. I am having problems connecting to the aspnet database. It
all started when I noticed that I could not log in to my webapplication
anymore under debug mode, although I was able to log in when remotely
accessing the website. I got the error message that the machineaspnet
user did not have access to the database: "Login failed for user
'GM-FEDORSASPNET' "This was highly peculiar and rather annoying so I tried several things to straighten this out, but it only made things worse!I
read that this had to with permissions of the database in question, al
though I could not understand why permissions suddenly had changed. I
tried to change the rights by attaching it to Management Studio Express
and defining an aspnet login, but that failed because a login under
that name was not accepted. I tried to make a copy of aspnet.mdf, delete the original and rename the copy to aspnet.mdf. When that didn't work, I then deleted the aspnet.mdf, counting on it that it would be regenerated automatically, but alas. I located the aspnet_regsql.exe tool to regenerate the database, but got the following error message: System.Web.HttpException:
Unable to connect to SQL Server database. --->
System.Data.SqlClient.SqlException: An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)What can I do?
View 4 Replies
View Related
Aug 17, 2007
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View 9 Replies
View Related
May 13, 2008
Hi There,
I have a database which I attached to Sql Server 2005 Express (With advanced features) and enabled full text search. I created a FT catalog and FT index on a column and can successfully run the following query:SELECT expertise
FROM faculty_user
WHERE FREETEXT (expertise, 'Berry' )My issue is that full text search requires user instances to be turned off. Simply setting "User Instance=False" over in my web.config's connection string (in VWD) does not solve the problem.I still get the following error and I think it may be caused by my ASPNETDB. I can't find my connection string to ASPNETDB to turn off user instances and I can't diagnose my other connection strings because of it.Generating user instances in SQL Server is disabled
Here is a sample of my other connection strings:
<add name="myConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabase.mdf;Integrated Security=True;User Instance=False" providerName="System.Data.SqlClient"/>
From what I understand, I should also not be attaching the database from my connection string like this because it relys on User Instances? I am officially confused! Thanks for reading, -Derek
View 1 Replies
View Related
Mar 7, 2008
I am using the xp_cmdshell to run a batch file that is trying to open the Adobe Acrobat Reader.
When I run the batch file in the command prompt, it opens perfectly.
When I run it in SQL with xp_cmdshell, it returns the contents fine, and I notice it tries to open Acrobat, but it is under the username "system". And I cannot get it to load in a visible area, and for some reason it doesnt seem to load properly under system.
Any ideas? Any Suggestions?
Please advise.
Marco
View 3 Replies
View Related
Jul 24, 2007
When attempting to use the FTP task to download a file from a Mainframe system the task fails stating the filename as invalid because it doesn't begin with a "/".
Adding the slash to the front of the file name causes the mainframe to be unable to locate the file.
Commandline version of FTP.exe mimicks this behavior by working perfectly when the filename has no slash, and being unable to find the file when the slash is present.
Why does microsoft force a filename to start with a "/" and is there a way to make SSIS skip the validation phase for the FTP task?
View 7 Replies
View Related
Mar 21, 2007
Relative newb to SSRS here, but the answer to this question evades me; answers and insight are appreciated.
Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements.
This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope.
Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.
So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.
Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.
I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.
Among what I've tried:
--Page footer (can't; databound)
--Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?).
--Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.
I may be having to explain to my client that you can't get there from here, and they may have to redesign their report. Does anyone have any insight?
Thank you for your time in reading this.
View 14 Replies
View Related
Mar 4, 2008
Hello,
SQL Server Reporting Services 2005 (SSRS) is installed on Vista Ultimate.
I cannot access to the http://localhost/ReportServer page. IE posts an internal error HTTP 500.0 (after IE required a Windows account).
All the configurations of Reporting Services are OK. In the Log file, there is nothing: the Web Services does not seem to be launched.
By continuing the investigations in IIS 7 (activation of the €śRules of follow-up of the requests having failed€?, I found the error following:
IIS Trace Detail Highlights
No. EventName Details Time
53. MODULE_SET_RESPONSE_ERROR_STATUS Warning
ModuleName="IsapiModule", Notification="EXECUTE_REQUEST_HANDLER", HttpStatus="500", HttpReason="Internal Server Error", HttpSubStatus="0", ErrorCode="Syntaxe du nom de fichier, de répertoire ou de volume incorrecte. (0x8007007b)", ConfigExceptionInfo="" 09:02:33.716
Translation of the french sentence: ErrorCode="Incorrect syntax of the file name, the directory or the volume."
I made a full research on Internet and in the documentation at my disposal and I did not find anything which can advance me towards a beginning of solution€¦
Thank you with all for the assistance which you will be able to bring to me.
View 4 Replies
View Related
Jan 29, 2008
Hye all, running WHS 2008, I try to install SQL Server 2005 with reporting services, but on install, in "components to install" wizard, the reporting services box is hided. I can't check this box :/ Do you know whats wrong ? if anyone knows the problem, thanks in advance.
View 1 Replies
View Related
Oct 1, 2007
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
View 2 Replies
View Related
Jan 7, 2008
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
View 2 Replies
View Related
Mar 18, 2008
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
View 2 Replies
View Related
Apr 21, 2005
Hi All
Can anyone tell me what this simple SQL query would be:
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
Many thanks
accelerator
View 2 Replies
View Related
May 15, 2005
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
View 4 Replies
View Related
May 28, 2006
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on:
I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD.
When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?
View 2 Replies
View Related
