SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Tri-purpose "XREF Table" trouble

    Before you even read I wish to thank you for even considering assisting me

    Tables:

    Content - id, name.....
    Cross - c, t, crosstype (XREF Table)
    ContentType - id, title....
    ___

    I need to XReference elements from 2 different tables to each other, and to each of themselves in one XREF table.

    So...I set it up for Cross.type to define if an XREF table entry was a...

    0 -> ContentType <-> ContentType (to group types)
    1 -> Content <-> ContentType (for defining what kind of content)
    2 -> Content <-> Content (for relating content)

    ...type of cross reference.

    My ContentType table is filled with entries like "news", "downloads", and "old".
    My Content table is filled with entries like "News Update 12-04-22 - Blahblahahl"

    Query:

    SELECT `content`.`id`, `content`.`title` FROM `content`,`cross`

    //This next line finds Content that has a cross reference with a ContentType item identified by "A_TYPE_ID"

    WHERE (`content`.`id` = `cross`.`c` AND `cross`.`t` = 'A_TYPE_ID' AND `cross`.`crosstype` = '1')

    //The next group finds if the Content item "A_CONTENT_ID" is cross referenced with the content.id found previously.

    AND ((`content`.`id` = `cross`.`c` AND `cross`.`t` = 'A_CONTENT_ID' AND `cross`.`crosstype` = '2')
    OR (`content`.`id` = `cross`.`t` AND `cross`.`c` = 'A_CONTENT_ID' AND `cross`.`crosstype` = '2'));
    Last edited by The PC Nerd; Jan 15, 2005 at 17:06.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    and your question was ... ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so I can have content-type, content-content, and type-type cross references in one table.
    Dude, the scary part is that I almost understand what you are trying to do.. and they're called self-joins.. I recommend against it, in this case, because it's tripled.

    What ever tables you have that can use an automatic ID-numbering system, use them and keep 'em separate. Next, use "fact tables" to relate records from one table to another.

    This fact table of yours might look like this:

    Code:
    ContentID     TypeID       ContentTypeID
    ---------------------------------------
    1               2                7
    2               7                8
    5               NULL             7
    43              2                2
    BE CAREFUL that you don't produce duplicate records in here! (add a datecreated column or something to be safe, if you want..)

    Dan

  4. #4
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by danfran
    Dude, the scary part is that I almost understand what you are trying to do.. and they're called self-joins.. I recommend against it, in this case, because it's tripled.

    What ever tables you have that can use an automatic ID-numbering system, use them and keep 'em separate. Next, use "fact tables" to relate records from one table to another.

    This fact table of yours might look like this:

    Code:
    ContentID     TypeID       ContentTypeID
    ---------------------------------------
    1               2                7
    2               7                8
    5               NULL             7
    43              2                2
    BE CAREFUL that you don't produce duplicate records in here! (add a datecreated column or something to be safe, if you want..)

    Dan
    Ok now i know the word for it, fact table. I'm going to edit my first post with the words fact table where needed for clarity.


    The problem would lie where I would need to link two of the same table together (in a fact table) so that is why I made two fields for the id's and an additional field to identify if the link was...
    Two Contents
    Two ContentTypes
    Normal Link (Content<->ContentType)

    That way I would not need a fact table for each fact table type(as defined in the list above). But I might revert to that because of this.

    One thing i think you missed is that type (the one you used for typeid) is not a table but simply an identifyer for what kind of linkage was being performed between the two id's (as I wrote in the first paragraph) in the fact table.


    In reply to the first guy, yea i forgot to say the query failed. It never returns anything and doesnt have a mysql error.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm the first guy, and i still don't understand what you're trying to do

    that's the most important part of trying to debug a query that isn't working

    you have to know what it's supposed to be doing

    what exactly are you trying to get from the database with that query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ha thanks for being so patient, that's the second thing I didnt tell you guys. Well i said it but i didnt make it clear.

    I am looking up content that has a link to a specific ContentType.ID (defined as A_TYPE_ID in this portion of the query; also it should be known that cross.c is the field for Content.id in a normal cross reference and cross.t is the ContentTypeID field)

    WHERE (`content`.`id` = `cross`.`c` AND `cross`.`t` = 'A_TYPE_ID' AND `cross`.`type` = '1')

    Then I need to find any cross references between the previous content.id (found in the last portion of the query shown) with a specified Content.id (defined as A_CONTENT_ID)

    AND ((`content`.`id` = `cross`.`c` AND `cross`.`t` = 'A_CONTENT_ID' AND `cross`.`type` = '2')
    OR (`content`.`id` = `cross`.`t` AND `cross`.`c` = 'A_CONTENT_ID' AND `cross`.`type` = '2'));


    There are two in the last portion because it is a cross reference between two of the same table (content) so C and T are both content.id's so i need to find if there is a link Cross.C to Cross.T OR Cross.T to Cross.C

    Full Query
    SELECT `content`.`id`, `content`.`title` FROM `content`,`cross` WHERE (`content`.`id` = `cross`.`c` AND `cross`.`t` = 'A_TYPE_ID' AND `cross`.`type` = '1') AND ((`content`.`id` = `cross`.`c` AND `cross`.`t` = 'A_CONTENT_ID' AND `cross`.`type` = '2') OR (`content`.`id` = `cross`.`t` AND `cross`.`c` = 'A_CONTENT_ID' AND `cross`.`type` = '2'));

    This is brought development to a hault for me I'm actually kind of depressed about it because i cant really do much more without this.

    This is like an intersection for 6 roads without the intersection or stoplights. And then two roads that pass right by it just fine. (the permission and news fetching systems)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my head hurts from thinking about that query

    i would like to suggest you re-model the relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well there is one solution where I separate the queries and bind them with PHP.

    The other would be to make three tables for each type of relationship but i still think this wont help because the error s in the first part.

    What I need is for the first bold item to stay the same in both of the next bold items. And I think that is where it goes down the shhh...

    if only there was a way to save that variable then place it in the next.

    Actually i am completely sure that is where it dies because i am changing the cross.c and .t declarations later so that nullifies the first content.id being set.

    SELECT `content`.`id`, `content`.`title` FROM `content`,`cross` WHERE (`content`.`id` = `cross`.`c` AND `cross`.`t` = 'A_TYPE_ID' AND `cross`.`type` = '1') AND ((`content`.`id` = `cross`.`c` AND `cross`.`t` = 'A_CONTENT_ID' AND `cross`.`type` = '2') OR (`content`.`id` = `cross`.`t` AND `cross`.`c` = 'A_CONTENT_ID' AND `cross`.`type` = '2'));

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT content.id
         , content.title 
      FROM content
         , cross 
     WHERE (
           content.id = cross.c 
       AND cross.t = 'A_TYPE_ID' 
       AND cross.type = '1'
           ) 
       AND (
               (
               content.id = cross.c 
           AND cross.t = 'A_CONTENT_ID' 
           AND cross.type = '2'
               ) 
            OR (
               content.id = cross.t 
           AND cross.c = 'A_CONTENT_ID' 
           AND cross.type = '2'
               )
           )
    do you see in the WHERE clause the first 3 lines?

    you have cross.t = 'A_TYPE_ID'

    this means that the 1st part of the OR will never be true

    you also have cross.type = '1'

    this means that the 2nd part of the OR will never be true

    in other words, you have written a query that is guaranteed always to return 0 rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just edited my post saying that then read this.

    The only solution would be variables in mysql or querying all content in php then searching the second half of the query with variables in place.

    Or just making 3 tables. If i declared the tables different names could it work? (using AS) or does it process those names before it actually does the query

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i can't answer you because i have no idea what you're actually trying to do

    don't you already have 3 tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well im saying if I made 3 instances of CROSS then I should be able to make it work correct? Is there an efficient way of doing this?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    3 instances of CROSS? you mean, 3 instances in the query?

    i seriously doubt it

    but as i said before, i don't really know what you're trying to do

    i don't understand why the type column is in the cross table

    i would've thought "type" would be an attribute of the type of content something is (e.g. like news, comments, opinions, etc.) and therefore belong in the content table

    i don't understand how you would relate a news item to a comments item with one "type" code in the cross table

    i am totally in the dark as to what you're trying to do

    as i suggested, perhaps you should re-model the data relationships

    perhaps i should just shut up now
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    English has never been a skill worth claiming for myself.

    Ill try one more time to explain it now that I know what you are confused about.

    Cross.CrossType = Used to declare the different relationships in the fact table, Cross
    (you can have Content-Content, Content-ContentType, and ContentType-ContentType)

    ContentType not Content.Type which is non-existant = Used to declare types of Content (among other tables, but forget I said that so it doesnt become more confusing for outsiders of the programming )
    Last edited by The PC Nerd; Jan 15, 2005 at 17:07.

  15. #15
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok i've looked up some SQL terminology so now I am going to say what I meant.

    I need a Cross Reference Table aka XREF Table that is capable of linking more than just resources from two tables.

    So what I did was define a third field in the XREF table to define what kind of XREFerence was occuring. (Cross.CrossType)

    When Cross.CrossType is 0 it is a XREFerence between Content and ContentType. When it is 1 it is between two ContentType's. When it is 2 it is between two Content.

    So There you now understand my Cross table.


    Now the problem is that I need to find Content that has an XREF in the Cross table that links it to a certain ContentType (this variable is A_TYPE_ID). Then go ahead and find if there is also an XREF with the previously found content.id to a specified other content item(defined as A_CONTENT_ID).

    Code:
    SELECT content.id
         , content.title 
      FROM content
         , cross 
     WHERE (
           content.id = cross.c 
       AND cross.t = 'A_TYPE_ID' 
       AND cross.type = '1'
           ) 
       AND (
               (
               content.id = cross.c 
           AND cross.t = 'A_CONTENT_ID' 
           AND cross.type = '2'
               ) 
            OR (
               content.id = cross.t 
           AND cross.c = 'A_CONTENT_ID' 
           AND cross.type = '2'
               )
           )
    I now see why I never am returned any results but does anyone have any idea how to do what I am trying to perform? Without fetching the first half of the query then taking those results in php and performing the second half(out of the question)

  16. #16
    SitePoint Member
    Join Date
    Jan 2005
    Location
    FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I beleive I need what is called a subquery so Im looking that up now.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •