Join of tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join of tables

Author

Topic

kshyamasagar
Starting Member

6 Posts

Posted - 2007-01-05 : 13:34:12

hi,
i am facing an issue. i have got two tables say table1 and table2.
table1 has column1,2,3 ( has values a,b,c) where as table 2 has different columns 4,5,6(j,k,l).
column4 in table 2 has value which contains %+column1+% and it has two entires for each row in table1.the column4 value in table2 is different for all rows.When i am trying to join the two tables, i am getting values like

1 2 3 4 5 6
a b c j k l
a b c j1 k l
a1 b c j k l
a1 b c j1 k l

but i want to display
a b c k l
a1 b c k l

is there a way to do it.

My queries were
select distinct pc_assign_worklist.pxRefObjectInsName AS [pxRefObjectInsName],
pc_assign_worklist.pxUrgencyAssign AS [pxUrgencyAssign],
pc_assign_worklist.pyLabel AS [pyLabel],
pc_assign_worklist.pyAssignmentStatus AS [pyAssignmentStatus],
pc_assign_worklist.pxAssignedOperatorID AS [pxAssignedOperatorID],
CONVERT(char (11),pc_assign_worklist.pxCreateDateTime) AS [pxCreateDateTime],
pc_assign_worklist.pxCreateOpName AS [pxCreateOpName],
pc_index_workparty.MemberIdentifier AS [MemberIdentifier],
pc_index_workparty.LastName AS [Last Name],
pc_index_workparty.FirstName AS [First Name],
pc_index_workparty.pxInsName AS [pxInsName],
pc_index_workparty.pzInsKey AS [pzInsKey],
pc_index_workparty.pxpartySubscript as "Workparty"
from dbo.pc_assign_worklist
LEFT OUTER JOIN dbo.pc_index_workparty
ON pc_index_workparty.pzInsKey LIKE '%' + pc_assign_worklist.pxRefObjectInsName + '%'
where
pxAssignedOperatorID = 'dasxkx1'

i also tried left inner join but it always returns the same result seta.
I even created a view by inner join of two tables and then did left outer join on the second table . The result is same.

Can somebody tell whether this is achivable or not.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 13:47:43

In your description of what you want, you showed column4 as being left out of the result. If that is the case in the real query then using distinct will give you what you want, but if you are including the column/s that have the different values for the two rows then the two rows are distinct from each other and they will both be returned.

In that case, you need to decide which of the two rows you want to return, and what is the basis for choosing that row. Does it always have a larger or smaller value in one of the columns for example. Or do you want to just always keep the largest or smallest value from each row.

Lets say the two rows looked like this

a b c j k l m
a b c n k l i

Which of one these would you want the result to be?

a b c j k l m

a b c n k l i

a b c j k l i

a b c n k l m

As you can see from this, it starts to get tricky to use examples to figure out what you want in your real data, so I suggest you post the CREATE TABLE statements and some real test data and then I can help you much more quickly.

kshyamasagar
Starting Member

6 Posts

Posted - 2007-01-05 : 16:11:25

row 1: I-13393 10 HC interaction GHC-HPLAN-CS-WORK-INTERACTION I-13393!3!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK-INTERACTION I-13393!3!PROVIDERPARTYURI Provider
row2: S-13586 10 Review Member Eligibility GHC-HPLAN-CS-WORK S-13586!0!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!0!PARTYURI Member
row 3: S-13586 10 Review Member Eligibility GHC-HPLAN-CS-WORK S-13586!0!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!0!PROVIDERPARTYURI Provider

Above is the resultset of join

my table1 i.e ., pc_assign_worklist has only two rows corresponding to this.

row1: S-13586 10
row 2: I-13393 10

table2 i.e pc_index_workparty has multiple rows

GHC-HPLAN-CS-WORK I-13393!2!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK I-13393!2!PARTYURI CSR
GHC-HPLAN-CS-WORK I-13393!5!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK I-13393!5!PARTYURI Contact
GHC-HPLAN-CS-WORK I-13393!6!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK I-13393!6!PARTYURI Provider

GHC-HPLAN-CS-WORK S-13586!5!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!5!PARTYURI Subscriber
GHC-HPLAN-CS-WORK S-13586!6!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!6!PARTYURI Contact
GHC-HPLAN-CS-WORK S-13586!7!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!7!PARTYURI Member

I WANT TO SHOW ONLY
TWO ROWS IN RESULT SET WHICH CONTAINS SOME COLUMN FROM TABLE 1 AND SOME FROM TABLE 2.
My Rulust set should be distinct in the column1 values of table1.
i.e
row 1: I-13393 10 HC interaction GHC-HPLAN-CS-WORK-INTERACTION I-13393!3!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK-INTERACTION I-13393!3!PROVIDERPARTYURI Provider
row2: S-13586 10 Review Member Eligibility GHC-HPLAN-CS-WORK S-13586!0!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!0!PARTYURI Member

See if this example hellps you to provide some solution.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 17:27:44

You still haven't given the table structures and you haven't told me how you want to pick from the duplicate rows. I know you want
row2: S-13586 10 Review Member Eligibility GHC-HPLAN-CS-WORK S-13586!0!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!0!PARTYURI Member
and you don't want this
row 3: S-13586 10 Review Member Eligibility GHC-HPLAN-CS-WORK S-13586!0!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!0!PROVIDERPARTYURI Provider
but you haven't said how you decide which one you want and which one you don't want. I can't guess and SQL Server won't guess. You get exactly what you ask for in a query, so if you don't ask for a specific row you get them both. To get only one you can't say "just give me one", you have to say "give me the one that ...."

Something like this will work, but I can't give you the full query unless you tell me what I asked.

SELECT col1, col2, col3, min(col4), min(col5)
FROM table/joins
WHERE ...
GROUP BY col1, col2, col3

kshyamasagar
Starting Member

6 Posts

Posted - 2007-01-05 : 17:35:18

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 17:40:09

You just reposted the same data and it doesn't tell me anything!

Take a look at these three rows

GHC-HPLAN-CS-WORK I-13393!2!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK I-13393!2!PARTYURI CSR
GHC-HPLAN-CS-WORK I-13393!5!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK I-13393!5!PARTYURI Contact
GHC-HPLAN-CS-WORK I-13393!6!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK I-13393!6!PARTYURI Provider

Now, tell me which one of those you want and why. AND I have no idea what this data is, you say it comes from the pc_index_workparty table, but I do not know the structure of that table. Until you give me that I cannot help you any further.

kshyamasagar
Starting Member

6 Posts

Posted - 2007-01-05 : 18:10:08

i do not know how to send you tha table structure as MS SQL does not have descibe command.
I am sending you my queries
CREATE VIEW AssignIndex
AS
SELECT DISTINCT pc_assign_worklist.pxRefObjectInsName AS [pxRefObjectInsName],
pc_assign_worklist.pxUrgencyAssign AS [pxUrgencyAssign],
pc_assign_worklist.pyLabel AS [pyLabel],
pc_assign_worklist.pyAssignmentStatus AS [pyAssignmentStatus],
pc_assign_worklist.pxAssignedOperatorID AS [pxAssignedOperatorID],
CONVERT(char (11),pc_assign_worklist.pxCreateDateTime) AS [pxCreateDateTime],
pc_assign_worklist.pxCreateOpName AS [pxCreateOpName],
pc_index_workparty.MemberIdentifier AS [MemberIdentifier],
pc_index_workparty.LastName AS [Last Name],
pc_index_workparty.FirstName AS [First Name],
pc_index_workparty.pxInsName AS [pxInsName],
pc_index_workparty.pzInsKey AS [pzInsKey],
pc_index_workparty.pxpartySubscript as "Workparty"
FROM dbo.pc_index_workparty
LEFT OUTER JOIN dbo.pc_assign_worklist
ON pc_index_workparty.pzInsKey LIKE '%' + pc_assign_worklist.pxRefObjectInsName + '%'

this creates the view

Then i join them with table2.

SELECT DISTINCT AssignIndex.pxRefObjectInsName AS [pxRefObjectInsName],
AssignIndex.pxUrgencyAssign AS [pxUrgencyAssign],
AssignIndex.pyLabel AS [pyLabel],
AssignIndex.pyAssignmentStatus AS [pyAssignmentStatus],
AssignIndex.pxAssignedOperatorID AS [pxAssignedOperatorID],
CONVERT(char (11),AssignIndex.pxCreateDateTime) AS [pxCreateDateTime],
AssignIndex.pxCreateOpName AS [pxCreateOpName],
AssignIndex.MemberIdentifier AS [MemberIdentifier],
AssignIndex.pxInsName ,
AssignIndex.pzInsKey ,
AssignIndex.Workparty
FROM
AssignIndex WHERE
EXISTS ( SELECT * FROM pc_assign_worklist where pxAssignedOperatorID = 'dasxkx1' ) AND
AssignIndex.pxAssignedOperatorID = 'dasxkx1'

I want to display rows which contains the rows of
SELECT * FROM pc_assign_worklist where pxAssignedOperatorID = 'dasxkx1'

But as told by you sql does not know what to diaplay if u do not specify.

I am sending some more info.
my aim is to display the info from table1 and table2 (some columns from each table) such that the result does not contain any repeated row in column1. problem is the result set has some column values which are different for some column except column1, which is in table1 only. Not in table2.

My results
I-13389 10 HC interaction Open dasxkx1 Jan 4 2007 Kshyamasagar Das NULL GHC-HPLAN-CS-WORK-INTERACTION I-13389!0!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK-INTERACTION I-13389!0!PROVIDERPARTYURI Provider
I-13391 10 HC interaction Open dasxkx1 Jan 4 2007 Kshyamasagar Das NULL GHC-HPLAN-CS-WORK-INTERACTION I-13391!0!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK-INTERACTION I-13391!0!PROVIDERPARTYURI Provider
I-13393 10 HC interaction Open dasxkx1 Jan 4 2007 Kshyamasagar Das NULL GHC-HPLAN-CS-WORK-INTERACTION I-13393!3!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK-INTERACTION I-13393!3!PROVIDERPARTYURI Provider
I-13416 10 HC interaction Open dasxkx1 Jan 5 2007 Kshyamasagar Das NULL GHC-HPLAN-CS-WORK-INTERACTION I-13416!6!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK-INTERACTION I-13416!6!PROVIDERPARTYURI Provider
I-13416 10 HC interaction Open dasxkx1 Jan 5 2007 Kshyamasagar Das 00000006 GHC-HPLAN-CS-WORK-INTERACTION I-13416!5!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK-INTERACTION I-13416!5!PARTYURI Member
S-13586 10 Review Member Eligibility Open dasxkx1 Jan 4 2007 Kshyamasagar Das NULL GHC-HPLAN-CS-WORK S-13586!0!PROVIDERPARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!0!PROVIDERPARTYURI Provider
S-13586 10 Review Member Eligibility Open dasxkx1 Jan 4 2007 Kshyamasagar Das 00000006 GHC-HPLAN-CS-WORK S-13586!0!PARTYURI INDEX-WORKPARTYURI GHC-HPLAN-CS-WORK S-13586!0!PARTYURI Member

Table 1 has values:

dasxkx1 GHC HP - Service Member Services 2007-01-04 12:43:31.537 Kshyamasagar Das dasxkx1 wfe NULL NULL HCPMReviewMemberEligibility NULL NULL GHC-HPLAN-CS-WORK S-13586!HCPMREVIEWMEMBERELIGIBILITY NULL Assign-Worklist GHC-HPlan-CS-Work-MemberEligibility S-13586 GHC-HPLAN-CS-WORK S-13586 NULL ASSIGNMENT35 2007-01-04 12:42:36.633 NULL NULL NULL NULL NULL 10 CSC eBusiness Team Open NULL HCPMReviewMemberEligibility NULL Review Member Eligibility NULL NULL ASSIGN-WORKLIST GHC-HPLAN-CS-WORK S-13586!HCPMREVIEWMEMBERELIGIBILITY 0x5A6C4237000012E77801B5975B6C5455148617435BDA52CBC5825CEB4011B0D04A2F94721128BDD316EA4CCB1DA1A5D3520AA5B4055BA384F020440DA218543406351242880A9AF062A20986A8313C100463041E340688F1C1177DC0E0B7CE59C3996907A844A7F9D739E73F7BAFB3F6DAEBB25B1B28681291F85AC4A42291 NULL
dasxkx1 GHC HP - Service Member Services 2007-01-04 12:24:01.877 Kshyamasagar Das dasxkx1 wfe NULL NULL CAInteractionDriver NULL NULL GHC-HPLAN-CS-WORK-INTERACTION I-13389!CAINTERACTIONDRIVER NULL Assign-Worklist GHC-HPlan-CS-Work-Interaction-Call I-13389 GHC-HPLAN-CS-WORK-INTERACTION I-13389 NULL InitialProcessTask 2007-01-04 12:24:01.870 NULL NULL NULL NULL NULL 10 CSC eBusiness Team Open NULL CAInteractionDriver Select tasks to perform HC interaction NULL NULL ASSIGN-WORKLIST GHC-HPLAN-CS-WORK-INTERACTION I-13389!CAINTERACTIONDRIVER 0x5A6C42370000133C7801B5986D4C956518C72F1110019194484CE928A4664282F85EEA11E4455EE31C34DF5214450401410A5A35E78774AB9969EBC5BED82AD79C4BAB0FB5D656CBE6AAB5D65A6F6BD35A35A7AD0F7D2AD72AFB5DCF7D1D9F73E09036EBB0FF759EE7FF3CF7755FF77DBDDD8786C6792D2292D48098121419 NULL
dasxkx1 GHC HP - Service Member Services 2007-01-04 12:42:27.677 Kshyamasagar Das dasxkx1 wfe NULL NULL CAInteractionDriver NULL NULL GHC-HPLAN-CS-WORK-INTERACTION I-13391!CAINTERACTIONDRIVER NULL Assign-Worklist GHC-HPlan-CS-Work-Interaction-Call I-13391 GHC-HPLAN-CS-WORK-INTERACTION I-13391 NULL InitialProcessTask 2007-01-04 12:42:27.670 NULL NULL NULL NULL NULL 10 CSC eBusiness Team Open NULL CAInteractionDriver Select tasks to perform HC interaction NULL NULL ASSIGN-WORKLIST GHC-HPLAN-CS-WORK-INTERACTION I-13391!CAINTERACTIONDRIVER 0x5A6C42370000133C7801B598696C545514C70FA51B6D2D8B05416B1D4A05045A692965A902434B17BADA99826C42A1504A4B292DD5D6A8217C10120D2E1854FC8251893144013F688C89468C51628821A88404346A08183FF8498D71FB9D77CFF066DAA960D469FE67DEFBBF77CF3DF79EED4E1B9B4A5A4524A91131392832 NULL
dasxkx1 GHC HP - Service Member Services 2007-01-04 14:38:11.647 Kshyamasagar Das dasxkx1 wfe NULL NULL CAInteractionDriver NULL NULL GHC-HPLAN-CS-WORK-INTERACTION I-13393!CAINTERACTIONDRIVER NULL Assign-Worklist GHC-HPlan-CS-Work-Interaction-Call I-13393 GHC-HPLAN-CS-WORK-INTERACTION I-13393 NULL InitialProcessTask 2007-01-04 14:38:11.640 NULL NULL NULL NULL NULL 10 CSC eBusiness Team Open NULL CAInteractionDriver Select tasks to perform HC interaction NULL NULL ASSIGN-WORKLIST GHC-HPLAN-CS-WORK-INTERACTION I-13393!CAINTERACTIONDRIVER 0x5A6C42370000133C7801B598696C545514C70FA59B6D2D08A552C43A4005445AE9C2AEC0D09D96B676A64510844269292D6D69A9B6460DE1839A6810C1A0E2178C4B0C21B2F841634C3462881A638C712124A05163C0F8C14F6A8CDBEFBC7B8637D30E50A34EF33FF3DEFFBD7BEEB9F76C77DAD0B8A05544921A10D3822213 NULL
dasxkx1 GHC HP - Service Member Services 2007-01-05 13:40:29.370 Kshyamasagar Das dasxkx1 wfe NULL NULL TaskFlow NULL NULL GHC-HPLAN-CS-WORK-INTERACTION I-13416!TASKFLOW NULL Assign-Worklist GHC-HPlan-CS-Work-Interaction-Call I-13416 GHC-HPLAN-CS-WORK-INTERACTION I-13416 NULL ProcessWrapUp 2007-01-05 13:40:29.367 NULL NULL NULL NULL NULL 10 CSC eBusiness Team Open NULL CAWrapUp Finalize Interaction HC interaction NULL NULL ASSIGN-WORKLIST GHC-HPLAN-CS-WORK-INTERACTION I-13416!TASKFLOW 0x5A6C4237000012A87801B5985B6C54551486172D334C07AC0511A5601DA00202ADB4B405048BA5A5175ADAB13305416E8596528AA5CC506C891AC28B241AD49020626210A3218668D4441363A209C4A80F3C10BCC408929810313E1863220F78F9D639EB704EE920C4E034FF3AE7FC7BEFB5D7DEEBB237C45B2B3A44241447 NULL

I will try to send the structure once i have the command as how to see the strcture of table in MS SQL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-05 : 18:46:15

kshyamasagar, why don't you put your time and effort to normalize your tables instead?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources