query

Hi, I have a query to build a returned record as and order by Name:
ID, Name
5 A
1 B
3 C '?
4 D
2 E
Now, I want to add a parameter (for example ID=3) to the query (or somehow),
so the returned record only return:
ID, Name
4 D
2 E
How to do that? Thanks.Please post the text of your query, then I will have my worker bees make the
changes you request.|||Try,
select [id], [name]
from t1
where [id] > 3
order by [name]
AMB
"js" wrote:

> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or somehow
),
> so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Add a WHERE clause to your query. It is possible to create a stored
procedure which accepts a parameter. The stored procedure would return data
just as your query does.
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or
> somehow), so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Thanks...
Add a WHERE clause(where [id] > 3) to query will filter out (2, E)?
select [id], [name]
from t1
where [id] > 3
order by [name]
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O1uUovATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Add a WHERE clause to your query. It is possible to create a stored
> procedure which accepts a parameter. The stored procedure would return
> data just as your query does.
> --
> Keith
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||One solution would be:
Select T.Id, T.Name
From Table As T
Where Ascii(T.Name) > Ascii('C')
Another would be
Select T.Id, T.Name
From Table As T
Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
Of course, by doing it this way, you'll likely get an index or table scan.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or somehow
),
> so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Actually, SQL as I just discovered, SQL is smart enough to do this sort of
analysis on strings for you. So you should be able to do this:
Select T.Id, T.Name
From Table As T
Where T.Name > 'C'
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23CLAj9ATFHA.3036@.TK2MSFTNGP10.phx.gbl...
> One solution would be:
> Select T.Id, T.Name
> From Table As T
> Where Ascii(T.Name) > Ascii('C')
> Another would be
> Select T.Id, T.Name
> From Table As T
> Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
> Of course, by doing it this way, you'll likely get an index or table scan.
>
> Thomas
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||Thanks Thomas,
I don't know 'C' but only "ID=3"

Query:
select [id], [name] from tb1
union
select [id], [name] from tb2
union
select [id], [name] from tb3
ORDER BY [name]
Output:
How to return this base the previous query:
Can I use a cursor in sql:
dim rs
sql0= "DECLARE TBs_Cursor CURSOR FOR "
sql1 = "select [id], [name] from tb1 union select [id], [name] from tb2
union select [id], [name] from tb3 ORDER BY [name]"
sql2 = sql0 + sql1 + 'OPEN Cursor, Loop if ID < 3, then delete, and return
the records'
rs.open sql2,...
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23CLAj9ATFHA.3036@.TK2MSFTNGP10.phx.gbl...
> One solution would be:
> Select T.Id, T.Name
> From Table As T
> Where Ascii(T.Name) > Ascii('C')
> Another would be
> Select T.Id, T.Name
> From Table As T
> Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
> Of course, by doing it this way, you'll likely get an index or table scan.
>
> Thomas
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||On Thu, 28 Apr 2005 11:52:44 -0400, "js" <js@.someone@.hotmail.com> wrote:

>Hi, I have a query to build a returned record as and order by Name:
>ID, Name
>5 A
>1 B
>3 C '?
>4 D
>2 E
>
>Now, I want to add a parameter (for example ID=3) to the query (or somehow)
,
>so the returned record only return:
>ID, Name
>4 D
>2 E
>
>How to do that? Thanks.
>
Hi js,
Try if this does what you want:
SELECT ID, Name
FROM MyTable
WHERE Name > (SELECT Name
FROM MyTable
WHERE Id = 3)
ORDER BY Name
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> I don't know 'C' but only "ID=3"
Not sure what you mean here.
Thomas

Queries will be the death of me

Hello! My query skills are in need of some help. I've simplified my tables
in order to explain what it is that I'm trying to do. I need this to create
dynamic checkboxes on an asp.net page and to have the checkbox values set as
the tables are drawn dynamically. Anyway, here goes:
Table A
ItemID <bigint>
Title <string>
Table B
PurchaseID <bitint>
ItemID <smallint>
I have two tables (A and B). The two tables are linked on field ItemID. I
need to make a query that will return ALL of the values from table A along
with a boolean value stating whether or not table B contains an item - but
only for a specific purchaseID.
For example:
Table A (ItemID, Title)
1 VCR
2 DVD
3 Television
4 Phone
Table B (PurchaseID, ItemID)
1 1 (VCR)
1 2 (DVD)
1 3 (Tel)
2 2 (DVD)
2 1 (...)
2 4
3 4
3 1
For the above data I need a list of the Items from tableA and a boolean
value showing whether the item exists in Table B for a specific PurchaseID,
in this case I used PurchaseID = 2.
Result Table (ItemID, Title, Boolean)
1 VCR 1
2 DVD 1
3 Television 0
4 Phone 1
The result shows that there exists a VCR, DVD and Phone for purchaseID, but
no Television.
Grateful for any help...
Hello ChrisN,
I think this is what you're looking for (with tables and data included):
-- DROP TABLE A - Items
CREATE TABLE A (
ItemID int NOT NULL, -- PK
Title varchar (50) NOT NULL)
go
ALTER TABLE A WITH NOCHECK ADD PRIMARY KEY CLUSTERED (ItemID)
go
-- DROP TABLE B - Purchases
CREATE TABLE B (
PurchaseID int NOT NULL, -- PK
ItemID int NOT NULL) -- PK
go
ALTER TABLE B WITH NOCHECK ADD PRIMARY KEY CLUSTERED (PurchaseID, ItemID)
go
-- Insert test sample data into table A:
insert into A values (1, 'VCR')
insert into A values (2, 'DVD')
insert into A values (3, 'Television')
insert into A values (4, 'Phone')
go
-- Insert test sample data into table B:
insert into B values (1, 1) -- VCR
insert into B values (1, 2) -- DVD
insert into B values (1, 3) -- Television
insert into B values (2, 2) -- DVD
insert into B values (2, 1) -- VCR
insert into B values (2, 4) -- Phone
insert into B values (3, 4) -- Phone
insert into B values (3, 1) -- VCR
go
SELECT TableA.ItemID, TableA.Title
FROM A TableA
INNER JOIN B TableB ON (TableA.ItemID = TableB.ItemID)
WHERE PurchaseID = 2
go
However, are you also interested in a FTS query based upon the above INNER
JOIN between tables A & B since you posted this question in the fulltext
newsgroup? If so, could you post an example of what you're looking for?
Thanks,
John
"ChrisN" <cnewald@.hotmail.com> wrote in message
news:uJV$f$b1EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello! My query skills are in need of some help. I've simplified my
tables
> in order to explain what it is that I'm trying to do. I need this to
create
> dynamic checkboxes on an asp.net page and to have the checkbox values set
as
> the tables are drawn dynamically. Anyway, here goes:
> Table A
> ItemID <bigint>
> Title <string>
> Table B
> PurchaseID <bitint>
> ItemID <smallint>
> I have two tables (A and B). The two tables are linked on field ItemID.
I
> need to make a query that will return ALL of the values from table A along
> with a boolean value stating whether or not table B contains an item - but
> only for a specific purchaseID.
> For example:
> Table A (ItemID, Title)
> 1 VCR
> 2 DVD
> 3 Television
> 4 Phone
> Table B (PurchaseID, ItemID)
> 1 1 (VCR)
> 1 2 (DVD)
> 1 3 (Tel)
> 2 2 (DVD)
> 2 1 (...)
> 2 4
> 3 4
> 3 1
> For the above data I need a list of the Items from tableA and a boolean
> value showing whether the item exists in Table B for a specific
PurchaseID,
> in this case I used PurchaseID = 2.
> Result Table (ItemID, Title, Boolean)
> 1 VCR 1
> 2 DVD 1
> 3 Television 0
> 4 Phone 1
> The result shows that there exists a VCR, DVD and Phone for purchaseID,
but
> no Television.
> Grateful for any help...
>
|||Thank you John for your response. Being a dough head I did post to the
wrong newsgroup and realized after it sent. I'll give your solution a try
in a little while.
Thanks again,
Chris.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OlzoyOf1EHA.1300@.TK2MSFTNGP14.phx.gbl...
> Hello ChrisN,
> I think this is what you're looking for (with tables and data included):
> -- DROP TABLE A - Items
> CREATE TABLE A (
> ItemID int NOT NULL, -- PK
> Title varchar (50) NOT NULL)
> go
> ALTER TABLE A WITH NOCHECK ADD PRIMARY KEY CLUSTERED (ItemID)
> go
> -- DROP TABLE B - Purchases
> CREATE TABLE B (
> PurchaseID int NOT NULL, -- PK
> ItemID int NOT NULL) -- PK
> go
> ALTER TABLE B WITH NOCHECK ADD PRIMARY KEY CLUSTERED (PurchaseID, ItemID)
> go
> -- Insert test sample data into table A:
> insert into A values (1, 'VCR')
> insert into A values (2, 'DVD')
> insert into A values (3, 'Television')
> insert into A values (4, 'Phone')
> go
> -- Insert test sample data into table B:
> insert into B values (1, 1) -- VCR
> insert into B values (1, 2) -- DVD
> insert into B values (1, 3) -- Television
> insert into B values (2, 2) -- DVD
> insert into B values (2, 1) -- VCR
> insert into B values (2, 4) -- Phone
> insert into B values (3, 4) -- Phone
> insert into B values (3, 1) -- VCR
> go
> SELECT TableA.ItemID, TableA.Title
> FROM A TableA
> INNER JOIN B TableB ON (TableA.ItemID = TableB.ItemID)
> WHERE PurchaseID = 2
> go
> However, are you also interested in a FTS query based upon the above INNER
> JOIN between tables A & B since you posted this question in the fulltext
> newsgroup? If so, could you post an example of what you're looking for?
> Thanks,
> John
>
>
> "ChrisN" <cnewald@.hotmail.com> wrote in message
> news:uJV$f$b1EHA.3416@.TK2MSFTNGP09.phx.gbl...
> tables
> create
> as
> I
> PurchaseID,
> but
>
sql