why is this select query not working?

[dysfunctionGazz]

Im using PHPMYADMIN...

This is the error im getting....

Im trying to "join" my tables so that i can get the names of bands, playing on a set date. I have 3 tables, eventdate, bands, bandshows.

Anyone see whats up with this?

===

Database suiciden_contacts - Table bands running on localhost
Error

SQL-query :

SELECT bandname, description
FROM bands
WHERE eventdate.bands = eventinfo.eventdate
LIMIT 0 , 30

MySQL said:


Unknown table 'eventdate' in where clause
Back

[sad69]

It probably should have thrown some more errors.. your query doesn't make any sense:

Im trying to "join" my tables so that i can get the names of bands, playing on a set date. I have 3 tables, eventdate, bands, bandshows.


SELECT bandname, description
FROM bands
WHERE eventdate.bands = eventinfo.eventdate


There's a reason for all the red.

You're trying to join some tables? The FROM clause of your SELECT statement has only ONE table: bands. What are you trying to JOIN bands with?
eventdate is a table, and you're imposing a restriction on which rows come from it, however you haven't specified it in your FROM clause..
What is eventinfo? It's not one of the 3 bands you've got listed..


I'm guessing you've got somethings backwards, so this is the query I think you're trying to execute:

SELECT bandname, description
FROM bands, eventdate
WHERE bands.someColumn=eventdate.someOtherColumn
LIMIT 0 , 30


So just fill in the column names that you're doing the join on between the two tables. Or maybe you're not trying to do a join...? Maybe you should try to explain what sort of resultset you're trying to acquire. Also, list the column names in each of your tables (the column type wouldn't hurt either).

Hope that helps,
Sadiq.

[dysfunctionGazz]

OK here is the 3 tables i have created:

bands
-----
ID, bandname, location, genre, description, notes, phone1, phone2, website, email

bandshows
----------
eventdate
bandname
eventtype
slot

eventinfo
---------
eventdate
under18s
admission
doors


So, for example, i want a query that displays the following

DATE: 18.08.04
EVENT: Quid Rock
LINEUP: Dry Rise - Rock from Brighton
Uniting the elements - Germanys finest export
Chaz n Dave - The best london band ever
ADMISSION: £1

That make sense? I dont understand joins proper. All i know is that the data i want is in the tables in the best way i can think of, and if i can work out the query, then i just gotta put it into PHP and i have magic listings that pull all the info out of my database! :D

[Kiwi]

I am assuming that eventdate is the primary key for the eventinfo table. Your query then needs to link together everything relating to the event through that date. It wouldn't be a bad idea to replace the eventdate with a unique ID, rather than the date (actually, I'd also pop genre, location, and phone numbers into separate tables).

You need to change your bandshow table so that it contains two primary keys (from bands and eventinfo). This will mean replacing bandshows.bandname with bandshows.bandid.

Making just that one change, to list the event details:

SELECT *
FROM
eventinfo AS a
WHERE
a.eventdate = <thedate>;


To get a multi-row result set of the bands at a given event:

SELECT
a.slot,
b.bandname,
b.description
FROM
bandshows AS a,
bands AS b
WHERE
a.bandid = b.ID AND
a.eventdate = <thedate>
ORDER BY 1
;

[ConfusedOfLife]

Or maybe something like this:


SELECT a.slot, b.bandname, b.description
FROM bandshows AS a
LEFT JOIN bands AS b ON a.bandid = b.id AND a.eventdate = <some date>


If you wana have an ORDER BY or GROUP BY clause you can add it in the end of the script, but I couldn't understand what that "ORDER BY 1" is standing for?!

[Kiwi]

A left join is implicit in the query I posted - making it explicit isn't really adding anything (except to seperate the two table definitions, which I tend to find more likely to hide errors). ORDER BY 1 means that it sorts on the first column of your results.