Whether there's a record or not

[JoSno]

Hello all,

I've got a query I need to run and i don't know if I can do it one pure MySQL query without reverting to C code and slow lookups (these tables are BIG!).

The scenario is I have one main table with records in and another with date ranges for the records in the first table, obviously the dates table has a field acting as a foreign key to the main table.

So I want to select every record in the main table whose related date range includes today (easy enough to do) but I also want to include all the main records that don't have any date ranges specified.

I'm thinking the best way to go about it might be to copy everything into another main table (to eventually be the results table) and remove everything that has a date range that doesn't include today.

Any ideas on doing in it one query though?

Cheers!
Jo

 

[nigelcohen]

Try this(assuming, say, a sales table with dates and a stock table without)


select * from sales s, stock t
where s.StockRef = t.Ref
and (s.Date = '2003-05-20' or s.Date = '')

 

[JoSno]

tahnks for the quick reply but I'm afraid it doesn't work, primarliy becuase there would be no stock record to match in the first where clause. Maybe I didn't make it clear, I need to include all main records which have dates records that are in range, and also main records that have no related date records at all, so no foreign key pointing to them. i think I may need to use a left join or something and pick off the "in-range"s and nulls.

Jo

 

[QatQat]

This one only returns all records in table1 that have got a related record in table2


Select t1.field1, t1.field2, t2.field1 INNER JOIN t2
on t1.fieldx = t2.fieldx

This one instead returns all record in table1 even if they don't have a related record in table2.

Select t1.field1, t1.field2, t2.field1 LEFT OUTER JOIN t2
on t1.fieldx = t2.fieldx



Bye

Qatqat

I have been happy throughout my life in thinking that samba was I kind of dance; now I live with Linux and all I do is working.

 

[JoSno]

Thanks a lot for your help and I imagine that would have worked fine but I have chosen the following solution.

I couls pslit the categories down into records with valid dates, records with invalid dates and records with no dates. I wanted the first and the last of these so I copied everything over and REMOVED the records wit hinvalid dates thus leavig me a perfect set of data. Venn would have been proud of my logic

Jo