SQL problem

Dale Parish

Hey all,

Pretty new to web development (been at it for 2 months now) and I've hit a wall here;
I have 2 tables in a MySQL database at the moment, and their structures are like this:

Table 1 Table 2
ColA ColB Col A ColB
1 abc 1 NULL
2 def 2 NULL
3 ghi 3 NULL

Edit: Seems that the formatting didn't stick but basically theres 2 tables and 2 columns per table..
So as you can see they share a common value in Column A, but I would like to be able to syncronise data between them, so that Column B in Table 2 is the same as Column B in Table 1. I looked into Foreign keys but they require that the value on the other table is unique,which isn't possible since there may be repeat values.
Since the data in Column B on Table 1 may change often, simply copying it via a script isn't enough.

I'm more or less looking for something similar to a pointer in C++/C etc

Can anyone shed some light on this?
Thanks,
Dale Parish

fenderlvr

Why do you need the 2nd table if they're both going to be exactly the same. Why not just point everything to table 1?

Dale Parish

Because I am using the 2nd table for a different page, it has more then 2 columns, so the data does vary.

FSL

What is preventing you from getting the data you need for the second page from both the tables simultaneously.

fenderlvr

I think you could probably just use a join.

so in table 1 you have your col 1 (id column) and col 2 (the data). In table 2 you just need the id and then whatever extra data you have in table 2.

Then do something like...

SELECT * FROM table2 JOIN table1 ON table2.col1 = table1.col1

You may run into problems because you say there are repeat values. Which table has the repeat values? If table2 has multiple ID values that match up to 1 row on table1 then that is ok. If it's the other way around then you might have to change the query a little.

Is this sort of what you're looking for?

Dale Parish

I probably left out a critical detail, I do not want to call the first table because the rows contain too much data, hence why I want to read a column from one of those rows based on a row in the second table

fenderlvr

I don't think I understand what you want to do then.

You say you want to copy it over, but you also say that you want it to be like a c style pointer because you don't want to copy it.

The select with join would be the c style pointer way. It just points to the table1 copy of the data.

Generally speaking, duplication is a sign of bad database design. I think you want to go with the select/join method. But if you really want to copy the data over you could run a SQL update like this...

UPDATE table2
SET
	ColB = table1.ColB
FROM table2
	JOIN table1 ON table1.ColA = table2.ColA

You'd have to run this often otherwise the two will be out of sync.

What I think you probably want is a trigger. You can create a trigger on table1 that updates table2 anytime you update table1. You'll have to figure this out because I've never had the need for a trigger, don't know them well.

Liam Byrne

Dale Parish wrote: »

I probably left out a critical detail, I do not want to call the first table because the rows contain too much data, hence why I want to read a column from one of those rows based on a row in the second table

Why not just select the two columns that you need from that table ?

You do know that you don't have to do a "SELECT * FROM table" ?

Page 1 can have "SELECT col1, col2 FROM table"
Page 2 can have "SELECT * FROM table"

Dale Parish

Liam Byrne wrote: »

Why not just select the two columns that you need from that table ?

You do know that you don't have to do a "SELECT * FROM table" ?

Page 1 can have "SELECT col1, col2 FROM table"
Page 2 can have "SELECT * FROM table"

I wasn't aware of that :pac:
Cheers!

croo

Database "Views" might also interest you.
http://techonthenet.com/sql/views.php

Dale Parish

croo wrote: »

Database "Views" might also interest you.
http://techonthenet.com/sql/views.php

Perfect!
Thanks for that!