• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

T SQL Query for 2 tables with one to many relationship

Hi Experts,

I need to query 2 tables that have a one to many relationship. Following are the tables:

tableA
*********
processID                 raceID                        raceName  
-------------               ---------                       -----------------------------------
6676                         34                               Men's 400 m relay - High School
6677                         102                             Junior girls' 100 m - Middle School
6678                          9987                          Boy's 100 m championship - Elem    

tableB
*********
processID         competeDate
-------------      -----------------------------------
6676                9/20/2011 1:45:40  
6676                9/20/2011 2:32:40
6678                9/20/2011 1:15:25
6678                9/21/2011 2:15:25
6677                8/20/2011 2:32:40
6677                8/20/2011 8:45:40
6677                8/20/2011 9:45:40    

As you can see, tableA has a one to many relationship to tableB. I need 2 queries, one to get all data for one processID and another to get all processID's, both sorted by date-time and second one by processID and date-time. This is what I need my data to look like:

1st query data:
-------------------
processID       raceID       raceName                                              competeDate
-------------     ---------      -----------------------------------                ------------------------
6676              34              Men's 400 m relay - High School            9/20/2011 1:45:40
6676              34              Men's 400 m relay - High School            9/20/2011 2:32:40

2nd query data:
--------------------
processID       raceID       raceName                                              competeDate
-------------     ---------      -----------------------------------                ------------------------
6676              34              Men's 400 m relay - High School            9/20/2011 1:45:40
6676              34              Men's 400 m relay - High School            9/20/2011 2:32:40
6677              102           Junior girls' 100 m - Middle School          8/20/2011 2:32:40
6677              102           Junior girls' 100 m - Middle School          8/20/2011 8:45:40
6677              102           Junior girls' 100 m - Middle School          8/20/2011 9:45:40
6678              9987         Boy's 100 m championship - Elem           9/20/2011 1:15:25
6678              9987         Boy's 100 m championship - Elem           9/21/2011 2:15:25

How would I need to write my 2 queries?
 
0
roger v
Asked:
roger v
  • 3
  • 3
  • 2
2 Solutions
 
ThomasianCommented:
First Query:
SELECT A.*, B.competeDate
FROM
	tableA As A INNER JOIN
	tableB As B ON A.processID=B.processID
WHERE A.processID=6676
ORDER BY B.competeDate

Open in new window

Second Query:
SELECT A.*, B.competeDate
FROM
	tableA As A INNER JOIN
	tableB As B ON A.processID=B.processID
ORDER BY A.processID,B.competeDate

Open in new window

Here's a good site to start learning SQL: http://www.w3schools.com/sql/sql_join_inner.asp
0
 
Dale BurrellDirectorCommented:
Seems like the same query would do both, just the first needs a processID restriction. Try:
declare @ProcessId varchar(38)

--set @ProcessId = 6676

select processID, raceID, raceName, competeDate
from tableA A, tableB B
where B.processID = A.processID
and (@ProcessId is null or A.processID = @ProcessId)
order by A.processId, B.competeDate

Open in new window

0
 
roger vAuthor Commented:
@Thomas & @dale:

To do an order by, can I use the column number? For example, if I want to sort by competeDate and it is column 6, wouldn't an 'order by 6' work? I tried it but it gives me an error.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
roger vAuthor Commented:
Never mind, it worked. It gave me a 'transport level error', but now it works.
0
 
ThomasianCommented:
Yes it should work. The order number should be based on the order in the SELECT statement. In the query (we provided), there are only 4 columns so to sort by competeDate you should use ORDER BY 4
0
 
roger vAuthor Commented:
@dale:

Your query as is, gave the 2nd query result set which is fine. To get the 1st query result set, do I need to have an additional 'AND' in the where clause?

Also, I don't understand this part:

(@ProcessId is null or A.processID = @ProcessId)

0
 
Dale BurrellDirectorCommented:
You learn something new everyday!

But I wouldn't do it that way, a) its not immediately obvious which column is being used and b) if you make any changes to the columns it could change the sort order.
0
 
Dale BurrellDirectorCommented:
Thats so you can use the same query for both situations, you either set @ProcessId or leave it null. To make the first query work all you need to do is uncomment the line starting -- set @ProcessId...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now