advanced query problem

[Brian W]

I have 5 tables that I need to interconnect: messages, events, teams, leagues, and members. A message can either be for an "event" or a "team". The query is supposed to get the last 5 messages posted for a specific league.

Through some sub-queries and a JOIN I've got all of the information I need but I cannot use one of my fields (league) within my WHERE clause presumably because the field is populated by one of my sub-queries. I need either a solution or some advice on a different structure for my query. Here is the query:

SELECT m.*,u.username,
(SELECT CONCAT(l.leagueAcro,'>',e.title) as title FROM events e LEFT JOIN leagues l on l.id = e.league WHERE e.board = m.board
UNION
SELECT CONCAT(l.leagueAcro,'>',t.team) as title FROM teams t LEFT JOIN leagues l on l.id = t.league WHERE t.board = m.board) as title,
(SELECT e.league FROM events e LEFT JOIN leagues l on l.id = e.league WHERE e.board = m.board
UNION
SELECT t.league FROM teams t LEFT JOIN leagues l on l.id = t.league WHERE t.board = m.board) as league
FROM messages m LEFT JOIN members u ON u.id = m.uid
WHERE league = 2 LIMIT 5

the field `title` ends up looking something like "Football>team's name" or "Soccer>teams vs other team".

The last line is what is screwing me up, the column `league` is populated through the second sub-query which leads me to believe mySQL processes the WHERE clause before it runs a sub-query for every singe db entry which makes since for performance reasons.

Unknown column 'league' in 'where clause'

 

Thanks in advance for any help...

[ngreenwood6]

can you show your database structure? you probably dont need the extra queries in there and can just do it all in one. Also tell what you are trying to get

[Brian W]

I'm not sure what you mean by tell you what I'm trying to get... I thought I explained it in the my first message. 

Table 'messages':

|| id || message || uid || time || board ||

 

Table 'events':

|| id || title || description || league ||

 

Table 'teams':

|| id || team || league || state || board ||

 

Table 'leagues':

|| id || league || leagueAcro ||

 

Table 'members':

|| id || username || password || email || posts || admin || moderator ||

[ngreenwood6]

is there anything in the messages table that links it to the league?

[Brian W]

no, not directly... I really don't want repeat information (have the league stated in both the message and in the parent).

[ngreenwood6]

you wouldnt put the league information in that table you would put the league id and link it using that. let me think about this and see what I can come up with

[ngreenwood6]

try this:

 

$select = 'messages.* members.username';
$from = 'messages LEFT JOIN members ON messages.uid = members.id LEFT JOIN teams ON messages.board = teams.board LEFT JOIN leagues ON teams.league = leagues.league';
$where = 'leagues.id = 2';
$orderby = 'messages.time DESC';
$limit = '5';

$query = 'SELECT $select FROM $from WHERE $where ORDER BY $orderby LIMIT $limit';

[Brian W]

I mean, the league field in the `event` and `team` table are used to reference the `leagues`.`id` field...

 

@ngreenwood6, I cleaned the code up a little (ie used " rather than ' around the $query string so it wouldn't use the variables literally and also added a comma between the two columns in the SELECT statement...) and the query returned nothing.

 

[ngreenwood6]

i forgot a , between messages.* and members.username did you add that:

 

$select = 'messages.*, members.username';

 

Also is it actually performing the query or is there an error?

[Brian W]

I added it... I do understand mySQL decently 

[ngreenwood6]

The only reason I can think of that this isnt running is because one of the values arent matching up. without actually being able to see the data and fields its kinda hard to troubleshoot. I would just look at the query and make sure that the tables that are supposed to join up are going to by looking at the values. Another thing that I can suggest is a really nice program that I use. Its called navicat. You must have the full version to do this but there is a query builder in there that will allow you to drag your tables there, select the fields, joins, where and much more for your query. Then you can actually perform the query to see what results you get. Might be worth a look for you. If you would like to upload a sql dump not necessarily with all the data but just something in each of the fields I would be more than happy to import it and see if I can build the query that you need.

[Brian W]

Well, essentially I think my problem is that I have it set up that messages can be a child of either 'teams' or 'events', so I have to run a sub query to get the data per message. If all the messaged I was trying to pull up had the same parent (such as when I am pulling the messages in the context of the actual board) it would not be a problem because I would use a simple JOIN to do so. A conditional query would be awesome, something like "if an event is found with the board column value X ? CONCAT(league.leagueAcro,'>',event.title) as title : CONCAT(league.leagueAcro,'>',team.name) as title".

Is that possible with MySQL?

The only two solutions meanwhile I can think of are dirty fixes (IMO).

#1, to have the league ID referenced in the messages table so that I didn't need to pull that from another table... but I need to reference it in the 'teams' and 'events' table already so I don't want duplicate information all over the place since I already need to JOIN the 'teams' OR 'events' table to the message.

#2, get the messages in one query and then loop queries for each message to get the additional info.

[Brian W]

Here is a dump of what I have so far...

 

[attachment deleted by admin]

[ngreenwood6]

ok with the export that you gave me I ran this:

 

SELECT messages.id,messages.message,messages.uid,messages.time,messages.board,messages.flagged,members.username FROM messages Left Join members ON messages.uid = members.id Left Join boards ON messages.board = boards.id Left Join events ON boards.id = events.board Left Join leagues ON events.league = leagues.id WHERE leagues.id =  1

 

It gave me 1 result, but that was only because the export that you sent me only had one event in it. I changed the id to 1 because there was no event id with 2 with the export that you gave me. Hopefully that will work for you. however, it is always better to link other tables to tables that you need to use info from it so you have a link to it. for instance you should probably add a lid field that holds the league id in the messages table. There is nothing wrong with doing that it is there so that you can link it using that. If you dont use links like that sometimes the info that you get will be off or you will have to go about doing it in a long way.