How to Create a Cross Reference Table/Query for My Data

How do I create a cross reference table/query for my data?

This seems like a fairly simple and common relational problem that is solved by a cross-reference table. For example:

CREATE TABLE dbo.Cards (
    id        INT            NOT NULL,
    name      VARCHAR(50)    NOT NULL,
    card_text VARCHAR(4000)  NOT NULL,
    CONSTRAINT PK_Cards PRIMARY KEY CLUSTERED (id)
)
GO
CREATE TABLE dbo.Card_Rulings (
    card_id        INT            NOT NULL,
    ruling_number  INT            NOT NULL,
    ruling_text    VARCHAR(4000)  NOT NULL,
    CONSTRAINT PK_Card_Rulings PRIMARY KEY CLUSTERED (card_id, ruling_number)
)
GO
CREATE TABLE dbo.Card_Ruling_Referenced_Cards (
    parent_card_id    INT    NOT NULL,
    ruling_number     INT    NOT NULL,
    child_card_id     INT    NOT NULL,
    CONSTRAINT PK_Card_Ruling_Referenced_Cards PRIMARY KEY CLUSTERED (parent_card_id, ruling_number, child_card_id)
)
GO
ALTER TABLE dbo.Card_Rulings
ADD CONSTRAINT FK_CardRulings_Cards FOREIGN KEY (card_id) REFERENCES dbo.Cards(id)
GO
ALTER TABLE dbo.Card_Ruling_Referenced_Cards
ADD CONSTRAINT FK_CardRulingReferencedCards_CardRulings FOREIGN KEY (parent_card_id, ruling_number) REFERENCES dbo.Card_Rulings (card_id, ruling_number)
GO
ALTER TABLE dbo.Card_Ruling_Referenced_Cards
ADD CONSTRAINT FK_CardRulingReferencedCards_Cards FOREIGN KEY (child_card_id) REFERENCES dbo.Cards(id)
GO

To get all card rulings for a card:

SELECT *
FROM dbo.Cards C
INNER JOIN dbo.Card_Rulings CR ON CR.card_id = C.id
WHERE C.id = @card_id

To get all cards referenced in a ruling by a given card:

SELECT C.*
FROM dbo.Card_Rulings CR
INNER JOIN dbo.Card_Ruling_Referenced_Cards CRRC ON CRRC.parent_card_id = CR.card_id
INNER JOIN dbo.Cards C ON C.id = CRRC.child_card_id
WHERE CR.card_id = @card_id

This was all off the top of my head and is not tested, so there might be syntactic errors, etc.

Your front end would be responsible for maintaining the references. This is probably desirable since it avoids the issue of someone forgetting to put quotes around a card name in a ruling text, etc.

SQL How to Create a View Using Cross Reference Tables

Creating a view will be a good option. Views works as virtual table which you can re-use in different quires.

To create view you need to write query like following.

CREATE VIEW SomeView
AS
SELECT m.Code, m.[AD Departments],mi.Manual
FROM Dept_Mappings AS m 
JOIN Manuals AS mi
ON m.Code=mi.Code

If you just want to select two columns from the view, you can do it like following.

SELECT [AD Departments],Manual
FROM SomeView
--You can put where condtion here if you want.

SQL query to cross-reference the same table

I think the problem lies in the data type of the column actual_size. I suspect it's varcahr and in that case 14 is less than 2.

Your code works fine here.

Please check the updated solution for the varchar column (actual_size) HERE.

how to query sql cross-reference table

    SELECT tr.ID_TR,tr.field 
    FROM planning_requests pr 
    INNER JOIN training_requests tr 
    ON tr.ID_TR = pr.ID_TR 
    WHERE pr.ID_TR NOT IN 
      (
      SELECT cpr.ID_TR 
      FROM planning_requests cpr 
      WHERE trainer IS NOT NULL AND trainer <> 'FREE' 
      ) 
    GROUP BY ID_TR

Why am I getting a cross-reference error with my insert/select statement in single database

you do not need to write "my_schema.table2.name" as two "." inside the name will be interpreted as a database.schema.table.

Multiple Mysql Cross Reference Tables, or One (by adding an extra field storing reference type)

I think that the cross-reference table may be a matter of preference in this situation, but as your data sets grow it may become cumbersome for both yourself and for your growing needs of the application to continue to keep that up.

Rather, I would rewrite the queries above to eliminate subqueries and implied joins. I would also add an index on anything that gets evaluated.

A first-blush rewrite would look like this:

select * from prx_sportsitems psi
left join prx_tags_items pti on (pti.ownerid=psi.id)
left join prx_tags pt on (pti.tagid=pt.id)
where pt.tagname = 'aerobic'
and pti.ownertype='sportsitems'
order by psi.dateadded desc
limit 0,30

And will require indexes on the following columns:

• prx_sportsitems.id
• prx_tags_items.id
• prx_tags.id
• prx_tags.tagname
• prx_tags_items.ownertype

Also, don't sort unless you absolutely have to!

Best of luck!

---

Related Topics