?
Solved

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

Posted on 2003-03-22
22
Medium Priority
?
208 Views
Last Modified: 2010-04-04
Hi,
I have four tables:
1-article(Aid,description,title,abstract); AID=P.K                           relation:
2-keywords( AID,keyword) ; aid+keyword=p.k , aid=f.k              article 1->n student , Keywords màn article
3-student(sid ,AID,sname,grade); sid=p.k, aid=f.k                     article màn teacher      
4-teacher(tid,aid,tname,support); tid+aid=p.k , aid=f.k

I want to show the article list( with special keyword) in web pages (as google page search)
After search for a special keyword(keywords). In my description about one article on html page  these fields should be exist:

1-Title
2-description
3-student names( pay attentionà student names)
4-teache names (pay attentionà teacher names)
5-abstract

Yes, this is a implementing of master-detail relationship between article  and student and teacher.
I don’t any question in how can communicate between Delphi and html creating.
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 .
can you write a special sql statement or any other works that include the student names for a special article(not repeated the article name)?
I m really expect to hear from you.

thanks in advance
hamid reza

0
Comment
Question by:123456
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 5
22 Comments
 
LVL 3

Expert Comment

by:LukA_YJK
ID: 8186607
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...
0
 
LVL 2

Author Comment

by:123456
ID: 8189080
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")

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8193209
can you show an output sample?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:LukA_YJK
ID: 8196443
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!
0
 
LVL 2

Author Comment

by:123456
ID: 8196553
meiki,
I don’t undestand what is your purpose about sample.
I should bring a sample about what ?

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8196911
>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 ;-)
0
 
LVL 3

Accepted Solution

by:
LukA_YJK earned 300 total points
ID: 8200522
Hamid, as Meikl said it is normal behaviour of SQL. So maybe better use a Query for finding Article IDs containing required keyword and two Tables connected to it by Master-Detail relation. Then write simething like that:

ArticleQuery.First;
while not ArticleQuery.EOF do begin
  OutputArticleInfo();
  TableTeacher.First;
  while not TableTeacher.EOF do begin
    OutputTeacherInfo()
    TableTeacher.Next;
  end;
  TableStudent.First;
  while not TableStudent.EOF do begin
    OutputStudentInfo()
    TableStudent.Next;
  end;
  ArticleQuery.Next;
end;
0
 
LVL 2

Author Comment

by:123456
ID: 8200997
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?
0
 
LVL 3

Expert Comment

by:LukA_YJK
ID: 8201365
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 ;)
0
 
LVL 2

Author Comment

by:123456
ID: 8201837
any feed back from you, meiki?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8201915
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 ;-)
0
 
LVL 2

Author Comment

by:123456
ID: 8201977
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

 



0
 
LVL 3

Expert Comment

by:LukA_YJK
ID: 8208190
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...
0
 
LVL 2

Author Comment

by:123456
ID: 8208847
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.
0
 
LVL 3

Expert Comment

by:LukA_YJK
ID: 8209285
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.
0
 
LVL 2

Author Comment

by:123456
ID: 8210091
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8210267
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 ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8210420
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 ;-)
0
 
LVL 3

Expert Comment

by:LukA_YJK
ID: 8219034
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!
0
 
LVL 2

Author Comment

by:123456
ID: 8236455
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

0
 
LVL 3

Expert Comment

by:LukA_YJK
ID: 8237244
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" ;)
0
 
LVL 2

Author Comment

by:123456
ID: 8237450
not related to pride.
this only related to knowledgement of experts in special issue .

hamid reza
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question