Multiple Joins

Hello,

I need to create a view which links 5 tables as follows:
I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.

I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.

Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses.
Best regards
ColinIf there are no detail Product & Years which are not present in the Header table, then this sounds like a left outer join. The rest sound like inner joins.

If I'm understanding your intention, the following (untested) should work :

select h.*,
d.*,
s.*,
de.*
from Header h left outer join
Detail on h.product = d.product and h.year = d.year inner join
sales s on s.product = d.product and s.year = d.year and s.week = d.week inner join
Orders o on o.product = d.product and o.year = d.year and o.week = d.week inner join
Deliveries de on de.product = d.product and de.year = d.year and de.week = d.week

Perhaps I'm misunderstanding what you're wanting to see...

Multiple Filtering on the same field using a Stored Procedure

Hello,

I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below:

Input field: Product Description

So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned.

SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill"

I guess there'll need to be a nested Select and loop to parse the space separated input field.

Any pointers would be appreciated.

Thank you

Lee

hi dear,

I answer the same question on MSDN forum....you can reach this via this link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1448983&SiteID=1

Thank You

Best Regards,

Muhammad Akhtar Shiekh

|||

Thank you very much Muhammad

Problem solved.

multiple distinct and time...

Here comes the table:
Id_Prices | Shop | Product | Price | tStamp
----
--
1 eMall Pen 10 20.5.2005
2 eMall Pen 11 21.5.2005
3 eMall Pen 9 22.5.2005
4 webShop Pen 10 22.5.2005
5 webShop Pen 12 23.5.2005
6 InetShop Pen 10 20.5.2005
7 netOultel Pen 9 19.5.2005
8 netOultel Pencil 5 19.5.2005
9 netOultel Pencil 6 20.5.2005
table hold records of:
multiple PRICES (distinguished by date)
for various PRODUCTS
from various SHOPS
now...
A) I need to get only most curent (date) PRICES from all SHOPs
for specific PRODUCT e.q.
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
B) List of all products and their most recent prices...
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
Pencil netOultel 20.5.2005 6
Anybody can point me in the right direction how to aproach this...?
PS: DB table structure and normalization indexes omited for brewity
thanx for any hint... PetttHi ,
Try this :
create table #tmp
(
Id_Prices integer ,
Shop char(9),
Product char(6),
Price integer,
tStamp char (9)
) ;
---
insert into #tmp values (1, 'eMall','Pen',10,'20.5.2005');
insert into #tmp values (2, 'eMall','Pen',11,'21.5.2005');
insert into #tmp values (3, 'eMall','Pen',9,'22.5.2005');
insert into #tmp values (4, 'webShop','Pen',10,'22.5.2005');
insert into #tmp values (5, 'webShop','Pen',12,'23.5.2005');
insert into #tmp values (6, 'InetShop','Pen',10,'20.5.2005');
insert into #tmp values (7, 'netOultel','Pen',9,'19.5.2005');
insert into #tmp values (8, 'netOultel','Pencil',5,'19.5.2005');
insert into #tmp values (9, 'netOultel','Pencil',6,'20.5.2005');
---
select a.* , b.price
from
(
select product , shop , max (tstamp) tstamp
from #tmp
group by product , shop
) a ,
(
select *
from #tmp
) b
where a.shop= b.shop
and a.product = b.product
and a.tstamp = b.tstamp
-- and b.product = 'Pen'
group by a.product , a.shop , a.tstamp , b.price
order by 1
"Petr SIMUNEK" wrote:

> Here comes the table:
> Id_Prices | Shop | Product | Price | tStamp
> ----
--
> 1 eMall Pen 10 20.5.200
5
> 2 eMall Pen 11 21.5.200
5
> 3 eMall Pen 9 22.5.20
05
> 4 webShop Pen 10 22.5.2005
> 5 webShop Pen 12 23.5.2005
> 6 InetShop Pen 10 20.5.2005
> 7 netOultel Pen 9 19.5.200
5
> 8 netOultel Pencil 5 19.5.200
5
> 9 netOultel Pencil 6 20.5.200
5
>
> table hold records of:
> multiple PRICES (distinguished by date)
> for various PRODUCTS
> from various SHOPS
>
> now...
> A) I need to get only most curent (date) PRICES from all SHOPs
> for specific PRODUCT e.q.
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> B) List of all products and their most recent prices...
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> Pencil netOultel 20.5.2005 6
> Anybody can point me in the right direction how to aproach this...?
> PS: DB table structure and normalization indexes omited for brewity
> thanx for any hint... Pettt
>
>
>

multiple distinct and time...

Here comes the table:
Id_Prices | Shop | Product | Price | tStamp
----
--
1 eMall Pen 10 20.5.2005
2 eMall Pen 11 21.5.2005
3 eMall Pen 9 22.5.2005
4 webShop Pen 10 22.5.2005
5 webShop Pen 12 23.5.2005
6 InetShop Pen 10 20.5.2005
7 netOultel Pen 9 19.5.2005
8 netOultel Pencil 5 19.5.2005
9 netOultel Pencil 6 20.5.2005
table hold records of:
multiple PRICES (distinguished by date)
for various PRODUCTS
from various SHOPS
now...
A) I need to get only most curent (date) PRICES from all SHOPs
for specific PRODUCT e.q.
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
B) List of all products and their most recent prices...
Pen eMall 22.5.2005 9
webShop 23.5.2005 12
InetShop 20.5.2005 10
netOutlet 19.5.2005 9
Pencil netOultel 20.5.2005 6
Anybody can point me in the right direction how to aproach this...?
PS: DB table structure and normalization indexes omited for brewity
thanx for any hint... PetttHi
The difference between the first and second query is that you are
resistricting to be a single product. One possible solution would be:
CREATE TABLE #prices ( Id_Prices int, Shop varchar(20), Product varchar(20),
Price int , tStamp datetime )
INSERT INTO #prices ( Id_Prices, Shop, Product, Price, tStamp )
SELECT 1, 'eMall', 'Pen', 10, '20050520'
UNION ALL SELECT 2, 'eMall', 'Pen', 11, '20050521'
UNION ALL SELECT 3, 'eMall', 'Pen', 9, '20050522'
UNION ALL SELECT 4, 'webShop', 'Pen', 10, '20050522'
UNION ALL SELECT 5, 'webShop', 'Pen', 12, '20050523'
UNION ALL SELECT 6, 'InetShop', 'Pen', 10, '20050520'
UNION ALL SELECT 7, 'netOultel', 'Pen', 9, '20050519'
UNION ALL SELECT 8, 'netOultel', 'Pencil', 5, '20050519'
UNION ALL SELECT 9, 'netOultel', 'Pencil', 6, '20050520'
-- For Pen only
SELECT p.*
from #prices p
JOIN ( SELECT shop, product, Max(tstamp) as tstamp
from #prices GROUP BY shop, product ) q ON q.shop = p.shop AND q.product =
p.product AND p.tstamp = q.tstamp
WHERE p.product = 'Pen'
or alternatively:
SELECT p.*
from #prices p
WHERE tstamp = ( SELECT Max(tstamp)
from #prices q WHERE q.shop = p.shop AND q.product = p.product AND
q.product = 'Pen')
AND p.product = 'Pen'
-- For all products
SELECT p.*
from #prices p
JOIN ( SELECT shop, product, Max(tstamp) as tstamp
from #prices GROUP BY shop, product ) q ON q.shop = p.shop AND q.product =
p.product AND p.tstamp = q.tstamp
If you are using this join alot you may want to create a view.
John
"Petr SIMUNEK" wrote:

> Here comes the table:
> Id_Prices | Shop | Product | Price | tStamp
> ----
--
> 1 eMall Pen 10 20.5.200
5
> 2 eMall Pen 11 21.5.200
5
> 3 eMall Pen 9 22.5.20
05
> 4 webShop Pen 10 22.5.2005
> 5 webShop Pen 12 23.5.2005
> 6 InetShop Pen 10 20.5.2005
> 7 netOultel Pen 9 19.5.200
5
> 8 netOultel Pencil 5 19.5.200
5
> 9 netOultel Pencil 6 20.5.200
5
>
> table hold records of:
> multiple PRICES (distinguished by date)
> for various PRODUCTS
> from various SHOPS
>
> now...
> A) I need to get only most curent (date) PRICES from all SHOPs
> for specific PRODUCT e.q.
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> B) List of all products and their most recent prices...
> Pen eMall 22.5.2005 9
> webShop 23.5.2005 12
> InetShop 20.5.2005 10
> netOutlet 19.5.2005 9
> Pencil netOultel 20.5.2005 6
> Anybody can point me in the right direction how to aproach this...?
> PS: DB table structure and normalization indexes omited for brewity
> thanx for any hint... Pettt
>
>|||thanx a lot guys...
It works like a charm of course... thanks again
PS: would you care to share with me how long experience with SQL do you have
?
(so I know how long the road is... :o))
thanx|||Hi
It is a never ending road... when you think you know it all something new
always turns up...and then there is the ageing process where you start to
forget what you have learn't anyhow.
John
"Petr SIMUNEK" wrote:

> thanx a lot guys...
> It works like a charm of course... thanks again
> PS: would you care to share with me how long experience with SQL do you ha
ve
> ?
> (so I know how long the road is... :o))
> thanx
>
>sql