Web Design Forums

Shiro · 28-01-2008, 11:09

I have two tables in a database:

Table 1 - files:

column 1 - ID
column 2 - deleted

Table 2 - seminar_files

column 1- seminar_id
column 2 - file_id

ID from table 1 and file_id from table 2 are the column that bridges these together. So for example, I may have a file with an ID of "haku" in table 1, and that same 'haku' may appear in table 2. It may appear zero times, one time or many times, but in such a case will have a different seminar_id for each time it appears.

I want to select all the IDs from from table 1 that aren't in table 2 when seminar_id is equal to 18 (for example). On top of this, I only want to select the files from table one where deleted is set to zero.

Anybody got an idea on how to put together this mysql query? I've tried a bunch of different ways, and I either end up with ALL the files from table one, or none!

datahound · 28-01-2008, 11:20

Are you looking at left join, right join queries. It is one of those. Last time I managed to bungle my way through by trial and error. I think that is what you need to research if that helps.

datahound · 28-01-2008, 11:21

MySQL Tutorial - Left Join

Sort of thing..?

Shiro · 28-01-2008, 11:40

I was trying out the left join, but I just wasn't getting it to work. I went with the trial and error method for about an hour and a half before posting that! I'll try again tomorrow if no one can point me in the right way. I know the right answer is there, I just gotta figure out what it is.

Thanks though!

d3mcfadden · 28-01-2008, 17:18

SELECT f.ID FROM files f WHERE f.ID NOT IN
( SELECT file_id FROM seminar_files sf WHERE sf.seminar_id = 18 )
AND f.deleted = 0

Shiro · 28-01-2008, 18:12

You sir are a savior! What I was trying was not anything even remotely close to that. (I didn't even know you could do that!). I'll try it out when I get to work.

Shiro · 28-01-2008, 21:10

I just tried it out and it worked perfectly. I learned a little something from that too! Thanks!

d3mcfadden · 28-01-2008, 21:27

no problem man.. its amazing how many people don't know about the IN / NOT IN clause...

Shiro · 28-01-2008, 21:50

I didn't, but I do now. I will definitely use that one again in the future.

28-01-2008, 11:09	#1 (permalink)
Shiro shiro Join Date: Aug 2007 Location: Yokohama, Japan Posts: 2,545	Mysql - cross table queries I have two tables in a database: Table 1 - files: column 1 - ID column 2 - deleted Table 2 - seminar_files column 1- seminar_id column 2 - file_id ID from table 1 and file_id from table 2 are the column that bridges these together. So for example, I may have a file with an ID of "haku" in table 1, and that same 'haku' may appear in table 2. It may appear zero times, one time or many times, but in such a case will have a different seminar_id for each time it appears. I want to select all the IDs from from table 1 that aren't in table 2 when seminar_id is equal to 18 (for example). On top of this, I only want to select the files from table one where deleted is set to zero. Anybody got an idea on how to put together this mysql query? I've tried a bunch of different ways, and I either end up with ALL the files from table one, or none!

28-01-2008, 11:20	#2 (permalink)
datahound Spare Parts Join Date: Jan 2005 Location: Bracknell Forest Posts: 4,931	Are you looking at left join, right join queries. It is one of those. Last time I managed to bungle my way through by trial and error. I think that is what you need to research if that helps. __________________ poochmedia \| Computer Spare Parts \| Computer & Telephone Disposals

28-01-2008, 11:21	#3 (permalink)
datahound Spare Parts Join Date: Jan 2005 Location: Bracknell Forest Posts: 4,931	MySQL Tutorial - Left Join Sort of thing..? __________________ poochmedia \| Computer Spare Parts \| Computer & Telephone Disposals

28-01-2008, 17:18	#5 (permalink)
d3mcfadden Senior Member Join Date: Apr 2005 Location: - Posts: 694	SELECT f.ID FROM files f WHERE f.ID NOT IN ( SELECT file_id FROM seminar_files sf WHERE sf.seminar_id = 18 ) AND f.deleted = 0 Last edited by d3mcfadden : 28-01-2008 at 21:25.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

28-01-2008, 11:40	#4 (permalink)
Shiro shiro Join Date: Aug 2007 Location: Yokohama, Japan Posts: 2,545	I was trying out the left join, but I just wasn't getting it to work. I went with the trial and error method for about an hour and a half before posting that! I'll try again tomorrow if no one can point me in the right way. I know the right answer is there, I just gotta figure out what it is. Thanks though!

28-01-2008, 18:12	#6 (permalink)
Shiro shiro Join Date: Aug 2007 Location: Yokohama, Japan Posts: 2,545	You sir are a savior! What I was trying was not anything even remotely close to that. (I didn't even know you could do that!). I'll try it out when I get to work.

28-01-2008, 21:10	#7 (permalink)
Shiro shiro Join Date: Aug 2007 Location: Yokohama, Japan Posts: 2,545	I just tried it out and it worked perfectly. I learned a little something from that too! Thanks!

28-01-2008, 21:27	#8 (permalink)
d3mcfadden Senior Member Join Date: Apr 2005 Location: - Posts: 694	no problem man.. its amazing how many people don't know about the IN / NOT IN clause...

28-01-2008, 21:50	#9 (permalink)
Shiro shiro Join Date: Aug 2007 Location: Yokohama, Japan Posts: 2,545	I didn't, but I do now. I will definitely use that one again in the future.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)