3 tables. one-one relation. Having trouble with Select

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

3 tables. one-one relation. Having trouble with Select

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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?
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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.
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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.
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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.
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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'
Post Reply