easy join question

blakey404

her is a simplified version of my problem... 2 tables... CUSTOMER customerId ... ORDER orderId customerId ... the relationship is clearly one (customer) to many (order) i want to do a join which will only bring back firt order found. so select * from customer inner join order on order.customerId = customer.customerId but in my "bigger" query this brings back all orders for each customer. so do any outer joins. IDEAS? Thanks in advance Anthony "having a thick day" Blake

Colin Angus Mackay

SELECT TOP 1 ...

Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website

blakey404

just gives the first customer - i want the first order found for each customer cheers anthony

Pete OHanlon

You could always try this:

select * from customer
inner join order a on a.customerId = customer.customerId
and a.orderId = (SELECT First 1 b.orderID FROM order b WHERE b.customerID = customer.customerId)

It's not going to be the most efficient SQL ever, but it should do the trick.

Please visit http://www.readytogiveup.com/ and do something special today. Deja View - the feeling that you've seen this post before.

blakey404

i will try something like that... i think that it will make a difference in speed but the overall query i'm working on returns 100,000+ rows so i'll have to just try and find out. cheers.

DQNOK

Rename your ORDER table to ORDERS since ORDER is a keyword (ORDER BY) that conflicts. I also took the liberty of renaming CUSTOMER to CUSTOMERS (since all table names should probably reflect a "plural" sense). The key is to form a "virtual" orders table that only contains the first order for each customer, and then join it to the CUSTOMERS INNER JOIN ORDERS table to get the other fields you want while eliminating records you don't want. Using the vendor-specific TOP keyword should work, but a more standard SQL way might be:

SELECT * 
FROM       (ORDERS AS O
INNER JOIN CUSTOMERS AS C ON O.customerId = C.customerId)
INNER JOIN (SELECT customerId, MIN(orderId) AS M
            FROM ORDERS
            GROUP BY customerId) AS GB ON O.orderId = GB.M;

Assuming your orderId is incresing, this will give it to you. The GROUP BY is how to eliminate all the rows you don't want. HOWEVER, you'll notice that the output reproduces O.customerId, C.customerId, and GB.customerId, all of which are redundant. The only way I know to preventing listing them all is to spell-out the fields you want instead of using '*'. Hope this helps. David

blakey404

Thats great. Don't worry about the table names - they were hyperthetical - trust me postng the actual query wouldnt have provoked any response it's huge.