SQL Join

Question

Post reply

SQL Join

h_maksuda

SSC Eights!

Points: 966
More actions
April 15, 2010 at 9:57 am

#217883

Hi:
I have to write a query to select data from two tables(table A and table B). Both tables have a foreign key (with another 3rd table (table C), table A and B don't have any relation with each other.
Table A has a DetailDesc column and table B also has DetailDesc column, but for the same foreign key this DetailDesc data varies in both tables and for the same foreign key table A may has 2 DetailDesc and table B may 5 DetailDesc. Now I have to return whatever DetailDesc have in both table for each foreigh key. If one table has 2 DetailDesc and another table has 5 DetailDesc then I want to see in the query result that for table A two DetailDesc has data and other rest 3 are null, on the other hand table B will show 5 DetailDesc data and vice varsa.
I wrote a query which is not return data the same way I want to see.
SELECT A.DetailID, A.DetailDesc, B.DetailID, B.DetailDesc
FROM A FULL OUTER JOIN
B ON A.C_ID= B.C_ID
my output is somthing like this:
A.DetailID A.DetailDesc B.DetailID B.DetailDesc
1 • Supplier Management 3 • Increase the ratio of supported applications
2 • Delivery Capacity 3 • Increase the ratio of supported applications
3 • Capacity Generated 3 • Increase the ratio of supported applications
4 • Project Delivery 5 • Status Report
4 • Project Delivery 6 • Continuity Report
4 • Project Delivery 7 • Assessment Report
4 • Project Delivery 8 • Surveys Report
5 • Business Service Delivery 5 • Condition Status Report
5• Business Service Delivery 6 • Business Continuity Report
5• Business Service Delivery 7 • Risk Assessment Report
For the same C_ID (C_ID=1)table A hase 3 data(first 3 rows- A.DetailID are 1,2,3) and table B has 1 data (B.DetailID is 3) so in the query result B.DetailID repetated 3 times.
Again table A has only one data for C_ID=2 and table B has 4 data so in the query result A.DetailID=4 repeated 4 times.
Is it possible to make the output like isteadt of repeating it shows null in query result?
Please need help. Code sample is highly appreciated.
Thanks in advance.
Maksuda...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Yes, it is possible to do what you want, but it's both difficult and a bad idea.

You should use the dataset from your query and do that kind of thing in the report or other front-end application.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

h_maksuda SSC Eights! Points: 966 More actions · Answer 2

Thanks SS for the reply. Actually I need it for creating some kind of report. I tired using dataset but

the alignment was bad for two seperate datasets. I don't know how to align them in that case.

Can you pls help me with a sample code.

Regards,

Maksuda....

WayneS SSC Guru Points: 95461 More actions · Answer 3

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

h_maksuda SSC Eights! Points: 966 More actions · Answer 4

yes ,I should provide scripts for the tables. Here is my script

CREATE TABLE [dbo].[C](

[C_ID] [int] NOT NULL,

[C_Name] [varchar](50) NULL,

CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED

(

[C_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[A](

[DetailID] [int] NOT NULL,

[DetailDesc] [varchar](50) NULL,

[C_ID] [int] NULL,

CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED

(

[DetailID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[A] WITH CHECK ADD CONSTRAINT [FK_A_C] FOREIGN KEY([C_ID])

REFERENCES [dbo].[C] ([C_ID])

GO

ALTER TABLE [dbo].[A] CHECK CONSTRAINT [FK_A_C]

GO

CREATE TABLE [dbo].(

[DetailID] [int] NOT NULL,

[DetailDesc] [varchar](50) NULL,

[C_ID] [int] NULL,

CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED

(

[DetailID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [FK_B_C] FOREIGN KEY([C_ID])

REFERENCES [dbo].[C] ([C_ID])

GO

ALTER TABLE [dbo]. CHECK CONSTRAINT [FK_B_C]

GO

--******************************************

INSERT INTO [C]

([C_ID] ,[C_Name])

VALUES(1,'FINANCE')

INSERT INTO [C]

([C_ID] ,[C_Name])

VALUES(2,'OPERATION')

INSERT INTO [C]

([C_ID] ,[C_Name])

VALUES(3,'CUSTOMER')

INSERT INTO [C]

([C_ID] ,[C_Name])

VALUES(4,'PEOPLE')

INSERT INTO [C]

([C_ID] ,[C_Name])

VALUES(5,'SERVICE')

--For Table A

INSERT INTO [A]