LEFT JOIN help

Databases
1 

SELECT 
	* 
FROM 
	".DB_PREFIX."ticket t 
				
LEFT JOIN 
	".DB_PREFIX."ticket_c c 
ON 
	c.ticketID = t.ticketID 
				
WHERE t.requesterID = '".$id."' ";
ORDER BY 
	c.commentDate DESC

Hi,
I’m trying to use JOIN to get variables from 2 tables. Currently I have (as above)

It seems to return everything fine, but will only return the first ticketID. The main data is from ticket, with the ORDER BY relying on ticket_c

Does anyone know where I’m going wrong? I’m new to JOINs

Thanks,
Rhys

2

query looks fine (apart from the dreaded SELECT *, but)… are you sure there’s extra data that should be returned? (IE: the requesterID in question has multiple entries in the ticket table?)

3

The ON c.ticketID = t.ticketID should be ON (c.ticketID = t.ticketID) .

4

No need for that.

but will only return the first ticketID

I guess what you’re saying is: my PHP code only shows me the first ticketID.
Please post that code.

5

That’s not strictly necessary, since he has a simple conditional expression rather than a compound one.

6

no, it should not

eschew (the use of (unnecessary) parentheses)

7

Unnecessary, probably. More readable, you better believe it. But that is beside the point I guess.

The advice I give TacMaf is to make sure the data comes out exactly like you want it by running the SQL code in MySQL Query Browser or phpMyAdmin. If it does, then in PHP or whatever language you are using, fetch your data into an array and then print_r() it so you can see what it looks like. Hopefully it will be easy to figure it out from there.

8

agree (wholeheartedly) :slight_smile:

9

I believe this problem was solved here.