no duplications in inner join / left join / right join query / JOIN HELP

no duplications in inner join / left join / right join query / JOIN HELP

Post by shripaldal » Sun, 04 Mar 2007 19:37:05


Hi,

I have two tables

table1:
========================
name sirname
===== ======
shripal dalal
deepak dalal

table2:
========================
name sirname
===== ======
ramesh dalal

now if i do a left join/inner join/right join (watever, i will take
left join as an example):

select table1.name, table2.name from table1 left join table2 on
table1.sirname = table2.sirname i get:

table1.name table2.name
========= ==========
shripal ramesh
deepak ramesh

now the above is wat i dont want.........

i want something like this:

table1.name table2.name
========= ==========
shripal ramesh
deepak <blank>

basically i just want to CONCATENATE TWO RECORDSETS....

something like:

select table1.name from table1 + select table2.name from table2 but
where sirname is equal in both tables

is this possible ?

regards,
shripal dalal.
 
 
 

no duplications in inner join / left join / right join query / JOIN HELP

Post by David Port » Sun, 04 Mar 2007 20:37:22


You've left a few questions unanswered, such as: What are the keys?
What if there is more than one person with the same name? Here's one
possibility:

SELECT t1.name, t2.name
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.sirname = t2.sirname
AND NOT EXISTS
(SELECT *
FROM table1
WHERE sirname = t1.sirname
AND name > t1.name);

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://www.yqcomputer.com/ (en-US,SQL.90).aspx

 
 
 

no duplications in inner join / left join / right join query / JOIN HELP

Post by shripaldal » Sun, 04 Mar 2007 21:19:52

n Mar 3, 4:37 pm, "David Portas"
< XXXX@XXXXX.COM > wrote:

hi david,

thanks for the reply.
basically what i need this for is:

there are two tables:

table1 is a master table, table2 is a detail table.

table1's primary_id is present in details_id of table2

table1 has only 1 record
table2 could have many records, usually upto 6 or seven

the problem is that when i inner join/left join/right join the above
tables in any way.......

the records of table1 repeat next to all records of table2, i know
this is a normal behaviour for any join

what i want is:

select table1.name, table2.orders from table1, table2 where table1.id
= table2.details

i get something like this:

name order
==== ========
shripal tv
shripal phone
shripal fridge
shripal dvd
shripal oven
shripal laptop

instead wat i want is:

name order
==== ========
shripal tv
<blank> phone
<blank> fridge
<blank> dvd
<blank> oven
<blank> laptop

is such a thing possible ? it could really save a lot of work.

regards,
shripal.