Table Join Problems with Nulls

PHPAnx

surfer

It's not a good idea to join two tables with a field that can be NULL. You must try to always join tables with keys. Either change venuesName to be a non NULL key (so it can't be NULL anymore) or add an extra field as a key to the venues table (eg add venues_id to table venues and change in table tours venuesName to venues_id). You can then join the two tables and get the expected result.
Hope it helps.

PHPAnx

mattr

Change the design to a more proper one.

dnszero

It sounds like you may need to normalize your db design. Check out this article http://www.phpbuilder.com/columns/barry20000731.php3

If that still doesn't get it, after reading, try posting a more detailed description of the design and logic and maybe a solution will be easier to see.

PHPAnx

Sxooter

So, let me ask you, can you have more than one group at a venue, as well as more than one venue per group?

If so, you need to use an intermediate table to join such data properly.

It works like this:

bands:

id | name

0 | Tatu
1 | Grand Funk Railroad
2 | Tom Lehrer

venues:

id | venue

0 | Madison Cube Gardens
1 | My Backyard
2 | Woodstock

join_table:

b_id | v_id

0 | 0
0 | 1
1 | 0
1 | 2
2 | 2

Then select like so:

select * from bands b, venues v, joiner j where b.id=j.bid and v.id = j.vid and b.id=1;

PHPAnx

Sxooter

The venue IDs need to be something fixed and immutable if you want to have coherent data for very long.

So, all the data entry people are doing is picking the venue from a drop down list, that only the chosen few can edit. I.e. 2 people can put new venues in, the other 20 put in the concerts as they come in.

If you store your venues as some kind of free form text entry, then you'll NEVER be able to match them up reliably. Ever.