TO KRETZSCHMAR OR GEOBUL (master-detail with sql or ...!?)

Salam, Hemid! Evvel bir ozununkulere dil ach da ;)
As ar as I understood you already did that:
SELECT A.Title, A.Description, S.SName, T.TName, A.Abstract
FROM Articles A, Keywords K, Students S, Teachers T
WHERE (A.AID = K.AID)AND(K.Keyword = "your keyword")AND(T.AID = A.AID)AND(S.AID = A.AID)
But ot repeats all article information for each techer/student. And SELECT DISTINCT is not a solution...
My teacher says me that there is a so called KISS-principle, which means "Keep It Simple, Stupid". So maybe you better use two Queries (or even Tables connected by Master-Detail relation with a Query):
One for finding out all articles containing the given keyword
SELECT A.Title, A.Description, A.Abstract
FROM Articles A, Keywords K
WHERE (A.AID = K.AID)AND(K.Keyword = "your keyword")
Second for techers and students for each article:
SELECT T.TName (S.SName)
FROM Techers T (Students S)
WHERE T.AID = "your AID" (S.AID = "your AID")
Hope it will help...

what is the mean of "you aid" in
SELECT T.TName (S.SName)
FROM Techers T (Students S)
WHERE T.AID = "your AID" (S.AID = "your AID")

can you show an output sample?

This part supposed be two queries: one for Teachers, one for Students.
SELECT T.TName
FROM Techers T
WHERE T.AID = "your AID"

SELECT S.SName
FROM Students S
WHERE S.AID = "your AID"

You can write a query parameter instead of "your AID" and connect these two queries to the 1st one as detail tables. So every time you get an ARTICLE from the first query these two queries will show the names of Teachers and Students working on this ARTICLE, correspondingly. Then you can iterate through them and output all names to your web-page under this ARTICLE and move to the next ARTICLE.
Hope it will help... Salamat qal!

meiki,
I don’t undestand what is your purpose about sample.
I should bring a sample about what ?

>but I cant write any sql statement that bring all of
>student names that create a article without any repeating >of article name so teacher names

thats a normal bahaviour of a sql-statement

>I should bring a sample about what ?

about how the data are shown on your html-page
(just alike printout as text)

or just, what do you have for a
problem with the first paragraph above?
how should it look?

meikl ;-)

About  
<<what do you have for a problem with the first paragraph above?
with pay attention to comments of  LUKA I write these sql statements


with adoquery1 do
begin
sql.Clear;
sql.add(format('select aid from article where aid in( select distinct(aid) from keywords where keyword in("%s","%s")',[str[1],str[2]]));
open;
end;
adoquery1.first;

  while not (adoquery1.eof) do
      begin
  with adoquery2 do
    begin
     sql.Clear;
     SQL.add(format('select Sname from student  where (AID="%s")',[adoquery1.FieldByName('AID').asstring]));
     open;
    end;
  //fill student(s) data to html page

  with adoquery3 do
     begin
       sql.Clear;
       SQL.add(format('select Tname from teacher  where (AID="%s")',[adoquery1.FieldByName('AID').asstring]));
       open;
     end;
// fill teacher(s) data   to html page
   adoquery2.close;
   adoquery3.close;
   adoquery1.next;
  end;
adoquery1.close


what is your idea about this coding?

One problem:
My adoquery1 was created dynamically with request of users( as search engine).
If we suppose have this keywords for searching:
Printer scanner +monitor
That show (articles which have printer or scanner keywords and should include monitor keyword >> pay attention to mean of this statement).I cant write sql statement.  
Can you write  appropriate sql statement for this problem?

Seems you are familiar with Master-Detail relation. I suppose to use it to connect your 2nd and 3rd queries to the 1st one, via SQL parameters. I don't use ADO, so I can't say much.
As for an SQL for the 1st query I think better generate the WHERE statement. Something like this:

var
  strSearch: string;
  strWhere: string;
  i,l: integer;
  c: char;
...
i := 1;
l := Length(strSearch);
while strSearch[i] = ' ' do inc(i);
while i<=l do begin
  c := strSearch(i);
  case c of
    '+': strWhere := strWhere + 'AND(Keyword="';
    ' ': begin
      strWhere := strWhere + '")OR(Keyword="';
      while strSearch[i+1] = ' ' do inc(i);
    end;
    else strWhere := strWhere + c;
  end;
  inc(i);
end;
...
sql.add('select aid from article where aid in( select distinct(aid) from keywords where '+ strWhere);
...
Hope it will help... and Hamid, would you talk directly to me at last ;)

any feed back from you, meiki?

i guess you are in good hands with luka,
i confirm luka's suggestions and i have
nothing to append to this

good work, luka :-)

meikl ;-)

thanks  LUKA_YJK,
suppose you have  these keywords
Printer scanner +monitor
only write the sql statement for these keywords without pay attentioning to keyword generatoring you do in your comment(i did it last days ago).
if you have these data in your keywprds table.

  AID  keyword
 ----  -------
 125   printer  
 126   scanner
 127   monitor
 125   monitor
 126   printer
 129   monitor
 
 my serach result should be this
 Aid  
 ----
 127
 125
 129

because my result article should include monitor key.

but why i dont speak with  your mother language?
1- arkadashemm ban turkey chokh basharmayam. ben iran da yashiyorum. yakhchee?


thanks

 

Thanks for nice words, Kretzschmar!
Hamid, I did not request to talk in my native language, of course :) but you did it. Men Azeriyem, gardash Agha, yakhshi! I just want you to speak to me ;)
Sorry for my omission, seems I did not make it out correctly. Disregard my previous comment, please. It was wrong :( Now it must work:

SELECT A.aid
FROM article A
WHERE (A.aid IN
 (SELECT aid FROM keywords WHERE keyword="monitor")
)
AND(A.aid IN
  (SELECT aid FROM keywords WHERE keyword IN ("scanner","monitor"))
)

Seems WHERE statement generation will be much simpler. I'll be back... hope...

This example say that my article should include  (+keyword)
Until have space keywords.the priority is with +keywords.
My example say that my article should  have monitor.

You may say why I use printer scanner in keyword entry?
A:Only when I have +monitor ,search for printer scanner in articles  for reducing results.

The coding of my purpose

If have(+keywords) then
Search for space keywords in result of +keywords.
Else if have(space keywords)
Search for space keywords in article
Else
Not found=true;

Yes, it is complicated.

If I wirte This example in keyword entery:
Printer scanner

Should return this result:
aid
------
125
126

thanks.

Suppose you have N keywords with plus and M without plus:
+kw1 +kw2 ... +kwN kw1 kw2 ... kwM
then it must work:
SELECT A.aid
FROM article A
WHERE ((A.aid IN
(SELECT aid FROM keywords WHERE keyword="+kw1")
)OR(A.aid IN
(SELECT aid FROM keywords WHERE keyword="+kw2")
)...OR(A.aid IN
(SELECT aid FROM keywords WHERE keyword="+kwN")
)
)AND(A.aid IN
 (SELECT aid FROM keywords WHERE keyword IN ("+kw1","+kw2", ... "+kwN", "kw1", "kw2", ... "kwM"))
)
So for "printer scanner" it will be
SELECT A.aid
FROM article A
WHERE
(A.aid IN
 (SELECT aid FROM keywords WHERE keyword IN ("printer","scanner"))

But I'm sure that it could be simplified. Now I must go, but I'll be back. Try this, please. Hope it will help.

But the problem was existed
Why?
The result of one +keyw0 don’t differ from +key1 to +keywN
Please pay attention the mean of in ,
Example you have two +keyw
+printer +scanner monitor
the result of this example include the result of this example
+printer monitor

for it this sql is wrong because the first example show articles which should have (+printer +scanner) together
but second sql show articles which should have (+printer ).

pay attention to  mean of  IN.
m.reza

the IN Clause is allways an implicite or clause like

someField IN (1,2,3)

is the same as

someField = 1 or
someField = 2 or
someField = 3

in conclosion to the problem above the
where should be like

(AID = (Select aid from KeyWordArticles where KeyWordId = (Select KeyWordId from KeyWords where KeywordName = 'Printer'))  and
AID = (Select aid from KeyWordArticles where KeyWordId = (Select KeyWordId from KeyWords where KeywordName = 'Scanner'))) or
AID = (Select aid from KeyWordArticles where KeyWordId = (Select KeyWordId from KeyWords where KeywordName = 'Monitor'))

not sure, if i matched the needs
(was a short look)

meikl ;-)

humm,

replace

AID = (...

with

AID IN (...

in all three lines

AID is not unique there

maybe there is better way with joining tables,
must closer look

meikl ;-)

Hamid, I just did it. Made a Table with your data and check out my SQL-s and it is working.
SELECT aid
FROM keywords
WHERE (aid IN
(SELECT aid FROM keywords WHERE keyword="monitor")
)
AND(aid IN
 (SELECT aid FROM keywords WHERE keyword IN ("scanner","monitor"))
)
returns
125
127
125
129
for scanner +monitor

SELECT aid
FROM keywords
WHERE (aid IN
 (SELECT aid FROM keywords WHERE keyword IN ("printer","scanner"))
)
returns
125
126
125
126
for printer scanner
of course SELECT DISTINCT will solve the "repeat" problem. As Kretzschmar said, there is probably more beautiful and better SQL, but I must go now... Salamat qal!

Hi luka,
I accept your answer as accepted( the master-detail sql)answer.
But one thing that you would consider in future:
If a person bring the name of special expert in  title of his(her) question,
Don’t add a comment because the questioner know that the skill of this expert is high in this issue .
Thanks for  every assisting.

thanks again of meikl.

Hamid reza

OK, Hamid! I accept your notice, of course.
But one thing that you would consider in future:
If you bring the name of a special expert in title of your(her) question, it may hurt the pride of other "expert" ;)

not related to pride.
this only related to knowledgement of experts in special issue .

hamid reza