Dual purpose cross ref table trouble.

[The PC Nerd]

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'));

[r937]

and your question was ... ?

[danfran]

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:

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

[The PC Nerd]

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:

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.

[r937]

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?

[The PC Nerd]

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)

[r937]

my head hurts from thinking about that query

i would like to suggest you re-model the relationships

[The PC Nerd]

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'));

[r937]

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

[The PC Nerd]

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

[r937]

i can't answer you because i have no idea what you're actually trying to do

don't you already have 3 tables?

[The PC Nerd]

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?

[r937]

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

[The PC Nerd]

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 )

[The PC Nerd]

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).

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)

[The PC Nerd]

I beleive I need what is called a subquery so Im looking that up now.