MS SQL Server :: Select Query With Multiple Row Coalesce Subquery

Subquery With Multiple Rows Inside SELECT

Mar 6, 2008

Hi there,
I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results.
Here is my basic structure:

Table: Profiles
Fields: Id, ProfileName

Table: Groups
Fields: Id, GroupName, ProfileId

I then need to return something like this:

ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"

Does anyone know how this can be done?
Thanks!

View 7 Replies View Related

Insert Query With A Select Subquery

Apr 1, 2008

Hi.I have an insert query which inserts record that are returned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?

View 4 Replies View Related

Insert Query With A Select Subquery

Apr 1, 2008

Hi.
I have an insert query which inserts record that are rturned from a select subquery:

INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...

col1 and col2 in tbl1 combined ,are a unique index.

So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.

The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).
How can I solve this ?

View 6 Replies View Related

Trying To Use COALESCE/ISNULL With CAST In Select Statement

Aug 27, 2007

Hello Everyone,

I'm new to SQL within the past year and am having quite a bit of difficulty trying to replace NULL in a column cast as smalldatetime. What i'd like to do is return the value "EMPLOYEED" within any record containing a null value, obviously I need to CAST the column to a varchar but when I try and do this I keep getting errors. I'm trying hard to find the answer online but the only examples I get are is for money and i'm not sure i'm completely understanding how this is supposed to work. I've also tried using the ISNULL function as well - getting the same errors in this case (though i'm using ISNULL successfully in another column).

It seems to me that I should be able to write the syntax as the following:

CAST(ISNULL(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate

----OR---

CAST(COALESCE(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate

I'm new enough that I could just be confusing myself, but I really seriously cannot find any good explanations or examples online for what I am trying to do.

Can some nice SQL expert provide assistance?

Thanks!!!!!

Ave

View 3 Replies View Related

SQL Server 2014 :: COALESCE To Replace Multiple CASE Statements

May 27, 2014

I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.

Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().

SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A

View 9 Replies View Related

Multiple Server Select Query

Mar 1, 2008

hi,i have three database servers with heterogeneous databases, and i have a sql server 2005 that should has a table that will be filled with records from the three servers every time period, so what would be the best technique to create this table with the scripts ????i used to use linked server + sql server agent jobs but usually for one linked server only, but this time i am afraid of the performance as there will be three linked servers, so i need ur suggestions.

View 2 Replies View Related

Multiple CTE In One SELECT Statement Query

May 8, 2014

I am trying to apply the logic from the following resource: URL....but cannot get it to work with my logic for some reason.For example, the following query:

;WITH CTE1 AS (SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Col1),
CTE2 AS (SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

Produces the following output:

Col1 | Col2
2014-05-08 10:55:54 | 2014/05/08

But, as soon as I try to do something else like:

;WITH CTE1 AS (SELECT COUNT(login) FROM userinfo AS Col1),
CTE2 AS (SELECT COUNT(login) FROM userinfo AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

I receive the following errors:

Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 1 of 'CTE1'.
Msg 8155, Level 16, State 2, Line 2
No column name was specified for column 1 of 'CTE2'.

Are there limitations when trying to use multiple CTE in a single query?

View 5 Replies View Related

Select Query Form Multiple Databases

Dec 14, 2007

How do I write an sql statement to link tables in two databases and query them? For example: SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN db1.table1.field1 ON db2.table1.field1 = db1.table1.field1

Thanks

View 6 Replies View Related

Faster Query - Select By One Or Multiple Fields

Feb 1, 2008

Let's say I have a table of users.
Let's imagine there's two fields:
username (PK),
password

Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to
(1) SELECT * FROM [User]
WHERE username = 'whatever' AND password='whatever'
or
(2) SELECT * FROM [User]
WHERE username = 'whatever'
and then in my code check that the record returned matched the password?

View 6 Replies View Related

Select Query - Multiple Constraints In One Field

Nov 6, 2007

Hi all,

I want to retrieve the datas from the table with condition if DetailID is not null and DetailID is not guid.empty then it return only the DetailID = @DetailID

can any one help on this.

Thanks,
Sajith

View 4 Replies View Related

Spreading SELECT Query Results Over Multiple Pages

Dec 20, 2006

Hello.Lets say that there is a table with N rows. now, i want to display the table's data on a web page. one way is to select the whole table and add each row's data to the webpage (something like SELECT * FROM TABLE1).going this way will create a huge page. i want to speared the results over multiple pages - excatly as this forum spread each forum messages over multiple pages.
for this purpose, i need to query Y rows each time, for example, if my table has 20 rows, and say that i want that each page will display 5 rows, then i need to query 5 rows each time. the first 5 rows for the first page, the next 5 rows for the second page and so on...
is there any way to achieve it using an SQL query?
the simplest way is to select the whole table and manaually filter the results. but this way will become slow as the table grows with data... and i dont want to select rows which i wont display anyway.
any suggestions?
Thanks.

View 5 Replies View Related

T-SQL (SS2K8) :: Select Query With Multiple Where Conditions And OR Operator

Feb 6, 2015

I have the got below T SQL query to fetch the matching record

DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int

[Code] .....

-- result - 'Orange'

This is an exact matching record and straight forward

Is it possible to identify the record using T SQL query based on the following scenarios

1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match

-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values

SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1

-- result - 'Orange'

View 4 Replies View Related

Select Query To Fetch Multiple Checkbox Items

Nov 15, 2013

I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.

Checkbox tag:
<asp:CheckBoxList ID="cblGroup" Style="vertical-align: baseline" runat="server" CssClass="chkbox">
</asp:CheckBoxList>

Select Query:SqlCommand cmd = new SqlCommand("select * from AppInvent_Test where Designation= '" + cblGroup.SelectedValue + "'", con);

View 2 Replies View Related

HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.

Aug 7, 2007

Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:

I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:

(AISC_Shapes_Table)

AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100

(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)

I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:

Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))

It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.

Regards,

Steve G.

View 4 Replies View Related

Query To Select Multiple Tables In Dataset Through ODBC Connection

Apr 10, 2007

Hi there,

I have a MS Access database (mdb) containing the following tables:

Crime

Criminal

CrimeCommitted

Hideout

CriminalType

The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:

Code Snippet

SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted
FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID
GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;

This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).

The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?

View 2 Replies View Related

Multiple Column SubQuery

May 14, 2008

I need to query out multuple rows of data from the following table and retrieve it as a single row.   Ideally, I'd like to do some sort of subquery that supports multiple columns, but as far as I know, that only exists on Oracle, not MSSQL.
Here's an example of the data:UserDefinedFieldId     UserDefinedRowID     FieldValue1                              1                              date (stored as text)2                              1                              text3                              1                              text1                              2                              date (stored as text)2                              2                              text3                              2                              text...27 UserDefinedFieldIds for each row in the actual table
I wrote a working query, but I need something more efficient for this particular application. (The query to do this must fit in a 2000char data field--with all 27columns brought in with separate subqueries, I'm pushing 5000.) Already tried shorter table names, which only saved me about a 1000 characters.
SELECT     (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable           WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId          AND SecondaryTable.UserDefinedFieldId = 1) AS Column1,     ,,,     (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable           WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId          AND SecondaryTable.UserDefinedFieldId = 27) AS Column27,FROM UserDefinedData AS PrimaryTableWHERE PrimaryTable.UserDefinedFieldId = 5 AND     Cast(Cast(PrimaryTable.FieldValue AS varchar(64)) AS datetime) > GetDate() ORDER BY PrimaryTable.UserDefinedRowID, PrimaryTable.UserDefinedFieldId
Any suggestions would be appreciated.
Thanks,
Brad

View 3 Replies View Related

How To Perform SELECT Query With Multiple Parameters In A Single Field In A Table

Sep 13, 2006

i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.

View 1 Replies View Related

Select Query Results In Multiple Columns Based On Type From Another Table

Apr 6, 2008

Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:

Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3

Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road

I'd like the results to be like this:

Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30

Anyone? Thanks in advance!

View 3 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Apr 26, 2008

hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Jul 20, 2005

I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View 3 Replies View Related

Running The Same Subquery Multiple Times

Mar 2, 2005

Hi,

I was wondering if this can be done...

I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?

E.g.
I have a table with 2 cols - amount, date.

SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)

Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.

As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?

This is only a basic example, what I'm trying to do will invovle a lot more calcultions.

Hope someone can help,
Thanks,
Stuart

View 5 Replies View Related

Combine Multiple Results Of Subquery

Nov 14, 2006

Table users:
userid, name, added...

Table groups
groupid, groupname...

Table groupadmins:
userid, groupid

The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'

If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users

then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?

View 4 Replies View Related

LIKE Using Subquery Returning Multiple Rows

Jan 30, 2008

This is probably very elementary to someone more experienced, but I'm having a hard time coming up with a way to do the following:

SELECT * FROM Transactions
WHERE MyField LIKE (SELECT MyValues FROM SearchValues)

But I can't because the subquery will return multiple rows. That's all I'm really trying to do - search all the rows in Transactions.MyField for any of the search values returned from the subquery.

And I can't restructure the SearchValues table to combine them into a single-row comma-delimited field or anything like that.

Any help would be appreciated-

Kenneth

View 11 Replies View Related

[RESOLVED] Multiple Records Subquery..

May 4, 2006

Hi everybody,

I like to display the records for AccountNo = 221 from table records shown below, how would I do this? I am display this results in a crystal report. What is sql statement to do this? Thanks.

Sql Statement: (this statement is not allowed)
Select AccountNo, RecordID, (Select Description From Table1 Where RecordID In (Select RecordID From Table2 Where Date < '04/05/2006')) As Description, Amount From Table2 Where Date < '04/05/2006'

Desired Result:

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Table 1

RecordID Description
20 Whew
21 Hi
21 There
21 Good Morning

Table 2
Date AccountNo RecordID Amount
04/02/2006 220 19 80.0
04/03/2006 221 20 290.0
04/04/2006 221 21 728.0
04/06/2006 223 23 200.3
04/07/2006 225 25 2893.20

den2005

View 3 Replies View Related

Select Without Subquery

Apr 3, 2008

Hi,

I have two tables. Customers and Orders.
Customers table contains customer id, and customer name columns.
Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.

I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought.
Can I write this sql without using a subquery.

Thanks,
Chamal.

View 6 Replies View Related

Select From Subquery

Jul 23, 2005

Hi.I'm new in SqlServer programing.Is it possible to do something like this ( It is common construction in oracle )Select X from(select a+1 as X from tab1)without creating view containig subquery ?thx. MD

View 2 Replies View Related

Select Subquery Returned More Than 1 Value

Dec 24, 2007

select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1
I need to write that kind of sql to return me single value a and multiple values b on each of lines, like
a b
----------------------------
tom small,big,hugh
But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!

View 4 Replies View Related

SQL Problem With Subquery In Select

Mar 22, 2008

1) Getting this error:
Msg 156, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10
Incorrect syntax near the keyword 'in'.
Msg 102, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10
Incorrect syntax near ','.
Msg 512, Level 16, State 1, Procedure SP_ImportHotels_Step4, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
2) I also coild not use subquery as myname... It did not allow to specify "as" for some reason...
Here is my procedure.

alter procedure SP_ImportHotels_Step4
As
BEGIN
INSERT INTO classifieds_Photos
( AdId, IsMainPreview, DateCreated, createdOn, createdBy, modifiedOn, modifiedBy, URL, ThumbnailURL )
SELECT classifieds_Ads.Id,
[i.ID] in (Select Min(Images.ID) from Images group by HotelID),
GetDate() AS Expr2, GetDate() AS Expr3, 'admin' AS Expr4, GetDate() AS Expr5, GetDate() AS Expr6, i.URL, i.ThumbnailURLFROM (classifieds_Ads
INNER JOIN classifieds_Address ON classifieds_Ads.LocationId = classifieds_Address.addressID)
INNER JOIN Images as i ON classifieds_Address.tempIONHotelID = i.HotelID;
END
go
execute SP_ImportHotels_Step4

View 15 Replies View Related

How Do I Select Tablename In Subquery

Feb 19, 2001

hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )

this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned in second

View 1 Replies View Related

How Do I Select Tablename In Subquery

Feb 19, 2001

hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column

note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help
*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )

this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned because max(id_no) column is absent

if you are run
select * from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2

this query return that

insqlhelp ---------------
aaa2

(1 row(s) affected)

this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")

other way is

set to any other text variable to table name,
and concetanate to query,
and execute with exec

but in this way you cant set to my int variable

please help me thanks for all

hra

View 1 Replies View Related

SELECT DISTINCT Subquery

Jul 4, 2006

I the following table:

table1
member_name legacy_id team_name
-----------------------------------------
Bill 1234 nationals
Bill 1234 nationals
Tom 3456 nationals
Tom 3456 orioles

I wish I could restructure the data or normalize it but this is unfortunately what I have to deal with.

I need a query that returns the team name and the number of times it appears in the table excluding duplicates for each person. I have duplicates all over the place in this tables. Bill could have nationals listed a couple hundred times.

My query should return

team_name count
-----------------
nationals 2 - because it occurs for bill, and tom
orioles 1 - because it occurs for tom

If I do something like:

select
distinct(team_name),
count(team_name)
from table1
group by team_name

I get back:

team_name count
-------------------
nationals 3 - because it occurs for bill twice, and tom once
orioles 1 - because it occurs for tom once

I've tried something like:

select
team_name,
count(team_name)
from
table1
where legacy_id in (
select distinct legacy_id from table1
)

I get a syntax error. Regardless, I'm not sure this will give me what I need.

I've tried over a dozen variations of select distinct, joins, etc but with no luck.

Any of you sql gurus know how to solve this problem? I've been banging my head against it for a couple days and boy does my head hurt.

View 4 Replies View Related

Subquery In SELECT Statement Before FROM

Nov 19, 2006

Hello!

I can use querys like these in Access:

SELECT Field1,
(SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1
SELECT Field1,
(SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1

But when I
try execute it with SQL Server Everywhere it says "Token in error
=
SELECT".

Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.

-Teemu

View 3 Replies View Related