SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


one-to-many relationship select query


one-to-many relationship select query

Author
Message
helpdesk-1100431
helpdesk-1100431
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 21
Hello to all,

I am a having trouble in querying one-to-many relationship. let me describe the scenario here. my table structure is like this.

Table A
A_ID F_Name M_Name L_Name
1 A B C
2 X Y Z

Table B
B_ID A_ID City State Zip
1 1 xxx TN 37013
2 1 yyy TX 37012


Now i want to select all the rows from Table A and only first respective row from Table B.

I appreciate all the help...
Mike4King
Mike4King
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35

SELECT *
FROM TABLE_A AS A
LEFT JOIN TABLE_B AS B ON A.A_ID = B.A_ID



Is that what you're looking for?
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9433 Visits: 25280
helpdesk-1100431

First of all to get tested answers please post your table definitions along with sample data as outlined in the first link in my signature block. It would be something like the following:

CREATE Table #A (A_ID INT,F_Name VARCHAR(2),   M_Name VARCHAR(2),L_Name1 VARCHAR(2))
INSERT INTO #A
SELECT 1,'A','B','C'UNION ALL
SELECT 2,'X','Y','Z'

CREATE Table #B(B_ID INT,A_ID INT, City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10))
INSERT INTO #B
SELECT 1,1,'xxx','TN','37013' UNION ALL
SELECT 2,1,'yyy','TX','37012'



Now if the above is correct, then it would have been easy for Mke4King to test his suggested solution:
SELECT * 
FROM TABLE_A AS A
LEFT JOIN TABLE_B AS B ON A.A_ID = B.A_ID



Which produces:

A_ID F_Name M_Name L_Name1 B_ID A_ID City State Zip
1 A B C 1 1 xxx TN 37013
1 A B C 2 1 yyy TX 37012
2 X Y Z NULL NULL NULL NULL NULL



Now is MIke4King's solution the one you are looking for? If not please post again and some one will be able to give you a tested solution.

Another question. What defines the first row in Table B? Remember there is no explicit guarantee that rows return by a SELECT statement are returned in the order in which the rows were inserted.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
helpdesk-1100431
helpdesk-1100431
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 21
Hello,

SELECT * 
FROM TABLE_A AS A
LEFT JOIN TABLE_B AS B ON A.A_ID = B.A_ID



is not something i am looking for. as this query repeats the value from table A.

I do not want to repeat my master information from Table A. And as to answer
Another question. What defines the first row in Table B? Remember there is no explicit guarantee that rows return by a SELECT statement are returned in the order in which the rows were inserted.


We can have first row from Table B or who's create date time stamp is first.

does it make sense?.?

Thanks for makin efforts for me.
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9433 Visits: 25280
who's create date time stamp is first.

I revised table B by adding a column and an additional vaue of A_ID:

CREATE Table #B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10),Entered DATETIME)
INSERT INTO #B
SELECT 1,1,'xxx','TN','37013',GETDATE() UNION ALL
SELECT 2,1,'yyy','TX','37012',GETDATE() - 40 UNION ALL
SELECT 2,2,'aaa','UT','44030', GETDATE() - 5



Then try this code:
;with numbered as(SELECT rowno=row_number() over
(partition by A_id order by entered ASC),A_Id,B_Id,city,state,zip,entered from #B) --
SELECT A.F_name,A.M_Name,A.L_Name1, N.A_id,N.City, N.State, N.Zip,N.Entered
FROM numbered AS N
INNER JOIN #A AS A ON A.A_id = N.A_id WHERE rowno = 1



Results:
F_name   M_Name   L_Name1   A_id   City   State   Zip   Entered
A B C 1 yyy TX 37012 2010-06-21 11:42:13.187
X Y Z 2 aaa UT 44030 2010-07-26 11:42:13.187


If this is not what you require please repost listing what I missed, or how I misundersood your requirements.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Nevyn
Nevyn
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1934 Visits: 3149
helpdesk-1100431 (7/30/2010)
Hello to all,

I am a having trouble in querying one-to-many relationship. let me describe the scenario here. my table structure is like this.

Table A
A_ID F_Name M_Name L_Name
1 A B C
2 X Y Z

Table B
B_ID A_ID City State Zip
1 1 xxx TN 37013
2 1 yyy TX 37012


Now i want to select all the rows from Table A and only first respective row from Table B.

I appreciate all the help...


There may be a prettier way, but this should accomplish what you want:


SELECT *
FROM Table_A a
LEFT OUTER JOIN
(SELECT B_ID,A_ID,City,State,Zip
FROM Table_B b
INNER JOIN
(SELECT A_ID,bid=(min B_ID)
FROM Table_B
GROUP BY A_ID) as inner
ON inner.bid=b.B_ID) as outer
ON a.A_ID=outer.A_ID


bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9433 Visits: 25280
Nevyn changed my create table statements to use your table names and ran your code:
SELECT * 
FROM Table_A a
LEFT OUTER JOIN
(SELECT B_ID,A_ID,City,State,Zip
FROM Table_B b
INNER JOIN
(SELECT A_ID,bid=(min B_ID)
FROM Table_B
GROUP BY A_ID) as inner
ON inner.bid=b.B_ID) as outer
ON a.A_ID=outer.A_ID



Result: Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'B_ID'.

Wondering what I did incorrectly?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Nevyn
Nevyn
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1934 Visits: 3149
bitbucket-25253 (7/31/2010)
Nevyn changed my create table statements to use your table names and ran your code:
SELECT * 
FROM Table_A a
LEFT OUTER JOIN
(SELECT B_ID,A_ID,City,State,Zip
FROM Table_B b
INNER JOIN
(SELECT A_ID,bid=(min B_ID)
FROM Table_B
GROUP BY A_ID) as inner
ON inner.bid=b.B_ID) as outer
ON a.A_ID=outer.A_ID



Result: Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'B_ID'.

Wondering what I did incorrectly?


You didn't, I did.

I didnt test it or debug (wasnt at a sql server machine). Had some resrved words for alias names and incorrect brackets. Try the below

CREATE Table #Table_A (A_ID INT,F_Name VARCHAR(2),    M_Name VARCHAR(2),L_Name1 VARCHAR(2))
INSERT INTO #Table_A
SELECT 1,'A','B','C'UNION ALL
SELECT 2,'X','Y','Z'

CREATE Table #Table_B(B_ID INT,A_ID INT, City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10))
INSERT INTO #Table_B
SELECT 1,1,'xxx','TN','37013' UNION ALL
SELECT 2,1,'yyy','TX','37012'

SELECT *
FROM #Table_A a
LEFT OUTER JOIN
(SELECT B_ID,b.A_ID,City,State,Zip
FROM #Table_B b
INNER JOIN
(SELECT A_ID,bid=min (B_ID)
FROM #Table_B
GROUP BY A_ID) as derived2
ON derived2.bid=b.B_ID) as derived1
ON a.A_ID=derived1.A_ID

DROP TABLE #Table_A
DROP TABLE #Table_B



My only comment on your solution is that it won't show a ROW of A if there isnt a matching row in B. Can be fixed by making the join right outer and putting the where condition on COALESCE(rowno,1).

I should also add that mine doesnt work with your amended test data because you B_ID wasnt unique in it
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9433 Visits: 25280
Nevyn
I asked the OP a question :
Another question. What defines the first row in Table B? Remember there is no explicit guarantee that rows return by a SELECT statement are returned in the order in which the rows were inserted.

OP's reply:
We can have first row from Table B or who's create date time stamp is first.

In response to that I added a column to Table B
CREATE Table #B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10),Entered DATETIME)
INSERT INTO #B
SELECT 1,1,'xxx','TN','37013',GETDATE() UNION ALL
SELECT 2,1,'yyy','TX','37012',GETDATE() - 40 UNION ALL
SELECT 2,2,'aaa','UT','44030', GETDATE() - 5



at this point I got lost in the detail .... Wondering how you would modify your T-SQL to select based on earliest date?

Thanks for the advice on using a RIGHt OUTER JOIN and COALESCE(rowno,1).
will test that tomorrow ...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Nevyn
Nevyn
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1934 Visits: 3149
Here's how I would do it with the datestamp field:

CREATE Table #Table_A (A_ID INT,F_Name VARCHAR(2),    M_Name VARCHAR(2),L_Name1 VARCHAR(2))
INSERT INTO #Table_A
SELECT 1,'A','B','C'UNION ALL
SELECT 2,'X','Y','Z'

CREATE Table #Table_B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10),Entered DATETIME)
INSERT INTO #Table_B
SELECT 1,1,'xxx','TN','37013',GETDATE() UNION ALL
SELECT 2,1,'yyy','TX','37012',GETDATE() - 40 UNION ALL
SELECT 2,2,'aaa','UT','44030', GETDATE() - 5

SELECT *
FROM #Table_A a
LEFT OUTER JOIN
(SELECT B_ID,b.A_ID,City,State,Zip
FROM #Table_B b
INNER JOIN
(SELECT A_ID,bid=min (Entered)
FROM #Table_B
GROUP BY A_ID) as derived2
ON derived2.bid=b.Entered AND derived2.A_ID =b.A_ID) as derived1
ON a.A_ID=derived1.A_ID

DROP TABLE #Table_A
DROP TABLE #Table_B



Basically the same thing. Note that adding the AND derived2.A_ID =b.A_ID to my original will also fix the issue of duplicate B_IDs throwing it off.

This does fail on the case where 2 records of B with the same A_ID have identical timestamps. Id need more nesting to get around that (min datetime then min ID), so I think your row_number solution probably makes more sense for the date issue.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search