To check presence of child and content

Discussion in 'MySQL' started by kalaid, Jul 19, 2007.

0
  1. #1
    Hi,
    In my database I have created table type and content. In type table I have maintained list of parent category and child category , below which I have have to add content.In content table I have list of content added below each category.
    Fields in type table -id,name,parentid
    Fields in content table-id,tid ,title

    I want to join two tables and I have to check for the presence of child category if present or content.

    I have tried with the following query
    mysql> select name,title from type left join content on (type.id=content.tid) wh
    ere parentid=$id;

    -------+-----------------+
    name | title |
    -------+-----------------+
    book | Mailer |
    book | calender Script |
    book | Data |
    book2 | NULL |
    -------+-----------------+
    Problem is I have got the parent category name instead of child category name. Plz help me solve this issue
     
    kalaid, Jul 19, 2007 IP
  2. coldfire7

    coldfire7 Peon

    Messages:
    504
    Likes Received:
    30
    Best Answers:
    0
    Trophy Points:
    0
    #2
    i didnt understood exactly wht u want.....but i think ur query is not so difficlut.
    Can u Just put the Two tables' fields and the result you require?
     
    coldfire7, Jul 19, 2007 IP
  3. kalaid

    kalaid Guest

    Messages:
    33
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Table:Type
    Fields
    id name parentid
    1 HTML 0
    2 PHP 0
    3 HTML script 1
    4 PHP script 1
    5 Free script 4
    Table: Content
    Fields
    cntid title tid desc
    1 Mailer script 5 Script to send mail
    2 Rating script 5 Script to rate


    I want to select the child of selected parent or select title from content if there is no child and content is present

    select name,title from type left join content on (type.id=content.tid) wh
    ere type.parentid=$id;

    I want to display the child name when the child is present for the passed id.
    otherwise I want to display title of content if child is not present for id passed.

    Above query works fine to select child name(if present) but the title is not selected in the absence of child and presence of content.
     
    kalaid, Jul 19, 2007 IP
  4. coldfire7

    coldfire7 Peon

    Messages:
    504
    Likes Received:
    30
    Best Answers:
    0
    Trophy Points:
    0
    #4
    there is no connection of parentID with contentID and u want to slect title from content from ultimately parentID. U need to define a new relationship i think between content and parent.
    How can u select title if there is no content/contid agaisnt the tid ? Am I getting right or not?
    i tried these on ur given data

    SELECT Content.Title, Type.Name
    FROM Type LEFT JOIN Content ON Type.tID=Content.cntID
    WHERE Type.parentID=1;
    it returned two results with two names for parentID=1 and no title(because there was no title defined for tid=1)
     
    coldfire7, Jul 20, 2007 IP
  5. kalaid

    kalaid Guest

    Messages:
    33
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Hi
    give me a suggesion to get the result
     
    kalaid, Jul 20, 2007 IP
  6. coldfire7

    coldfire7 Peon

    Messages:
    504
    Likes Received:
    30
    Best Answers:
    0
    Trophy Points:
    0
    #6
    (i may be wrong as i havnt understood completely what u are upto)

    if u use the same query and also add some content data as

    2, ABC script, 1, Script to ratexyz

    than the query will obviously return the title as well. What I am saying is for the given parentID, if u dont have the Content for the given Type, there would be no title value returned.
     
    coldfire7, Jul 20, 2007 IP