Hello ,
well i have a problem in SQL n tht is
... i have a column cantainning  names of students in any
institute ...now i have to write a query tht should give me numeric
answer for the characters present in a students name
suppose i would like a charcter 'A'

Alen = 1 should be displayed
AAbias = 2
Micheal = 1
Missy = 0

any ideas how can i do this in a query

Bye

The solution to this might vary significantly depending upon which vendor
product you are using.

Oracle?
Sybase?
MS SQL Server?
etc


> Hello ,
> well i have a problem in SQL n tht is
> ... i have a column cantainning  names of students in any
> institute ...now i have to write a query tht should give me numeric
> answer for the characters present in a students name
> suppose i would like a charcter 'A'
>
> Alen = 1 should be displayed
> AAbias = 2
> Micheal = 1
> Missy = 0
>
> any ideas how can i do this in a query
>
> Bye

The solution to this might vary significantly depending upon which vendor
product you are using.

Oracle?
Sybase?
MS SQL Server?
etc


> Hello ,
> well i have a problem in SQL n tht is
> ... i have a column cantainning  names of students in any
> institute ...now i have to write a query tht should give me numeric
> answer for the characters present in a students name
> suppose i would like a charcter 'A'
>
> Alen = 1 should be displayed
> AAbias = 2
> Micheal = 1
> Missy = 0
>
> any ideas how can i do this in a query
>
> Bye

Faisal,

This is a very clever solution!

Remember to upper the name prior to replace since Oracle is by default case
sensitive.

select length(name) - length(replace(upper(name),'A','')) numChars
from student

Regards,
Michael Gerholdt
SUNY College at Fredonia
----- Original Message -----
From: "Faisal Raja" <rajafaisal09@...>

> Well I was looking the solution in Oracle Actually
> But thanks for replying i have got the solution now Thanks a lot
> it will be like
>             Select length(name) - length( replace(name,'A',' ' ))  Name
>              from student ;
> Well i have got solution like this , If any one know its solution other
way round to then plz share it
> Thanks to all

>
> > Hello ,
> > well i have a problem in SQL n tht is
> > ... i have a column cantainning  names of students in any
> > institute ...now i have to write a query tht should give me numeric
> > answer for the characters present in a students name
> > suppose i would like a charcter 'A'
> >
> > Alen = 1 should be displayed
> > AAbias = 2
> > Micheal = 1
> > Missy = 0
> >
> > any ideas how can i do this in a query

I've been just switching over to using ansi joins when I can. I have an
instance in a subquery that I think should work but it does not.

    SELECT a.spriden_id,
           a.spriden_first_name
      FROM spriden a join sgbstdn b on a.spriden_pidm = b.sgbstdn_pidm
       AND a.spriden_pidm = 3344334
       AND b.sgbstdn_term_code_eff = (SELECT max(z.sgbstdn_term_code_eff)
                                        FROM sgbstdn z
                                       WHERE z.sgbstdn_term_code_eff <=
'200420'
                                         AND z.sgbstdn_pidm = b.sgbstdn_pidm)

The above works fine but when I replace the subquery with an ansi join (as
below) I get no results.


    SELECT a.spriden_id,
           a.spriden_first_name
      FROM spriden a join sgbstdn b on a.spriden_pidm = b.sgbstdn_pidm
       AND a.spriden_pidm = 3344334
       AND b.sgbstdn_term_code_eff = (SELECT max(z.sgbstdn_term_code_eff)
                                        FROM sgbstdn z join sgbstdn b on
b.sgbstdn_pidm = z.sgbstdn_pidm
                                       WHERE z.sgbstdn_term_code_eff <=
'200420')


Oracle is new to ansi joins as of 9i I believe. Is there a known problem
with ansi joins in subqueries, or am I doing something wrong and just not
seeing it?
--
Michael Gerholdt
Senior Programmer/Analyst
Web Development Manager
706 Maytum Hall
SUNY Fredonia
716.673.3388

I think, but I don't use Oracle 9, you have the alias b defined twice. I
would switch one of the two places you mention this table to another
alias say c.

SELECT a.spriden_id,
           a.spriden_first_name
      FROM spriden a join sgbstdn b on a.spriden_pidm = b.sgbstdn_pidm
       AND a.spriden_pidm = 3344334
       AND b.sgbstdn_term_code_eff = (SELECT max(z.sgbstdn_term_code_eff)
                                        FROM sgbstdn z join sgbstdn c on
c.sgbstdn_pidm = z.sgbstdn_pidm
                                       WHERE z.sgbstdn_term_code_eff <=
'200420')


You might be confusing the SQL engine. Note just a wild guess.

John Warner
mailto:john@...

> -----Original Message-----
> From: Michael Gerholdt [mailto:gerholdt@...]
> Sent: Tuesday, July 06, 2004 11:08 AM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: [SQLQueriesNoCode] ansi joins in Oracle subqueries
>
>
> I've been just switching over to using ansi joins when I can.
> I have an instance in a subquery that I think should work but
> it does not.
>
>    SELECT a.spriden_id,
>           a.spriden_first_name
>      FROM spriden a join sgbstdn b on a.spriden_pidm = b.sgbstdn_pidm
>       AND a.spriden_pidm = 3344334
>       AND b.sgbstdn_term_code_eff = (SELECT
> max(z.sgbstdn_term_code_eff)
>                                        FROM sgbstdn z
>                                       WHERE
> z.sgbstdn_term_code_eff <= '200420'
>                                         AND z.sgbstdn_pidm =
> b.sgbstdn_pidm)
>
> The above works fine but when I replace the subquery with an
> ansi join (as
> below) I get no results.
>
>
>    SELECT a.spriden_id,
>           a.spriden_first_name
>      FROM spriden a join sgbstdn b on a.spriden_pidm = b.sgbstdn_pidm
>       AND a.spriden_pidm = 3344334
>       AND b.sgbstdn_term_code_eff = (SELECT
> max(z.sgbstdn_term_code_eff)
>                                        FROM sgbstdn z join
> sgbstdn b on b.sgbstdn_pidm = z.sgbstdn_pidm
>                                       WHERE z.sgbstdn_term_code_eff <=
> '200420')
>
>
> Oracle is new to ansi joins as of 9i I believe. Is there a
> known problem with ansi joins in subqueries, or am I doing
> something wrong and just not seeing it?
> --
> Michael Gerholdt
> Senior Programmer/Analyst
> Web Development Manager
> 706 Maytum Hall
> SUNY Fredonia
> 716.673.3388
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Yahoo! Domains - Claim yours for only $14.70
> http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/m7folB/T> M
>
>
> --------------------------------------------------------------
> ------~->
>
>
> Yahoo! Groups Links
>
>
>
>
>
>

Ah ... that could be it. I'd still have to join that inner table back to b
as well ...

I'll play with your suggestion.

Thanks

> I think, but I don't use Oracle 9, you have the alias b defined twice. I
> would switch one of the two places you mention this table to another
> alias say c.
>
> SELECT a.spriden_id,
>           a.spriden_first_name
>      FROM spriden a join sgbstdn b on a.spriden_pidm = b.sgbstdn_pidm
>       AND a.spriden_pidm = 3344334
>       AND b.sgbstdn_term_code_eff = (SELECT max(z.sgbstdn_term_code_eff)
>                                        FROM sgbstdn z join sgbstdn c on
> c.sgbstdn_pidm = z.sgbstdn_pidm
>                                       WHERE z.sgbstdn_term_code_eff <=
> '200420')
>
>
> You might be confusing the SQL engine. Note just a wild guess.
>
> John Warner
> mailto:john@...
>
> > -----Original Message-----
> > From: Michael Gerholdt [mailto:gerholdt@...]
> > Sent: Tuesday, July 06, 2004 11:08 AM
> > To: SQLQueriesNoCode@yahoogroups.com
> > Subject: [SQLQueriesNoCode] ansi joins in Oracle subqueries
> >
> >
> > I've been just switching over to using ansi joins when I can.
> > I have an instance in a subquery that I think should work but
> > it does not.
> >
> >    SELECT a.spriden_id,
> >           a.spriden_first_name
> >      FROM spriden a join sgbstdn b on a.spriden_pidm = b.sgbstdn_pidm
> >       AND a.spriden_pidm = 3344334
> >       AND b.sgbstdn_term_code_eff = (SELECT
> > max(z.sgbstdn_term_code_eff)
> >                                        FROM sgbstdn z
> >                                       WHERE
> > z.sgbstdn_term_code_eff <= '200420'
> >                                         AND z.sgbstdn_pidm =
> > b.sgbstdn_pidm)
> >
> > The above works fine but when I replace the subquery with an
> > ansi join (as
> > below) I get no results.
> >
> >
> >    SELECT a.spriden_id,
> >           a.spriden_first_name
> >      FROM spriden a join sgbstdn b on a.spriden_pidm = b.sgbstdn_pidm
> >       AND a.spriden_pidm = 3344334
> >       AND b.sgbstdn_term_code_eff = (SELECT
> > max(z.sgbstdn_term_code_eff)
> >                                        FROM sgbstdn z join
> > sgbstdn b on b.sgbstdn_pidm = z.sgbstdn_pidm
> >                                       WHERE z.sgbstdn_term_code_eff <=
> > '200420')
> >
> >
> > Oracle is new to ansi joins as of 9i I believe. Is there a
> > known problem with ansi joins in subqueries, or am I doing
> > something wrong and just not seeing it?
> > --
> > Michael Gerholdt
> > Senior Programmer/Analyst
> > Web Development Manager
> > 706 Maytum Hall
> > SUNY Fredonia
> > 716.673.3388
> >
> >
> >
> >
> > ------------------------ Yahoo! Groups Sponsor
> > --------------------~-->
> > Yahoo! Domains - Claim yours for only $14.70
> > http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/m7folB/T> M
> >
> >
> > --------------------------------------------------------------
> > ------~->
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>

Hello there!  My first post.

I am working on a database that uses first and last names in its
search parameters.  I am using traditional queries such as SELECT *
FROM TABLE WHERE FIRST_NAME = 'JEFFREY';

But is there a way you can fashion the SQL query itself so that it
looks for a substring?  For example, is there a way to have the query
look up the string 'JEFF' and also include in its results the
names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
with J-E-F-F?

Hi,

I have table as follows

Row Id, fld_1, Date, fld_2, fld_3

fld_1 will be used to join to other table. Multiple records
can have same value for fld_1. The 'Date' field will always have
different value.

What I need is collection of records (all fields) for each unique
value of fld_1, where 'Date' is maximum.

e.g.

Row Id, fld_1, Date, fld_2, fld_3
1, A, 2/2/04, p, q
2, A, 2/3/04, z, v
3, A, 2/4/04, t, y
4, B, 1/3/04, s, d
5, C, 3/3/04, T, h
6, C, 5/6/04, y, u

I should get following set
3, A, 2/4/04, t, y
4, B, 1/3/04, s, d
6, C, 5/6/04, y, u

What would be query ?

Thanks and regards,

Prasanna Dhakephalkar

SELECT First_Name
FROM Table
WHERE First_Name Like 'Jeff%';

John Warner
mailto:john@...

> -----Original Message-----
> From: jephmat@... [mailto:jephmat@...]
> Sent: Tuesday, July 06, 2004 10:08 PM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: [SQLQueriesNoCode] Substring queries?
>
>
>  Hello there!  My first post.
>
> I am working on a database that uses first and last names in its
> search parameters.  I am using traditional queries such as SELECT *
> FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
>
> But is there a way you can fashion the SQL query itself so that it
> looks for a substring?  For example, is there a way to have the query
> look up the string 'JEFF' and also include in its results the
> names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
> with J-E-F-F?
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Yahoo! Domains - Claim yours for only $14.70
> http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/m7folB/T> M
>
>
> --------------------------------------------------------------
> ------~->
>
>
> Yahoo! Groups Links
>
>
>
>
>
>

>
> I am working on a database that uses first and last names in its
> search parameters.  I am using traditional queries such as SELECT *
> FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
>
> But is there a way you can fashion the SQL query itself so that it
> looks for a substring?  For example, is there a way to have the query
> look up the string 'JEFF' and also include in its results the
> names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
> with J-E-F-F?
>
>

this is quite simple because you need not actually treat it as a substr and
use any functions to work on the data. Instead, just use the SQL operator
"LIKE" instead of "=" and then make sure you use the percent sign as a wild
card (unless you are in Access where I think it is an asterisk)

Do a search on "LIKE" operator in SQL and you'll be set


where first_name like 'JEFF%'

does what you want above

where first_name like '%na'

will get 'Anna' and 'banana' and anything that ends in 'na'

where first name like 'J%y'

Joy, Jimmy, Jeffrey, etc

where first_name like 'J_y'

Joy, Jay
(the underscore limits to one character)

select a.field1, a.field2, b.field3
from tableA a join tableB b on b.fld_1 = a.fld_1
where a.date = (select max(c.Date)
                              from tableA c
                            where c.fld_1 = a.fld_1)




> Hi,
>
> I have table as follows
>
> Row Id, fld_1, Date, fld_2, fld_3
>
> fld_1 will be used to join to other table. Multiple records
> can have same value for fld_1. The 'Date' field will always have
> different value.
>
> What I need is collection of records (all fields) for each unique
> value of fld_1, where 'Date' is maximum.
>
> e.g.
>
> Row Id, fld_1, Date, fld_2, fld_3
> 1, A, 2/2/04, p, q
> 2, A, 2/3/04, z, v
> 3, A, 2/4/04, t, y
> 4, B, 1/3/04, s, d
> 5, C, 3/3/04, T, h
> 6, C, 5/6/04, y, u
>
> I should get following set
> 3, A, 2/4/04, t, y
> 4, B, 1/3/04, s, d
> 6, C, 5/6/04, y, u
>
> What would be query ?
>

use  : first_name like '%JEF%'

Mark Gorelik
Fujitsu Consulting
416-969-7141



-----Original Message-----
From: jephmat@... [mailto:jephmat@...]
Sent: Tuesday, July 06, 2004 10:08 PM
To: SQLQueriesNoCode@yahoogroups.com
Subject: [SQLQueriesNoCode] Substring queries?


  Hello there!  My first post.

I am working on a database that uses first and last names in its
search parameters.  I am using traditional queries such as SELECT *
FROM TABLE WHERE FIRST_NAME = 'JEFFREY';

But is there a way you can fashion the SQL query itself so that it
looks for a substring?  For example, is there a way to have the query
look up the string 'JEFF' and also include in its results the
names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
with J-E-F-F?






Yahoo! Groups Links






*** This communication is strictly private and contains confidential information
intended solely for the addressee(s).  Access to this communication by parties
other than the intended recipient(s) is unauthorized.  If you have received this
communication in error and are not the intended recipient, any disclosure,
copying, distribution or action taken is strictly prohibited and may be
unlawful. ***

*** Ce message est strictement prive et contient des renseignements
confidentiels destines uniquement au(x) destinataire(s).  L'acces a ce message
par des parties autres que le(s) destinataire(s) prevu(s) n'est pas autorise. 
Si vous avez reçu ce message par erreur et n'etes pas le destinataire prevu,
toute divulgation, copie, distribution ou action prise est strictement interdite
et peut etre consideree comme illegale. ***

Have a go with SELECT * FROM TABLE WHERE FIRST_NAME LIKE 'JEFF%'
Note that the syntax may vary from vendor to vendor.

-----Original Message-----
From: jephmat@... [mailto:jephmat@...]
Sent: Tuesday, July 06, 2004 10:08 PM
To: SQLQueriesNoCode@yahoogroups.com
Subject: [SQLQueriesNoCode] Substring queries?

  Hello there!  My first post.

I am working on a database that uses first and last names in its
search parameters.  I am using traditional queries such as SELECT *
FROM TABLE WHERE FIRST_NAME = 'JEFFREY';

But is there a way you can fashion the SQL query itself so that it
looks for a substring?  For example, is there a way to have the query
look up the string 'JEFF' and also include in its results the
names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
with J-E-F-F?






Yahoo! Groups Links

There are a couple of ways to do this, using CharIndex or Like.

Select * From Table Where FirstName Like 'Jeff%'

Select * From Table Where CharIndex('Jeff', FirstName) > 0


George Clay
SQL Server DBA
www.sqldudes.com

--- jephmat@... wrote:
>  Hello there!  My first post.
>
> I am working on a database that uses first and last names in its
> search parameters.  I am using traditional queries such as SELECT *
> FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
>
> But is there a way you can fashion the SQL query itself so that it
> looks for a substring?  For example, is there a way to have the query
> look up the string 'JEFF' and also include in its results the
> names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
> with J-E-F-F?
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>




__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

Prasanna Dhakephalkar wrote:
> [...]
> What I need is collection of records (all fields) for each unique
> value of fld_1, where 'Date' is maximum.
> [...]

SELECT * FROM yourtable a WHERE date=
(SELECT MAX(date) FROM yourtable b WHERE a.fld_1=b.fld_1)

Razvan

SELECT * FROM your_table WHERE first_name LIKE 'JEFF%'

If you use Microsoft SQL Server, you may also want to try:

SELECT * FROM your_table WHERE DIFFERENCE(first_name, 'Jeffrey')>2

This query can find even variations like "Geoffrey", "Jefry", etc.

For more informations, see "Comparing SOUNDEX and DIFFERENCE" in BOL:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_11_1rj9.asp

Razvan

----- Original Message -----
From: jephmat@...
To: SQLQueriesNoCode@yahoogroups.com
Sent: Wednesday, July 07, 2004 5:07 AM
Subject: [SQLQueriesNoCode] Substring queries?

I am working on a database that uses first and last names in its
search parameters.  I am using traditional queries such as SELECT *
FROM TABLE WHERE FIRST_NAME = 'JEFFREY';

But is there a way you can fashion the SQL query itself so that it
looks for a substring?  For example, is there a way to have the query
look up the string 'JEFF' and also include in its results the
names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
with J-E-F-F?

Hi,

For a farther-ranging and perhaps more intuitive search you might like to
use SQL Server's built-in SOUNDEX command. The process dates back to 19th
century, when census-takers needed to account for accidental or traditional
variations in the spellings of surnames: for example, "MacFadyen" and
"McFadden". To my understanding, both names are valid spellings of the same
name, but would not be treated as such in the typical alphabetical listing.
The SOUNDEX process compares the consonantal components - in which case,
both names might render something like "MCFDN".

Apparently the command can also rank the similarity of the matches by
percent. I don't think it works (without tweaking) for surnames that
include hyphens, apostrophes, spaces (i.e, Leonardo "da Vinci"), etc. You
might need to process your search string first (by removing the illegal
characters).

          regards,
          Iian

At 12:45 PM 7/7/2004 -0700, you wrote:
>There are a couple of ways to do this, using CharIndex or Like.
>
>Select * From Table Where FirstName Like 'Jeff%'
>
>Select * From Table Where CharIndex('Jeff', FirstName) > 0
>
>
>George Clay
>SQL Server DBA
>www.sqldudes.com
>
>--- jephmat@... wrote:
> >  Hello there!  My first post.
> >
> > I am working on a database that uses first and last names in its
> > search parameters.  I am using traditional queries such as SELECT *
> > FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
> >
> > But is there a way you can fashion the SQL query itself so that it
> > looks for a substring?  For example, is there a way to have the query
> > look up the string 'JEFF' and also include in its results the
> > names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
> > with J-E-F-F?
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! Mail is new and improved - Check it out!
>http://promotions.yahoo.com/new_mail
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>

----- Original Message -----

From: Joe Delpha

To: SQLQueriesNoCode@yahoogroups.com

Sent: Wednesday, July 07, 2004 9:42 PM

Subject: [SQLQueriesNoCode] Sql puzzle

Hello All,

            I have a table with a lay out as follows

OrderNo, OrderLineNo, TransDate, TransType, TransTime, TransAmt, TransQty.

 

The transaction type may be ‘AD’ add, or ‘UP’ update with add always being first within the table.

I would like to select all ‘UP’ transactions for a given transaction day and also select the immediately previous row corresponding to that line.

 

I have tried

SELECT A.OrderNo, A.OrderLineNo, A.TransDate, A.TransAmt, A.TransQty, B.TransAmt, B.TransQty

FROM TABLEA A

INNER JOIN TABLE A B

            ON A.OrderNo = B.OrderNo AND A.OrderLineNo=B.OrderLineNo

                 AND ((A.TransDate = B.TransDate AND A.TransTime > B.TransTime) OR A.TransDate>B.TransDate)

WHERE A.TransDate=’2004-05-06’ and A.Transtype=’UP’

 

Does anyone know of a method I might use to return only one row from the B side of this query?

Hi Prasanna Dhakephalkar,

below query may help you

set nocount on
if exists ( select 'X'
    from sysobjects
    where  type = 'U'
    and  name = 'SQL_QNC')
begin
  drop table SQL_QNC
end
go
Create table SQL_QNC(
RowId int,
fld_1 Varchar(10),
Date datetime,
fld_2 Varchar(10),
fld_3 Varchar(10))
go
insert into SQL_QNC
values(1, 'A', '02-Feb-2004', 'fld_21', 'fld_31')
insert into SQL_QNC
values(2, 'A', '02-Mar-2004', 'fld_22', 'fld_32')
insert into SQL_QNC
values(3, 'A', '02-Apr-2004', 'fld_23', 'fld_33')
insert into SQL_QNC
values(4, 'B', '01-Mar-2004', 'fld_24', 'fld_34')
insert into SQL_QNC
values(5, 'C', '03-Mar-2004', 'fld_25', 'fld_35')
insert into SQL_QNC
values(6, 'C', '03-Jun-2004', 'fld_26', 'fld_36')
go
SELECT * FROM SQL_QNC
select  outer_table.RowId,
   outer_table.fld_1,
   outer_table.Date,
   outer_table.fld_2,
   outer_table.fld_3
from  SQL_QNC outer_table,
   (select fld_1  'FLD_1_INNER',
     max(Date) 'DATE_INNER'
   from   SQL_QNC
   group by fld_1) A
where A.DATE_INNER = outer_table.date
and  A.FLD_1_INNER = outer_table.fld_1
ORDER BY 1




set nocount off



---------------------
    Date: Wed, 07 Jul 2004 15:05:06 -0000
    From: "pdhakeph" <pdhakeph@...>
Subject: Get specifc records from a table

Hi,

I have table as follows

Row Id, fld_1, Date, fld_2, fld_3

fld_1 will be used to join to other table. Multiple records
can have same value for fld_1. The 'Date' field will always have
different value.

What I need is collection of records (all fields) for each unique
value of fld_1, where 'Date' is maximum.

e.g.

Row Id, fld_1, Date, fld_2, fld_3
1, A, 2/2/04, p, q
2, A, 2/3/04, z, v
3, A, 2/4/04, t, y
4, B, 1/3/04, s, d
5, C, 3/3/04, T, h
6, C, 5/6/04, y, u

I should get following set
3, A, 2/4/04, t, y
4, B, 1/3/04, s, d
6, C, 5/6/04, y, u

What would be query ?

Thanks and regards,

Prasanna Dhakephalkar

DISCLAIMER:

Information transmitted by this e-mail may be proprietary to Ramco
Systems Ltd., and / or the authors of the information and is intended for
use only by the individual or entity to which it is addressed, and may
contain confidential or legally privileged information. If you are not
the intended recipient or it appears that this mail has been forwarded
to you without proper authority, you are not authorised to access, read,
disclose, copy, use or otherwise deal with it and any such actions are
prohibited and may be unlawful.

Internet communications cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, arrive late or
contain viruses. Ramco Systems Limited therefore does not accept
liability for any errors, omissions, viruses or computer problems experienced
as a result of this transmission.

If you have received this e-mail in error, please notify us immediately
at mail to: mailadmin@... and delete this mail from your
records. Notice is hereby given that no representation, contract or other
binding obligation shall be created by this e-mail.

Hi,

Thanks ,Michael Gerholdt, Razvan Socol

Regards,

Prasanna.

Hi Guys,



This should be so simple, but I am flumoxed how to achieve it, I
have 2 tables, one with the customers details and a second table
detailing years attended, I would like to pull in the following
information:



tblCustomers

ID
FirstName



tblAttended

EventYear
CustID
Attended

The tblCustomers.ID is the link to the tblAttended.CustID to select
the customers and the years, the field attended is yes/no to see if
they did or didn't. I would like to show the results as follows,
basically a count of the years attended with their name:

Customer          No of Years Attended

L Hughes          4

J Williams        6


Can you show me the way…please?

Regards,

Darryl

John Warner
mailto:john@...

-----Original Message-----
From: Razvan Socol [mailto:rsocol@...]
Sent: Thursday, July 08, 2004 2:23 AM
To: SQLQueriesNoCode@yahoogroups.com
Subject: Re: [SQLQueriesNoCode] Sql puzzle

Which is the primary key in this table ? Don't tell me you don't have a primary key...

I suppose the unique key is (OrderNo, OrderLineNo, TransDate, TransTime).

If you also have a surrogate primary key (let's call it PK), the following query might work:

 

Hi Guys,



This should be so simple, but I am flumoxed how to achieve it, I
have 2 tables, one with the customers details and a second table
detailing years attended, I would like to pull in the following
information:



tblCustomers

ID
FirstName



tblAttended

EventYear
CustID
Attended

The tblCustomers.ID is the link to the tblAttended.CustID to select
the customers and the years, the field attended is yes/no to see if
they did or didn't. I would like to show the results as follows,
basically a count of the years attended with their name:

Customer          No of Years Attended

L Hughes          4

J Williams        6


Can you show me the way…please?

Regards,

Darryl

Hi Guys,

Forgot I posted this, solved it already:

SELECT tblCustomers.FirstName, COUNT(tblAttended.attended) AS
EventsAttended FROM tblAttended, tblCustomers WHERE tblAttended.custID
=  tblCustomers.ID GROUP BY tblCustomers.FirstName
	 ORDER BY tblCustomers.FirstName

Thanks in anticipation of the replies you were preparing...

Darryl

-----Original Message-----
From: Godden, Darryl
Sent: 08 July 2004 10:57
To: SQLQueriesNoCode@yahoogroups.com
Subject: [SQLQueriesNoCode] (unknown)

Hi Guys,



This should be so simple, but I am flumoxed how to achieve it, I
have 2 tables, one with the customers details and a second table
detailing years attended, I would like to pull in the following
information:



tblCustomers

ID
FirstName



tblAttended

EventYear
CustID
Attended

The tblCustomers.ID is the link to the tblAttended.CustID to select
the customers and the years, the field attended is yes/no to see if
they did or didn't. I would like to show the results as follows,
basically a count of the years attended with their name:

Customer          No of Years Attended

L Hughes          4

J Williams        6


Can you show me the way...please?

Regards,

Darryl








Yahoo! Groups Links






***
This e-mail and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed.  If you have received this e-mail in error please notify the
system manager using the details below. The contents of this e-mail
represent the views of the individual(s) named above and do not
necessarily represent the views of Merthyr Tydfil County Borough
Council.

Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn
gyfrinachol ac wedi'u bwriadu ar gyfer pwy bynnag y cyfeirir ef ato neu
atynt. Os ydych wedi ei dderbyn drwy gamgymeriad yna gadewch i'r rheolwr
systemau wybod drwy ddefnyddio'r manylion isod. Mae cynnwys yr e-bost
hwn cynrychioli barn y sawl a enwir uchod, felly nid ydyw'n dilyn ei fod
yn cynrychioli barn Cyngor Bwrdeistref Sirol Merthyr
Tudful.

Cyngor Bwrdeistref Sirol Merthyr Tudful
Canolfan Dinesig
Stryd Y Castell
Merthyr Tudful
CF47 8AN

Merthyr Tydfil County Borough Council
Civic Centre
Castle Street
Merthyr Tydfil
CF47 8AN

Teleffon\Telephone: 01685 725000

http://www.merthyr.gov.uk
____________________________________________________________
This email has been scanned for all viruses by the MessageLabs SkyScan
service. For more information on a proactive anti-virus service working
around the clock, around the globe, visit http://www.messagelabs.com
________________________________________________________________________

***
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.  If
you have received this e-mail in error please notify the system manager using
the details below. The contents of this e-mail represent the views of the
individual(s) named above and do not necessarily represent the views of Merthyr
Tydfil County Borough Council.

Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn gyfrinachol ac
wedi'u bwriadu ar gyfer pwy bynnag y cyfeirir ef ato neu atynt. Os ydych wedi ei
dderbyn drwy gamgymeriad yna gadewch i'r rheolwr systemau wybod drwy
ddefnyddio'r manylion isod. Mae cynnwys yr e-bost hwn cynrychioli barn y sawl a
enwir uchod, felly nid ydyw'n dilyn ei fod yn cynrychioli barn Cyngor
Bwrdeistref Sirol Merthyr
Tudful.

Cyngor Bwrdeistref Sirol Merthyr Tudful
Canolfan Dinesig
Stryd Y Castell
Merthyr Tudful
CF47 8AN

Merthyr Tydfil County Borough Council
Civic Centre
Castle Street
Merthyr Tydfil
CF47 8AN

Teleffon\Telephone: 01685 725000

http://www.merthyr.gov.uk
____________________________________________________________
This email has been scanned for all viruses by the MessageLabs SkyScan
service. For more information on a proactive anti-virus service working
around the clock, around the globe, visit http://www.messagelabs.com
________________________________________________________________________

Hi Guys,



This should be so simple, but I am flumoxed how to achieve it, I
have 2 tables, one with the customers details and a second table
detailing years attended, I would like to pull in the following
information:



tblCustomers

ID
FirstName



tblAttended

EventYear
CustID
Attended

The tblCustomers.ID is the link to the tblAttended.CustID to select
the customers and the years, the field attended is yes/no to see if
they did or didn't. I would like to show the results as follows,
basically a count of the years attended with their name:

Customer          No of Years Attended

L Hughes          4

J Williams        6


Can you show me the way…please?

Regards,

Darryl

Consider the following table in sql server 2000.   ProjectInfo
(Project, Phase, Manager, Assistant)  --- all varchar fields, no
keys.

The datalooks like:

Project1, Phase1, Bill, Tom
Project1, Phase2, Bill, Tom
Project2, Phase1, Jill, Joe
Project3, Phase1, Jill, Ted
Project3, Phase2, Jill, Tom

I want to select all records except if for any given project if the
manager and assistant are the same I only want to select one record
and not return the phase.

So using the example above I want my result set to be:

Project1, <null>, Bill, Tom
Project2, Phase1, Jill, Joe
Project3, Phase1, Jill, Ted
Project3, Phase2, Jill, Tom

Is there way to do this in a select statement (preferably without
cursors)?

Thanks!!