July 30, 2010 at 1:39 pm
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_IDF_NameM_NameL_Name
1ABC
2XYZ
Table B
B_IDA_IDCityStateZip
11xxxTN37013
21yyyTX37012
Now i want to select all the rows from Table A and only first respective row from Table B.
I appreciate all the help...
July 30, 2010 at 2:22 pm
SELECT *
FROM TABLE_A AS A
LEFT JOIN TABLE_B AS B ON A.A_ID = B.A_ID
[/code]
Is that what you're looking for?
July 30, 2010 at 2:38 pm
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_IDF_NameM_NameL_Name1B_IDA_IDCityStateZip
1ABC11xxxTN37013
1ABC21yyyTX37012
2XYZNULLNULLNULLNULLNULL
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.
July 30, 2010 at 9:40 pm
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.
July 31, 2010 at 11:03 am
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_nameM_NameL_Name1A_idCityStateZipEntered
ABC1yyyTX370122010-06-21 11:42:13.187
XYZ2aaaUT440302010-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.
July 31, 2010 at 4:57 pm
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 AA_IDF_NameM_NameL_Name
1ABC
2XYZ
Table B
B_IDA_IDCityStateZip
11xxxTN37013
21yyyTX37012
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
July 31, 2010 at 7:51 pm
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?
July 31, 2010 at 8:39 pm
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
July 31, 2010 at 9:23 pm
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 ...
July 31, 2010 at 9:27 pm
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.
August 1, 2010 at 6:56 am
Nevyn Posted Yesterday @ 11:27 PM
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.
Not near a machine with SQL Server, but much thanks for teaching the OP AND myself. I do appreciate your efforts to teach both of us some truly neat T-SQL.
Now I wish the OP had supplied us with the complete table definitions (indexes) etc., so we could look at the execution plans for both suggested solutions, then this would have been a great learning experience for the OP and myself.
Again I thank you for all your effort, and I am sure the OP shares my gratitude.
August 1, 2010 at 11:53 pm
Thanks you alll...
Above one is just perfect for my problem. And i will make my changes according to specification.
Thanks for making efforts for me... I appreciate all the work thanks again.... 🙂
August 2, 2010 at 8:18 am
Thanks all to make effort for me.
This one does work for me just perfect.
Once again thank you all and i really appreciate all the help... with 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply