left join/inner join/outer join with repeat values

shripaldalal ( ) writes:
> 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.

If you are on SQL 2005, you could do like this:

SELECT CASE rn WHEN 1 THEN CustomerID ELSE '' END,
OrderID
FROM (SELECT CustomerID, OrderID,
rn = row_number() OVER(PARTITION BY CustomerID
ORDER BY OrderID)
FROM Orders
WHERE OrderDate BETWEEN '19970101' AND '19970401') AS s
ORDER BY CustomerID, OrderID

But as Dan said, this is really the task of a report writer.


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: left join/inner join/outer join with repeat values 

On Mar 3, 7:59 pm, Erland Sommarskog wrote:
> shripaldalal( ) writes:
> > 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.
>
> If you are on SQL 2005, you could do like this:
>
> SELECT CASE rn WHEN 1 THEN CustomerID ELSE '' END,
> OrderID
> FROM (SELECT CustomerID, OrderID,
> rn = row_number() OVER(PARTITION BY CustomerID
> ORDER BY OrderID)
> FROM Orders
> WHERE OrderDate BETWEEN '19970101' AND '19970401') AS s
> ORDER BY CustomerID, OrderID
>
> But as Dan said, this is really the task of a report writer.
>
> --
> Erland Sommarskog, SQL Server MVP, esq....RemoveThis@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Hi,

Thanks for the reply. I am using MSAccess/JET.

The problem is.....

I dont really require this for reports or anything......

I am doing this because I want to basically join three tables with a
left join / inner join

TABLE1, TABLE2, TABLE3

SELECT * from (table1 left join table2 on table1.id = table2.subid)
left join table3 on table1.id = table3.subid

it gives me:

table1.name table2.orders table3.contact_no
shripal dalal TV 12345678
shripal dalal TV 12345679
shripal dalal DVD 12345678
shripal dalal DVD 12345679
shripal dalal Fridge 12345678
shripal dalal Fridge 12345679
shripal dalal Laptop 12345678
shripal dalal Laptop 12345679
shripal dalal Phone 12345678
shripal dalal Phone 12345679

as you can see all the information is repeated

wat i want is:

table1.name table2.orders table3.contact_no
shripal dalal TV 12345678
shripal dalal DVD <blank>
shripal dalal Fridge <blank>
shripal dalal Laptop <blank>
shripal dalal Phone <blank>

please help thanks.

regards,
shripal dalal.
 >> Stay informed about: left join/inner join/outer join with repeat values 

shripaldalal ( ) writes:
> Thanks for the reply. I am using MSAccess/JET.

Then my friend you are in the wrong newsgroup. In this newsgroup we can
offer solutions for SQL Server. There are surely people here with
experience from Access that may be able to offer you a solution. However,
if you post in SQL Server newsgroup without even saying that you are
using Access, how would they be able to know what you are looking for?

> SELECT * from (table1 left join table2 on table1.id = table2.subid)
> left join table3 on table1.id = table3.subid
>
> it gives me:
>
> table1.name table2.orders table3.contact_no
> shripal dalal TV 12345678
> shripal dalal TV 12345679
> shripal dalal DVD 12345678
> shripal dalal DVD 12345679
> shripal dalal Fridge 12345678
> shripal dalal Fridge 12345679
> shripal dalal Laptop 12345678
> shripal dalal Laptop 12345679
> shripal dalal Phone 12345678
> shripal dalal Phone 12345679
>
> as you can see all the information is repeated

This is apparently because your join conditions are incomplete. But without
knowledge of your tables I can't say where the error is. There is a standard
recommendation for this type of queries, and that is you post:

o CREATE TABLE statements for your tables, including definitions of keys.
o INSERT statments with sample data that demonstrtes the problem with
your current query.
o The desired result given this sample.

I don't know what culture they have in the Access newsgroup, but I would
expect that they too appreciate this sort of information.

> wat i want is:
>
> table1.name table2.orders table3.contact_no
> shripal dalal TV 12345678
> shripal dalal DVD <blank>
> shripal dalal Fridge <blank>
> shripal dalal Laptop <blank>
> shripal dalal Phone <blank>

And this thing with the contract number being blank instead of repeated
is a fairly odd thing. The solution I posted was based on ANSI SQL, but
being ANSI does not mean implemented. The solution fot this in Access
is likely to be completely different, so I can only recommend you to
find an Access newsgroup.
--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: left join/inner join/outer join with repeat values