Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql queries

Author  Topic 

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-18 : 21:36:36
Please I need help with this query
==> What are the full names (first name, a space, and last name)
of Members that joined the club in 2012?
Show them in chronological order by the date they joined.
This what I have so far
==> What are the full names (first name, a space, and last name)
of Members that joined the club in 2012?
Show them in chronological order by the date they joined.
Thanks in advance any direction would be highly welcomed

Slim Shady

AASC
Starting Member

24 Posts

Posted - 2014-11-19 : 00:45:21
you need to use two concepts in your query
1. Concatenation
2. date functions

-- sample query
select lastname+', '+firstname
from members
where datepart(yy,joindate)='2012'
order by joindate
Go to Top of Page

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-19 : 05:39:00
Thank you very much AASC

Slim Shady
Go to Top of Page

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-19 : 08:51:13
This is what I have sofar is not working right AASC
==>
SELECT COALESCE(FirstName + ' ','') + COALESCE(LastName,'') AS FullName
FROM Person INNERJOIN
SELECT Count(*)DateJoined
FROM Member
WHERE datepart(YYYY, DateJoined)='2012'
ORDER BY DateJoined

Slim Shady
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 08:57:14
Post your error messages (and check your syntax -- INNER JOIN is two words and you are missing an ON clause)
Go to Top of Page

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-19 : 09:54:52
GBritton Thanks for the tips
it works but does not list out names and dates join
it just list all names
remember the person table is a parent table has all the fields like fname and lastname that member table dont have .
So thats where my main problem is joining the to such that dates joijned and names show as well
this what I have ==>
SELECT COALESCE(FirstName + ' ','') + COALESCE(LastName,'') AS FullName FROM Person
SELECT DateJoined FROM Member WHERE datepart(YYYY, DateJoined)='2012'
ORDER BY DateJoined

Slim Shady
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 10:36:01
you have two queries, one from the Person table and a second one from the Member table. Do you want to have a single query joining the two? If so, what is the join column?
Go to Top of Page

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-19 : 10:38:30
Yes I want to able to pull out the dates joined and and full names as displayed but don't know how i AM VERY NEW TO SQL and any help and guidance would be welcomed Gbritton.
Thank you

Slim Shady
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 11:38:41
OK -- so you need to answer my second question: What is the join column? That is, what column (or columns) do both tables have in common? (There needs to be at least one)
Go to Top of Page

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-19 : 12:07:38
they both have ID
and Id in Person table is a fk to Member table

Slim Shady
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 12:28:24
[code]
select lastname+', '+firstname, joindate
from Person p
join Members m
on p.id = m.id
where datepart(yy,joindate)='2012'
order by joindate
[/code]
Go to Top of Page
   

- Advertisement -