Joining two SQL statements and formatting returned data.

[Luk3r]

Hey guys, so what I'm trying to do is run two queries (keep in mind, there may be a better way to do this) to basically return a result of count = 0 or count = 1

#1

select * from person where username = 'username'

From this query I want to get the users ID from the result. The column name is simply "id". I then want to use that ID in query 2

#2

select * from CCR where personFK = 'id'

This query is definitely one I need help with. I want to search by ID AND by datetime (column name = startTime). So, the value returned from the code I provided will be something like: id = 194, startTime = 2014-05-06 13:42:11.000


In the second query let's say I want to find user with ID of 194, date of 2014-05-06 (in MMDDYYY format), with a time stamp of 13:42. How can I do this all with one query? Did I forget something? Thanks in advance for the help!



Edit**:
So, I figured this much out:

declare @userIDVar varchar(50)
set @userIDVar = (select id from person where username = 'username')
select startTime from ccr where personFK = @userIDVar

This will return just the datetime (startTime) values for that user ID. Now I need to know how to add to that query to only search for a specify date and specific time. I also forgot to mention this is SQL Server 2005. But if someone gives me SQL Server 2008+ advice I can probably work with it.

[Rabbit]

Join the two tables together and filter for both conditions in the WHERE clause.

[Luk3r]

Rabbit, thanks for your reply. I've accomplished what I needed by using the below code:

declare @userIDVar varchar(50)
set @userIDVar = (select id from person where username = 'username')
select count(*) from ccr where [personFK] = @userIDVar and [starttime] between '20140506 15:04.000' and '20140506 15:04:59.999'

[ck9663]

You can also just JOIN the two tables...

select count(*) from ccr c
inner join person p on 
p.username = 'username' and [c.personFK] = c.id and [starttime] between '20140506 15:04.000' and '20140506 15:04:59.999'

Happy Coding!!!


~~ CK

[Luk3r]

ck9663, you are God sent! That's exactly what I was looking for. I had to change it just a little bit because the syntax was either wrong in SQL Server 2005 or I simply didn't explain my tables/columns well enough. Anyway, here's what I've got in case anyone else is interested:

select count(*) from ccr c
inner join person p on
p.username = 'username' and [personFK] = p.id and [starttime] between '20140506 15:04.000' and '20140506 15:04:59.999'