Some good Select or While

[Lukas]

Hi all

A have a problem with access query.
This is exemple what I want

I have two tables

Table1 - Product Table2 - Features
ID_product Param ID_features Param
P1 X1 F1 X1
P1 Y1 F1 Y1
P1 Z1 F2 Z1
P2 X1 F3 Y1
The way verification
<<--
Problem is:
1) Select some features and its Param
2) If this ALL Params (this one Features) are CONTAINS in Prams
some Product, take this Features

I want to get this Results

Some New Table
ID_product Features
P1 F1
P1 F2
P1 F3
P2 NULL

Any Idea??

 

[Jamie Collins]

SELECT
t1.ID_product,
t2.Features
FROM
Table1 As t1
LEFT JOIN
Table2 As t2
ON
t1.Param = t2.Param;

I don't think that's correct. Contrast the OP's results

with the results from your query

ID_product Features
P1 F1
P1 F3
P1 F1
P1 F2
P2 F1

The OP's row (P2, NULL) has me puzzled...

Jamie.

--

 

[Gary Walter]

SELECT
t1.ID_product,
t2.Features
FROM
Table1 As t1
LEFT JOIN
Table2 As t2
ON
t1.Param = t2.Param;

 

[Gary Walter]

X1 does not join Y1
so get Null (is what I thought)

 

[Lukas]

I don't want P2.
P2 was for example that P2 not JOIN..
Finaly I Want only

ID_product Features
P1 F1
P1 F2
P1 F3

Sorry my puzzle.....

SELECT
t1.ID_product,

[quoted text clipped - 5 lines]

ON
t1.Param = t2.Param;

I don't think that's correct. Contrast the OP's results

with the results from your query

ID_product Features
P1 F1
P1 F3
P1 F1
P1 F2
P2 F1

The OP's row (P2, NULL) has me puzzled...

Jamie.

--

 

[Lukas via AccessMonster.com]

From This query I don't get this fomat
ID_product Features
P1 F1
P1 F2
P1 F3

SELECT
t1.ID_product,
t2.Features
FROM
Table1 As t1
LEFT JOIN
Table2 As t2
ON
t1.Param = t2.Param;

Hi all

[quoted text clipped - 26 lines]

Any Idea??

 

[Gary Walter]

change JOIN to INNER
(the LEFT JOIN preserved the ID
when there was no match on join)

SELECT
t1.ID_product,
t2.Features
FROM
Table1 As t1
INNER JOIN
Table2 As t2
ON
t1.Param = t2.Param;

From This query I don't get this fomat
ID_product Features
P1 F1
P1 F2
P1 F3

SELECT
t1.ID_product,
t2.Features
FROM
Table1 As t1
LEFT JOIN
Table2 As t2
ON
t1.Param = t2.Param;

Hi all

[quoted text clipped - 26 lines]

Any Idea??

 

[Lukas via AccessMonster.com]

Thanks
but this query work OK if I want join only one t1.Param = t2.Param.
Maybe I write otherwise

Table2 - Features Table1 - Product
ID_features Param ID_product Param
F1 X1,Y1 P1 X1,Y1,Z1
F2 Z1 P2 X1
F3 Y1

E.G
1) Select some (first, second .....) FeaturesParam (e.g F1 - X1,Y1) -
It's OK I know how.
2) And next step is question - Are contains FeaturesParam (X1,Y1) in any
ProductParam (e.g P1 - X1,Y1,Z1)
In this instance ? Yes? Take ID_product (P1) and Features (F1) and go next
Features...

Results is
P1 F1
P1 F2
P1 F3

P2 is not in results because not exist Features with only one Param (X1)....

change JOIN to INNER
(the LEFT JOIN preserved the ID
when there was no match on join)

SELECT
t1.ID_product,
t2.Features
FROM
Table1 As t1
INNER JOIN
Table2 As t2
ON
t1.Param = t2.Param;

From This query I don't get this fomat
ID_product Features

[quoted text clipped - 21 lines]

Message posted via AccessMonster.com

 

[Jamie Collins]

change JOIN to INNER
(the LEFT JOIN preserved the ID
when there was no match on join)

As far as I can tell, the resultset would be the same.

I'm still puzzled on this one, possible a (English) language barrier
for the OP. Something to do with P1 and (F1, F2, F3) having ALL (X1,
Y1, Z1) ...?

Jamie.

--

 

[Lukas via AccessMonster.com]

Yes , combine all.
Explanatorily :

I have some parameters
X1 - New
Y1 - Black
Z1 - Big

I have some product with some parameters.
P1 - X1,Y1,Z1 (Book - New, Black, Big)

I have some features with some parameters.
F1 - X1, Y1 (both) (Horror - New, Black)
F2 - Z1 (Big Bookcase= Big)
F3 - Y1 (Room1=Black)

Through features prarameters I try, which products contains these features.

My example P1 book have this features:
Horror, Big Bookcase, Room1

 

[Jamie Collins]

Yes , combine all.

OK then, it sounds like you want to perform relational division (google
it), something like:

SELECT DT1.ID_product
FROM
(
SELECT DISTINCT
P1.ID_product,
F1.Param
FROM
Product AS P1
INNER JOIN
Features AS F1
ON
P1.Param = F1.Param
) AS DT1
GROUP BY DT1.ID_product
HAVING COUNT(DT1.Param) =
(
SELECT COUNT(D1.Param) FROM
(
SELECT DISTINCT F1.Param
FROM Features AS F1
) AS D1
);

Jamie.

--

 

[Lukas via AccessMonster.com]

Many Thanks....
Lukas

OK then, it sounds like you want to perform relational division (google
it), something like:

SELECT DT1.ID_product
FROM
(
SELECT DISTINCT
P1.ID_product,
F1.Param
FROM
Product AS P1
INNER JOIN
Features AS F1
ON
P1.Param = F1.Param
) AS DT1
GROUP BY DT1.ID_product
HAVING COUNT(DT1.Param) =
(
SELECT COUNT(D1.Param) FROM
(
SELECT DISTINCT F1.Param
FROM Features AS F1
) AS D1
);

Jamie.

--