Having some trouble with this select statement...

Similar Threads:

1.Having trouble with this create table statement

hi

sql server 2005

When I insert a row into this table I get this error:
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint

the idea of having this self referecning was to tell if a comment is a reply 
to another comment. A comment doesn't need to be a reply to anther comment.

CREATE table dbo.Comment(
 Id uniqueidentifier NOT NULL,
 UserId uniqueidentifier NOT NULL,
 CommentId uniqueidentifier NULL,
 RegDate datetime NOT NULL CONSTRAINT DF_CommentDate  DEFAULT (getdate()),
 Body nvarchar(1000),
 CONSTRAINT PK_Comment PRIMARY KEY CLUSTERED (id ASC),
 CONSTRAINT FK_Comment_Aspnet_Users FOREIGN KEY(UserId) REFERENCES 
dbo.aspnet_Users (UserId),
 CONSTRAINT FK_Comment_Comment FOREIGN KEY(CommentId) REFERENCES 
dbo.Comment(Id)
)

any suggestion? 

2.Having trouble with SELECT UNION

I have created the following VIEWS in my database. (Sorry, they're quite
long, but I've reproduced them exactly below.) In a nutshell,
VMSGRECIPIENTSA and VMSGRECIPIENTSB return the same record structure.
VMSGRECIPIENTS simply pulls these two together via a UNION. (For brevity,
I'n going to call these VMRA, VMRB and VMR.)

A bit of history: Orignally, VMRA and VMRB did not exist. VMR was crafted as
a single union query with all the complexity of VMRA and VMRB UNIONed
together. However, I had performance problems. I began to split up the VIEW
to see which of the UNIONed SELECTS was causing the problem. Interestingly
enough, I found that each on its own ran like lightning. I also found that

SELECT * FROM VMRA UNION SELECT * FROM VMRB WHERE ... also ran like
lightning. OK, sez I. I'll play that game. I split the two unioned SELECT
statments into VMRA and VMRB and crafted VMR to simply UNION these together.
Sounds nice, but it just doesn't work out that way.

At this point SELECT * FROM VMRA UNION SELECT * FROM VMRB WHERE ... runs
like lightning. (As we already saw.) However, SELECT * FROM VMR WHERE ...
(same WHERE CLAUSE) takes forever to execute. It's as though in processing
VMR, the DBMS *first* UNIONs together all records qualified in the two
SELECTs (what a dog!) and only *then* applies the WHERE clause to select the
qualifying records from the UNIONed recordset which it has created. This
just won't fly. When retrieving from a VIEW, shouldn't the WHERE criteria be
applied as early on as possible?

DISCLAIMER: I am not using SQL Server at the moment, I'm using Interbase. So
how can you help me? Well first of all, if you're familiar with this sort of
thing and you can tell me, 'yeah dummy, that's how it works ;-)' well,
that'll be a big help to me. On the other hand, if you can tell me that SQL
Server would handle this just fine, and that it's Interbase which is
brain-dead, well I might not have a solution, but I'll feel just that much
better for crafting this UNION in this first place.

So in the final analysis, what is the practical application for UNION? Is
UNION intended specifically for submitted SELECT statements (which can
contain qualifying selection criteria) as opposed to for VIEWS (which don't
contain qualifying selection criteria). Is UNION not suitable for VIEW
definitions?

(VIEW definitions presented below.)

Thanks very much for your help!

- Joseph Geretz -

CREATE VIEW "VMSGRECIPIENTSA" (
  "MSGID",
  "MSGTYPE",
  "MSGSTATUS",
  "OPENEDTIME",
  "SEALEDTIME",
  "PERSONID",
  "SHAREDID",
  "ATTACHMENTS",
  "ATTACHMENTTYPE",
  "ROUTINGID",
  "ROUTINGTYPE",
  "RESPONSETOID",
  "FROMID",
  "SUBJECTID",
  "SUBJECT",
  "ADDLSUBJECT",
  "PRIORITYID",
  "PRIORITYCODE",
  "PRIORITYSORT",
  "MSGTEXT",
  "RECIPIDDIRECT",
  "ISPOOL",
  "RECIPIDPOOL",
  "ORIGPOOLID",
  "ISRESPONSIBLE",
  "ISPRIVATE",
  "ISRECEIPTREQ",
  "LASTACTION",
  "CREATETIME",
  "SCHEDTIME",
  "UPDATETIME"
) AS

SELECT
  MSGMASTER.MSGID,
  MSGMASTER.MSGTYPE,
  MSGMASTER.MSGSTATUS,
  MSGMASTER.CREATETIME,
  MSGMASTER.SEALEDTIME,
  MSGMASTER.PERSONID,
  PERSON.SHAREDID,
  VMSGATTACHMENTS.ATTACHMENTS,
  DOCUMENTS.DOCUMENTTYPEID,
  MSGROUTING.ROUTINGID,
  MSGROUTING.ROUTINGTYPE,
  MSGROUTING.RESPONSETOID,
  MSGROUTING.USERSECURITYID,
  MSGROUTING.SUBJECTID,
  MSGSUBJECTS.SUBJECT,
  MSGROUTING.ADDLSUBJECT,
  MSGROUTING.PRIORITYID,
  MSGPRIORITIES.PRIORITYCODE,
  MSGPRIORITIES.SORTSEQ,
  MSGROUTING.MSGTEXT,
  MSGRECIPIENTS.ENTITYID,
  USERGROUPS.ISPOOL,
  GROUPMEMBERSHIP.USERSECURITYID,
  MSGRECIPIENTS.ORIGGROUPID,
  MSGRECIPIENTS.ISRESPONSIBLE,
  MSGRECIPIENTS.ISPRIVATE,
  MSGRECIPIENTS.ISRECEIPTREQ,
  MSGRECIPIENTS.LASTACTION,
  MSGROUTING.CREATETIME,
  MSGRECIPIENTS.SCHEDTIME,
  MSGRECIPIENTS.UPDATETIME
FROM
  MSGMASTER
  LEFT JOIN PERSON ON (MSGMASTER.PERSONID = PERSON.PERSONID)
  LEFT JOIN MSGROUTING ON (MSGROUTING.MSGID = MSGMASTER.MSGID)
  LEFT JOIN MSGRECIPIENTS ON (MSGRECIPIENTS.MSGID = MSGROUTING.MSGID) AND
(MSGRECIPIENTS.ROUTINGID =

MSGROUTING.ROUTINGID)
  LEFT JOIN USERGROUPS ON (USERGROUPS.GROUPID = MSGRECIPIENTS.ENTITYID)
  LEFT JOIN GROUPMEMBERSHIP ON (GROUPMEMBERSHIP.GROUPID =
USERGROUPS.GROUPID)
  LEFT JOIN MSGPRIORITIES ON (MSGROUTING.PRIORITYID =
MSGPRIORITIES.PRIORITYID)
  LEFT JOIN MSGSUBJECTS ON (MSGROUTING.SUBJECTID = MSGSUBJECTS.SUBJECTID)
  LEFT JOIN VMSGATTACHMENTS ON (MSGROUTING.MSGID = VMSGATTACHMENTS.MSGID)
  LEFT JOIN  DOCUMENTS ON (VMSGATTACHMENTS.ATTACHMENTS =
DOCUMENTS.DOCUMENTID)
WHERE (MSGROUTING.SUBJECTID <> 0)
;
COMMIT WORK;

/* View: VMSGRECIPIENTSB, Owner: SRSSOFTWARE */

CREATE VIEW "VMSGRECIPIENTSB" (
  "MSGID",
  "MSGTYPE",
  "MSGSTATUS",
  "OPENEDTIME",
  "SEALEDTIME",
  "PERSONID",
  "SHAREDID",
  "ATTACHMENTS",
  "ATTACHMENTTYPE",
  "ROUTINGID",
  "ROUTINGTYPE",
  "RESPONSETOID",
  "FROMID",
  "SUBJECTID",
  "SUBJECT",
  "ADDLSUBJECT",
  "PRIORITYID",
  "PRIORITYCODE",
  "PRIORITYSORT",
  "MSGTEXT",
  "RECIPIDDIRECT",
  "ISPOOL",
  "RECIPIDPOOL",
  "ORIGPOOLID",
  "ISRESPONSIBLE",
  "ISPRIVATE",
  "ISRECEIPTREQ",
  "LASTACTION",
  "CREATETIME",
  "SCHEDTIME",
  "UPDATETIME"
) AS

SELECT
  MSGMASTER.MSGID,
  MSGMASTER.MSGTYPE,
  MSGMASTER.MSGSTATUS,
  MSGMASTER.CREATETIME,
  MSGMASTER.SEALEDTIME,
  MSGMASTER.PERSONID,
  PERSON.SHAREDID,
  VMSGATTACHMENTS.ATTACHMENTS,
  DOCUMENTS.DOCUMENTTYPEID,
  MSGROUTING.ROUTINGID,
  MSGROUTING.ROUTINGTYPE,
  MSGROUTING.RESPONSETOID,
  MSGROUTING.USERSECURITYID,
  MSGROUTING.SUBJECTID,
  MSGROUTING.ADDLSUBJECT,
  MSGROUTING.ADDLSUBJECT,
  MSGROUTING.PRIORITYID,
  MSGPRIORITIES.PRIORITYCODE,
  MSGPRIORITIES.SORTSEQ,
  MSGROUTING.MSGTEXT,
  MSGRECIPIENTS.ENTITYID,
  USERGROUPS.ISPOOL,
  GROUPMEMBERSHIP.USERSECURITYID,
  MSGRECIPIENTS.ORIGGROUPID,
  MSGRECIPIENTS.ISRESPONSIBLE,
  MSGRECIPIENTS.ISPRIVATE,
  MSGRECIPIENTS.ISRECEIPTREQ,
  MSGRECIPIENTS.LASTACTION,
  MSGROUTING.CREATETIME,
  MSGRECIPIENTS.SCHEDTIME,
  MSGRECIPIENTS.UPDATETIME
FROM
  MSGMASTER
  LEFT JOIN PERSON ON (MSGMASTER.PERSONID = PERSON.PERSONID)
  LEFT JOIN MSGROUTING ON (MSGROUTING.MSGID = MSGMASTER.MSGID)
  LEFT JOIN MSGRECIPIENTS ON (MSGRECIPIENTS.MSGID = MSGROUTING.MSGID) AND
(MSGRECIPIENTS.ROUTINGID =

MSGROUTING.ROUTINGID)
  LEFT JOIN USERGROUPS ON (USERGROUPS.GROUPID = MSGRECIPIENTS.ENTITYID)
  LEFT JOIN GROUPMEMBERSHIP ON (GROUPMEMBERSHIP.GROUPID =
USERGROUPS.GROUPID)
  LEFT JOIN MSGPRIORITIES ON (MSGROUTING.PRIORITYID =
MSGPRIORITIES.PRIORITYID)
  LEFT JOIN MSGSUBJECTS ON (MSGROUTING.SUBJECTID = MSGSUBJECTS.SUBJECTID)
  LEFT JOIN VMSGATTACHMENTS ON (MSGROUTING.MSGID = VMSGATTACHMENTS.MSGID)
  LEFT JOIN  DOCUMENTS ON (VMSGATTACHMENTS.ATTACHMENTS =
DOCUMENTS.DOCUMENTID)
WHERE (MSGROUTING.SUBJECTID = 0)
;
COMMIT WORK;

/* View: VMSGRECIPIENTS, Owner: SRSSOFTWARE */

CREATE VIEW "VMSGRECIPIENTS" (
  "MSGID",
  "MSGTYPE",
  "MSGSTATUS",
  "OPENEDTIME",
  "SEALEDTIME",
  "PERSONID",
  "SHAREDID",
  "ATTACHMENTS",
  "ATTACHMENTTYPE",
  "ROUTINGID",
  "ROUTINGTYPE",
  "RESPONSETOID",
  "FROMID",
  "SUBJECTID",
  "SUBJECT",
  "ADDLSUBJECT",
  "PRIORITYID",
  "PRIORITYCODE",
  "PRIORITYSORT",
  "MSGTEXT",
  "RECIPIDDIRECT",
  "ISPOOL",
  "RECIPIDPOOL",
  "ORIGPOOLID",
  "ISRESPONSIBLE",
  "ISPRIVATE",
  "ISRECEIPTREQ",
  "LASTACTION",
  "CREATETIME",
  "SCHEDTIME",
  "UPDATETIME"
) AS

SELECT * FROM VMSGRECIPIENTSA
   UNION
SELECT * FROM VMSGRECIPIENTSB
;
COMMIT WORK;

3.having problem in subquery in select statement

Hi,
i have a query which run successfully on SQL Server but giving error in
SQLCE.
The Query is:

SELECT a.Ad_Surr_Key, a.Ad_Description, count(*) as WO_Count,
(select count(*) from tbl_WORK_ORDER_HEADER wx, tbl_WORK_ORDER_DETAIL
wdx
where wx.WO_Hdr_Surr_Key = wdx.WO_Hdr_Surr_Key and wdx.Ad_Surr_Key =
a.Ad_Surr_Key and
(WO_Status = '0' OR WO_Status = '2')) as Count_Inc from
 tbl_WORK_ORDER_HEADER w, tbl_WORK_ORDER_DETAIL wd, tbl_UNIT_MASTER u,
 tbl_AD_MASTER a, tbl_CREW_MASTER c, tbl_ROUTE_MASTER r where
w.WO_Hdr_Surr_Key = wd.WO_Hdr_Surr_Key and wd.Ad_Surr_Key =
a.Ad_Surr_Key and
 wd.Assigned_Crew_Surr_Key = c.Crew_Surr_Key and c.Crew_Surr_Key = '1'
and
 w.Unit_Surr_Key = u.Unit_Surr_Key and u.Route_Surr_Key =
r.Route_Surr_Key and
r.Route_Surr_Key = '2'
group by a.Ad_Surr_Key, a.Ad_Description order by a.Ad_Description

can anyone have solution.

4.Trouble with SELECT ... INTO SQL statement

Hi all.

I'm trying to use some VBA to browse through the Tables of my Access DB and 
to create Back-ups in a separate backup Access file for the ones i want.

To achieve the latter, i'm trying to use an SQL SELECT ... INTO statement 
identical to the one that's being created if i design a Make-Table Query.

If i design and run the Query, it's being executed ok and the Table i 
include in the Query design is indeed created in the backup Access file.
However, if i try to use the Query's SQL statement to execute the same 
action via VBA, it fails.

Here's the related part of my VBA code:

strSQL = "SELECT [" & BackupTbls(iCnt) & "].* " _
            & "INTO [" & BackupTbls(iCnt) & "] IN '" & strBackupDB & "' " _
            & "FROM [" & BackupTbls(iCnt) & "]"
CurrentDb.Connection.Execute strSQL

Notes:
- BackupTbls() is a String type array containing the Tables i want to backup
- strBackupDB is the full name of the backup Access file (Path + Name)
- The strSQL string is IDENTICAL to the one used by the Access Query (except 
for the ';' in the end in the Query string)

The error message im getting is:

Run-time error '3251'
Operation is not supported for this type of object.

This appears only when trying to execute via VBA, the normal Access Query 
(with the identical SQL string) executes ok.

I'd be grateful to have any suggestions on this.

Kind regards,
Alex

5.Use results from a select statement in a follow-up select statement in a SP

Hi,

This is probably a simple question, but I'm new at this.  What I am
trying to do is use the results from one select statement in my final
select statement.  So basically the following:

select x from table_a where name = @inputname

select date, name from table_a where level like x + '%'

where the x is from select statement 1

Is this possible?

Thanks,
Todd

6. Need help w/ SELECT statement within a SELECT statement

7. Having trouble

8. Kinda new and having trouble with SQL stored procedure