Page 1 of 1
3 tables. one-one relation. Having trouble with Select
Posted: Tue Feb 28, 2006 2:28 am
by jmut
hi,
I have three tables.
Main_table:
id autoincrement
type enum(type1,type2)
....fields common for the other 2 tables....
Type1_table:
id (one-one relation with MAin_table)
.....fields specific for type1_table
Type2_table:
id (one-one relation with MAin_table)
.....fields specific for type2_table
At first I got data with 2 selects:
Code: Select all
SELECT *
FROM Main_tables aa
INNER JOIN Ttype1_table ab
USING ( id )
and same for type2
But then I see I cannot use orderBY clause as results mix up.
And if I order by id....i take first type1 then type2 and it ultimately does not order things.
So I am thinking how could select whole data with one query...so I can orderby freely.
Tried this:
Code: Select all
SELECT * FROM Type1_table ab LEFT JOIN Main_Table aa USING (id )
RIGHT JOIN Type2_table at USING (id )
As I cannot use INNER JOIN because id in type1_table could not appear in type2_table.
Could someone help me with this issue? Thanks in advance for spending time.
Posted: Tue Feb 28, 2006 1:00 pm
by timvw
How much sense does it make you even try (I'd be surprised if you found a reliable way to do it anway) to fetch all the (different) data at once? As you stated, the data in both collections are different..
My advise:
- use one query if you want to select the "common" data
- use two queries if you want to select "all" data
Posted: Wed Mar 01, 2006 1:50 am
by jmut
timvw wrote:How much sense does it make you even try (I'd be surprised if you found a reliable way to do it anway) to fetch all the (different) data at once? As you stated, the data in both collections are different..
My advise:
- use one query if you want to select the "common" data
- use two queries if you want to select "all" data
But I am thinking it should be possible somehow.
For example the resultset will look like.
common_columns ________________ |
type1_table_columns |
type2_table_columns
common_data with id relating to type1_table | some data here | null values
common_data with id relating to type2_table | null values | some data here
Does not that make sense?
Posted: Wed Mar 01, 2006 2:01 am
by AGISB
Try the following
Code: Select all
SELECT a.primary, a.column1, b. column1, c.column1 FROM maintable a, secondtable b, thirdtable c
WHERE a.primary = '1' AND a.primary = b.primary AND a.primary = c.primary order by a.primary
I find it confusing to use joins when SELECTING 1to1 relationships
Posted: Wed Mar 01, 2006 4:08 am
by jmut
AGISB wrote:Try the following
Code: Select all
SELECT a.primary, a.column1, b. column1, c.column1 FROM maintable a, secondtable b, thirdtable c
WHERE a.primary = '1' AND a.primary = b.primary AND a.primary = c.primary order by a.primary
I find it confusing to use joins when SELECTING 1to1 relationships
does not work.
I tried this already
Ok...how would you design the tables to handle such situation. So that I don't have redundant data.
Posted: Wed Mar 01, 2006 7:56 am
by AGISB
The above should work as long as you have the main table primary key in all the 1to1 relationship tables.
If you got NULL values in a table you might want to use the IF NULL clause
If I would split hairs I have to state that a 1 to 1 relationship is already using redudant data when the primary key is in all of the 1to1 tables.
Posted: Wed Mar 01, 2006 8:47 am
by jmut
I have sample data like this:
main_table
id | type | name ..... | date_created | date_modified | other_common_data
4 | type2 | name_x
7 | type1 | name_y
type1
id| specific_type1_column
7 | some_data
type2
id | specific_type2_column
4 | some_other_data
Practically in type1 and type2 ids are unique respectively.
Could you please give me idea how would you build tables. I guess my design was poor.
Thank you in advance for spending time.
Posted: Thu Mar 02, 2006 7:57 am
by AGISB
jmut wrote:I have sample data like this:
main_table
id | type | name ..... | date_created | date_modified | other_common_data
4 | type2 | name_x
7 | type1 | name_y
type1
id| specific_type1_column
7 | some_data
type2
id | specific_type2_column
4 | some_other_data
Practically in type1 and type2 ids are unique respectively.
Could you please give me idea how would you build tables. I guess my design was poor.
Thank you in advance for spending time.
That does not look like a 1to 1 relationship.
Here is an example of a 1 to 1 relationship
main table
id
datafield1
datafield2
second table
id
moredata1
moredata2
third
id
evenmoredata1
evenmoredata2
1to1 relationship is when you for example got an entry with ID=5 in main table that is connected to the id=5 entry in second table and with id=5 entry in third table.
What you are trying to achive is a 1 to many relationship
lets say datafield1 in main table has a possibility of 2 values (type1 or type2)
So one intitial ID can have many ID's (here 2) those are called foreign keys
now you got another table
fourthtable
newID
newdatafield
now this is filled with the specifics of the types (example a payment solution)
newid=1 newdatafield=wire transfer
newid=2 newdatafield=credit card
Now you could check the info by
Code: Select all
SELECT a.datafield1, b.newdatafield FROM maintable a, fourthtable b WHERE ID='5'