Help with a select statement from 2 tables pls

[John Cassell]

Hi There,

Can someone help me work out the correct way to achieve this pls..

I have 2 tables with data as in the image below. I have a query which says 'Display item & price'. It has two parameters, Item and CustCode.

If I select '001' as the item and 'A000' as the CustCode then it will display "001, £200.00" which is correct because I want either CustPrice if there is one or failing that the List_Price. However if I select '001' as the customer and 'Z001' as the custCode then it returns no records because it can't find Z001 in the tbl_CustPrices table (This is entirely possible for a customer to not appear in that tbl because it may never have had a special price)

My code is below, hope someone can help. Thanks

SELECT     tbl_Items.item, COALESCE (tbl_CustPrices.CustPrice, tbl_Items.List_Price) AS Price
FROM         tbl_Items CROSS JOIN
                      tbl_CustPrices
WHERE     (tbl_Items.item = @Param1) AND (tbl_CustPrices.CustCode = @Param2) AND (tbl_CustPrices.item = @Param1)

 

[John Cassell]

Hi Again, seems this one is a puzzler! Maybe I have overcomplicated my request, if someone thinks they can do this but is confused by something I've written can you let me know and I'll try to explain better. Could really do with this working.

Many thanks

John

 

[giadich]

SELECT     tbl_Items.item, COALESCE (tbl_CustPrices.CustPrice, tbl_Items.List_Price) AS Price
FROM         tbl_Items, tbl_CustPrices
WHERE     (tbl_Items.item = @Param1) AND (tbl_CustPrices.CustCode = @Param2) AND (tbl_CustPrices.item = @Param1

 

[John Cassell]

Thanks a lot for the reply giadich, however this leaves me with the same problem..

If I specify a customer for @Param2 that is not present in the tbl_CustPrices table then it returns no values (So A000 works but Z100 doesn't).

So I am sure the part where it says "AND (tbl_CustPrices.CustCode = @Param2)" needs to be changed because effectively it is demanding that it finds something in that table. I really don't know how to re-write this so it says "if custcode is present then show corresponding custprice otherwise show listprice from the items table"

Thanks very much

John

 

[John Cassell]

Hi Again, got the answer for those interested...

Select tblItems.Item,
       Coalesce(tblPrices.SpecialPrice, tblItems.ListPrice) As Price
From   tblItems
       Left Join @tblPrices tblPrices
          On tblItems.Item = tblPrices.Item
          And tblPrices.Customer = @param1

this now gives either the special customer price or the list price if customer has no special price.

Thanks anyway.

John