Multiples In Select Query
Mar 1, 2006
I'm trying to select DISTINCT records, but I keep getting multiple records returned even though I used DISTINCT. The returned records are below the query. I ommitted a couple end columns for ease of reference. Thanks :)
_________________________________________________________________
SELECT distinct USERS.UserID, miaminewspaper.PropertyPrice, convert(datetime,miaminewspaper.FirstInsertDate), dateadd(day, 30, miaminewspaper.FirstInsertDate)as EndDate, 1, convert (int,AdNumber) as Classified, 0
FROM USERS RIGHT OUTER JOIN
miaminewspaperON USERS.EmailAddress = miaminewspaper.AdvertiserEmail
WHERE miaminewspaper.validAD=1
------ Returns -------- Notice UserID repeats for 1 and 2!!
UserID Property Price End Date
----------- ---------- ---------
1 175600.0000 2006-03-15
1 550500.0000 2006-03-15
2 175600.0000 2006-03-15
2 550500.0000 2006-03-15
3 150000.0000 2006-03-15
4 256666.0000 2006-03-15
(6 row(s) affected)
View 2 Replies
ADVERTISEMENT
Dec 8, 2004
Is it possible to write a single select statement that would pull the following from a table named tblPayments.
I need the 'first payment date and amount', 'last payment date and amount', 'largest payment date and amount', and 'total payments' for each client by a date range.
tblPayments
ClientID
PaymentDate
PaymentAmount
Thanks for any suggestions.
View 13 Replies
View Related
May 7, 2015
How do I select where two columns are the same, but the remaining columns might be different? For example, if I have 4 columns: First, Last, Class, and Year. I want a listing of First, Last, Class and Year but only if the same First, Last has > 1 row (ie that the same person is in the table twice.)
View 5 Replies
View Related
Feb 8, 2007
hi,
a simple question. I have more that 100 table (132 just). one per month from 1997.
Example:
H0_CONTABILIDAD_199701
H0_CONTABILIDAD_199702
...
H0_CONTABILIDAD_200701
H0_CONTABILIDAD_200702
..
.
.
H0_CONTABILIDAD_200712
Now, how I update all tables with a stantement, sp, etc. Each table have 500k of rows aprox (500.000)
the idea is:
update <table>
set myField = 'newValue'
TIA
View 5 Replies
View Related
Oct 3, 2007
Hi,
I'am begining with sql server 2005 and on my server, i have already 3 sql server instances which have been created.
I want to create a new instance an don't know how to add an instance ?
Can anyone help me ?
Thanks in advance
View 3 Replies
View Related
Feb 12, 2007
HiIs it possible to return the results of a query so that instead ofhaving say 10 rows its concatenated, egMy query returns 'M' 10 times, can this be returned as 'M M M M M M MM M M'?ThanksLee
View 3 Replies
View Related
May 21, 2008
I have 3x6 textbox lay out, QTY, Descp, Cost, with submit button with followin code...., insert function has too many function, anyone know how to work thiis type of data entery?? Thanks in advanc.Dim Quntityx() As Integer = {txtQ1.Text, txtQ2.Text, txtQ3.Text, txtQ4.Text, txtQ5.Text, txtQ6.Text}
Dim xDex() As String = {txtD1.Text, txtD2.Text, txtD3.Text, txtD4.Text, txtD5.Text, txtD6.Text}Dim xcost() As Decimal = {txtC1.Text, txtC2.Text, txtC3.Text, txtC4.Text, txtC5.Text, txtC6.Text}
Dim i As IntegerFor i = 0 To 5
itemInput.InsertParameters.Add("RequestN", RequestN)itemInput.InsertParameters.Add("Description", xDex(i))
itemInput.InsertParameters.Add("Cost", xcost(i))itemInput.InsertParameters.Add("Quantity", Quntityx(i))
itemInput.Insert(i)
Next i
View 5 Replies
View Related
Apr 11, 2007
Hello,
I've written this stored proc, which have to collect records in one table in several databases. These tables contains very few records (1 to 10 max). In SQL Server standard edition, this stored proc is executed instantaneously. But in the Express, it could take 1 minute.
Is there a solution to this problem ?
Thanks in advance,
Best regards,
Guy
ALTER procedure [dbo].[SP_CHECK_USERS]
AS
select 'FIDUCIAIRE' as code,moment,nom,machine from MERCATORFIDUCIAIRE.dbo.sessions s1 where (s1.inactif=0) and (s1.nom<>'MercatorIshop')
union select 'TEST1 ' as code,moment,nom,machine from MERCATORTEST1.dbo.sessions s2 where (s2.inactif=0) and (s2.nom<>'MercatorIshop')
union select 'TEST10 ' as code,moment,nom,machine from MERCATORTEST10.dbo.sessions s3 where (s3.inactif=0) and (s3.nom<>'MercatorIshop')
union select 'TEST11 ' as code,moment,nom,machine from MERCATORTEST11.dbo.sessions s4 where (s4.inactif=0) and (s4.nom<>'MercatorIshop')
union select 'TEST12 ' as code,moment,nom,machine from MERCATORTEST12.dbo.sessions s5 where (s5.inactif=0) and (s5.nom<>'MercatorIshop')
union select 'TEST13 ' as code,moment,nom,machine from MERCATORTEST13.dbo.sessions s6 where (s6.inactif=0) and (s6.nom<>'MercatorIshop')
union select 'TEST14 ' as code,moment,nom,machine from MERCATORTEST14.dbo.sessions s7 where (s7.inactif=0) and (s7.nom<>'MercatorIshop')
union select 'TEST15 ' as code,moment,nom,machine from MERCATORTEST15.dbo.sessions s8 where (s8.inactif=0) and (s8.nom<>'MercatorIshop')
union select 'TEST16 ' as code,moment,nom,machine from MERCATORTEST16.dbo.sessions s9 where (s9.inactif=0) and (s9.nom<>'MercatorIshop')
union select 'TEST17 ' as code,moment,nom,machine from MERCATORTEST17.dbo.sessions s10 where (s10.inactif=0) and (s10.nom<>'MercatorIshop')
union select 'TEST18 ' as code,moment,nom,machine from MERCATORTEST18.dbo.sessions s11 where (s11.inactif=0) and (s11.nom<>'MercatorIshop')
union select 'TEST2 ' as code,moment,nom,machine from MERCATORTEST2.dbo.sessions s12 where (s12.inactif=0) and (s12.nom<>'MercatorIshop')
union select 'TEST20 ' as code,moment,nom,machine from MERCATORTEST20.dbo.sessions s13 where (s13.inactif=0) and (s13.nom<>'MercatorIshop')
union select 'TEST21 ' as code,moment,nom,machine from MERCATORTEST21.dbo.sessions s14 where (s14.inactif=0) and (s14.nom<>'MercatorIshop')
union select 'TEST23 ' as code,moment,nom,machine from MERCATORTEST23.dbo.sessions s15 where (s15.inactif=0) and (s15.nom<>'MercatorIshop')
union select 'TEST3 ' as code,moment,nom,machine from MERCATORTEST3.dbo.sessions s16 where (s16.inactif=0) and (s16.nom<>'MercatorIshop')
union select 'TEST4 ' as code,moment,nom,machine from MERCATORTEST4.dbo.sessions s17 where (s17.inactif=0) and (s17.nom<>'MercatorIshop')
union select 'TEST5 ' as code,moment,nom,machine from MERCATORTEST5.dbo.sessions s18 where (s18.inactif=0) and (s18.nom<>'MercatorIshop')
union select 'TEST6 ' as code,moment,nom,machine from MERCATORTEST6.dbo.sessions s19 where (s19.inactif=0) and (s19.nom<>'MercatorIshop')
union select 'TEST7 ' as code,moment,nom,machine from MERCATORTEST7.dbo.sessions s20 where (s20.inactif=0) and (s20.nom<>'MercatorIshop')
union select 'TEST8 ' as code,moment,nom,machine from MERCATORTEST8.dbo.sessions s21 where (s21.inactif=0) and (s21.nom<>'MercatorIshop')
union select 'TEST9 ' as code,moment,nom,machine from MERCATORTEST9.dbo.sessions s22 where (s22.inactif=0) and (s22.nom<>'MercatorIshop')
View 2 Replies
View Related
Oct 28, 2015
I have the following results:
CustomerProductName ValueChargesNewCharges
13AZ 40005056
13BY 30023
13BX 50003536
13BW 16001312
13BV 107009392
13BU 7000109110
And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.
View 9 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
Sep 6, 2006
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
View 4 Replies
View Related
Nov 13, 2015
I need to create SQL to convert multiple rows data to single row for given subscriber#. Below is the example. In below example , I've 4 family members with same subscriber # and each members have separate rows, I want to combine member data for same subscriber in 1 row, so there would be a 1 row for each subscriber.
View 6 Replies
View Related
Jul 9, 2002
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
View 1 Replies
View Related
Aug 22, 2006
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
please help
mustfa
View 6 Replies
View Related
Jul 23, 2005
Hi,Is it possible to install on a Windows server 2003,SQL SERVER 2000 in the folowing configuration :SQL server 7.0 as a default InstanceandSQL server 2000 as a named instance.Thanks for your answer.
View 1 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jun 26, 2015
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
View 4 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
View 4 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
Oct 29, 1998
hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..
select * from authors
OR
create procedure authors as
select * from authors
lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input
Ali
View 1 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Jan 28, 2008
Hi,
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005.
I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC
Whose result set is as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
On modifying the query to:-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC
I get the result set as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
I want the result set as follows:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above.
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
View 7 Replies
View Related
Nov 21, 2006
Hi,
I have been having problems trying to sort this query out and would appreciate some pointers.
I have a SQL Server table 'Match' which includes the columns:
match_date datetime
age_group int
The match_date column includes both date and time values. The age_group column is an id linked to an 'Age_Group' table.
I would like the select query to retrieve the top 3 dates (not including times) and the agegroups for these dates.
For example:
The table data might include
match_date, agegroup
--------------------
10/11/2006 10:00, 1
10/11/2006 11:00, 1
10/11/2006 12:00, 1
10/11/2006 13:00, 2
03/11/2006 09:00, 3
03/11/2006 10:00, 4
03/11/2006 11:00, 5
25/10/2006 10:00, 2
20/09/2006 10:00, 5
20/09/2006 10:00, 6
I would like the SELECT to retrieve like so:
match_date, agegroup
--------------------
10/11/2006, 1
10/11/2006, 2
03/11/2006, 3
03/11/2006, 4
03/11/2006, 5
25/10/2006, 2
So the query only returns the one record for each age_group on each date for the top 3 dates.
Does anyone have any ideas? I hope I have explained it well enough!
Thanks,
Pete
View 2 Replies
View Related
Feb 27, 2007
Hi, I'm trying to do the following select statement.
I have the following databases with the following tables and columns which will be involved with the query.
DataBase Name: usersTable: UsersColumn: UserNameColumn: UserId
DataBase Name: documentareasTable: document_areaColumn: doc_area_idColumn: doc_area_nameColumn: doc_area_typeColumn: doc_area_defaultTable: document_area_user_accessColumn: doc_area (contains a doc_area_id)Column: user_id (contains a username)
Now I want to select the doc_area_name where the logged in username exists in the user_id of the document_area_user_access table.
So to clarify, I am looking to look through the document_area_user_access for each time the current users username exists and for each existing username the doc_area_name is displayed depending on what doc_area(s) is associated with the user_id in document_area_user_access.
Don't know if this has made any sense??
I am hoping to do all this in the aspx sqlsource if possible. As for each userarea that the user has access a link will exist.
Cheers, Mark
View 5 Replies
View Related
Apr 3, 2007
Hello,I have the following tables:[Blogs] > BlogId (PK), ...[Posts] > PostId (PK), BlogId (FK), Title, Content, ...[Labels] > LabelId (PK), LabelName[LabelsInPosts] > LabelId (PK), PostId(PK)I am selecting all posts from a Blog given the BlogId:SELECT * FROM dbo.Posts WHERE BlogId = @BlogIdThis will return the columns Title, Content, ... for those posts.I would like to add 2 columns, PostLabels and LabelsCount:1. PostLabels would be created by:For each post select all the labels associated with it inLabelsInPosts. Then for each label get the label name. Then createthe value of PostLabels which would be each label separated by acomma.I tried the following but this is not working. I am having problems getting the LabelId from LabelsInPosts and then getting the LabelName from Labels and use them to create the CSV string:SELECT DISTINCT l1.PostID, STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + l2.LabelName FROM Labels AS l2 WHERE l2.PostID = l1.PostID ORDER BY ',' + l2.LabelName FOR XML PATH('')), 1, 1, '') AS PostLabelsFROM Labels AS l1ORDER BY l1.PostID2. LabelsCount would be the number of labels associated with each postI created a procedure which, given a PostId, returns the number oflabels associated with it.So I suppose it would be only a question of integrating a call tothis procedure with this SELECT I am trying to create.Thank You,Miguel
View 2 Replies
View Related
Apr 11, 2007
I need to query the DB and only see if the 1st 5 characters of a Guid match. How would i go about only calling a certain amount of characters from a Guid?
View 11 Replies
View Related
Aug 26, 2007
i have a table which has a colum name's Name, Album.
the way data is storing is for
Name Album
'123' 'xyz'
'4555' 'xyz'
''212' 'xyz'
'33sa' 'abc'
'nyz1' 'abc' and so on.
what would be the query for selecting all the Album's one row only.
like when i write the query i want it to return like this.
'xyz'
'abc'
View 2 Replies
View Related
Apr 12, 2008
I have a table as below after executing select query select userid as userid,count as totalcount from table1
UserId totalcount
101 15
102 7
105 6
106 4
108 3
i want a extra column so that it is an autoincrement and the query result should be like
UserId totalcount Sno
101 15 1
102 7 2
105 6 3
106 4 4
View 1 Replies
View Related
Apr 25, 2008
hi friends...... i have table t1 task startdate enddate 1 03/04/2008 10/04/2008 2 04/04/2008 10/04/20083 06/04/2008 25/04/2008 i need a query to get task id and column2column2 = if(enddate > getdate()) then 1 else 2 give me the answer without the use of cursors......answer should be in a query...thanks in advance
View 5 Replies
View Related
Apr 28, 2008
I am writing to learn if it is possible to obtain a value from a select query, and utilize this value in a succeeding query.
Please see below for my current query. I would like to take the filenum result, and use the value in a second query.
Thanks in advance!
select shhd.CUST_NO, shhd.file_no as filenum from tpsdba.shipment_header SHHD WHERE SHHD.CUST_NO = '052584 ' AND (SHHD.DATE_ENTRY >= '20080401' AND SHHD.DATE_ENTRY <= '20080404')
View 2 Replies
View Related
May 10, 2008
im trying to get a page to display this query like this in a data grid
SELECT TOP @rows * FROM students
where student_id = @studentID
Basically I want code that returns the top X number of rows, where X is a user defined variable (like in a textbox)... I've been trying to find a way to get this to work and have not and any luck... any ideas?
thanks!
View 1 Replies
View Related
Jun 19, 2008
Hello All
I havetable with 2 columns and Datas like this
LRNo LRStatus
L001 10
L002 10
L003 10
L001 15
L002 15
L001 20
i need a query to select the LRNo where lrstatus =10 and not equal to 15 and 20
here is my query
select lrno from tbl where lrstatus in (10) and not in (15.20)
but i didnt get it
Please help me
Thanks in Advance
View 6 Replies
View Related
