Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

one-to-many relationship select query Expand / Collapse
Author
Message
Posted Friday, July 30, 2010 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:50 PM
Points: 6, 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...
Post #961609
Posted Friday, July 30, 2010 2:22 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 18, 2011 9:26 AM
Points: 10, 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?
Post #961624
Posted Friday, July 30, 2010 2:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #961629
Posted Friday, July 30, 2010 9:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:50 PM
Points: 6, 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.
Post #961684
Posted Saturday, July 31, 2010 11:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #961776
Posted Saturday, July 31, 2010 4:57 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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

Post #961815
Posted Saturday, July 31, 2010 7:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #961836
Posted Saturday, July 31, 2010 8:39 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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
Post #961841
Posted Saturday, July 31, 2010 9:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #961845
Posted Saturday, July 31, 2010 9:27 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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.

Post #961846
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse