mysql Table Select

[Elpetha Brooke]

Hi

I have two tables

TABLE1
t1_id int not null
t1_other stuff blah blah

TABLE2
t2_id int not null
t2_t1_id int not null
t2_other stuff blah blah

the link between the tables is t1_id --> t2_t1_id

There is a list of items with ID 1-10 in TABLE1
in TABLE2 there is a list of items with the t2_t1_id 1-5

What I want to do is select stuff from TABLE1 because they don't have an
entry in TABLE2 i.e. 6-10

Can this be done?

Thanks in advance

Eurisko

[Wouter]

Yes,

your tables both needs to be of the InnoDB type (not MYISAM !),
and you need to use:

ALTER TABLE `table2`ADD CONSTRAINT `table2_ibfk1` FOREIGN KEY (`td2_id`)
REFERENCES `table1` (`td1_id`) ON UPDATE CASCADE;
Or someting alike.
Look for documentation about FOREIGN KEY
(in google search for: foreign key mysql )

Ciao,
Wouter


"Elpetha Brooke" <ns*********@ntlworld.com> wrote in message
news:Bw**************@newsfe6-win.ntli.net...
: Hi
:
: I have two tables
:
: TABLE1
: t1_id int not null
: t1_other stuff blah blah
:
: TABLE2
: t2_id int not null
: t2_t1_id int not null
: t2_other stuff blah blah
:
: the link between the tables is t1_id --> t2_t1_id
:
: There is a list of items with ID 1-10 in TABLE1
: in TABLE2 there is a list of items with the t2_t1_id 1-5
:
: What I want to do is select stuff from TABLE1 because they don't have an
: entry in TABLE2 i.e. 6-10
:
: Can this be done?
:
: Thanks in advance
:
: Eurisko
:
:

[Elpetha Brooke]

Thanks Wouter, but what sql statement would acheive my goal

Thanks again

Eurisko
"Wouter" <no******************@djwice.com> wrote in message
news:cm**********@news.tudelft.nl...

Yes,

your tables both needs to be of the InnoDB type (not MYISAM !),
and you need to use:

ALTER TABLE `table2`ADD CONSTRAINT `table2_ibfk1` FOREIGN KEY (`td2_id`)
REFERENCES `table1` (`td1_id`) ON UPDATE CASCADE;
Or someting alike.
Look for documentation about FOREIGN KEY
(in google search for: foreign key mysql )

Ciao,
Wouter


"Elpetha Brooke" <ns*********@ntlworld.com> wrote in message
news:Bw**************@newsfe6-win.ntli.net...
: Hi
:
: I have two tables
:
: TABLE1
: t1_id int not null
: t1_other stuff blah blah
:
: TABLE2
: t2_id int not null
: t2_t1_id int not null
: t2_other stuff blah blah
:
: the link between the tables is t1_id --> t2_t1_id
:
: There is a list of items with ID 1-10 in TABLE1
: in TABLE2 there is a list of items with the t2_t1_id 1-5
:
: What I want to do is select stuff from TABLE1 because they don't have an
: entry in TABLE2 i.e. 6-10
:
: Can this be done?
:
: Thanks in advance
:
: Eurisko
:
:

[Bill Karwin]

Elpetha Brooke wrote:

What I want to do is select stuff from TABLE1 because they don't have an
entry in TABLE2 i.e. 6-10

SELECT TABLE1.*
FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.t1_id = TABLE2.t2_t1_id
WHERE TABLE2.t2_t1_id IS NULL

Regards,
Bill K.

[Eurisko]

Thanks Bill That did the trick ;)

Eurisko
"Bill Karwin" <bi**@karwin.com> wrote in message
news:cm*********@enews1.newsguy.com...

Elpetha Brooke wrote:

What I want to do is select stuff from TABLE1 because they don't have an
entry in TABLE2 i.e. 6-10

SELECT TABLE1.*
FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.t1_id = TABLE2.t2_t1_id
WHERE TABLE2.t2_t1_id IS NULL

Regards,
Bill K.

[Phillip T. Murphy]

"Wouter" <no******************@djwice.com> wrote in message
news:cm**********@news.tudelft.nl...

Yes,

your tables both needs to be of the InnoDB type (not MYISAM !),
and you need to use:

ALTER TABLE `table2`ADD CONSTRAINT `table2_ibfk1` FOREIGN KEY (`td2_id`)
REFERENCES `table1` (`td1_id`) ON UPDATE CASCADE;
Or someting alike.
Look for documentation about FOREIGN KEY
(in google search for: foreign key mysql )

Ciao,
Wouter

Why would he need to change is table type to InnoDB? Why not just use an
outer join?

[Wouter]

Yes, I was to quick in my responds.

The join that Bill did suggest is the way to do it.

I only read;

I want table1_id -> table2_id

and so wrote the alter table responds, way to quick ;)

Wouter
"Phillip T. Murphy" <pS**********@bellsouth.net> wrote in message
news:AV****************@bignews5.bellsouth.net...
:
: "Wouter" <no******************@djwice.com> wrote in message
: news:cm**********@news.tudelft.nl...
: > Yes,
: >
: > your tables both needs to be of the InnoDB type (not MYISAM !),
: > and you need to use:
: >
: > ALTER TABLE `table2`ADD CONSTRAINT `table2_ibfk1` FOREIGN KEY (`td2_id`)
: > REFERENCES `table1` (`td1_id`) ON UPDATE CASCADE;
: >
: >
: > Or someting alike.
: > Look for documentation about FOREIGN KEY
: > (in google search for: foreign key mysql )
: >
: > Ciao,
: > Wouter
: >
: >
: Why would he need to change is table type to InnoDB? Why not just use an
: outer join?
:
: