[RESOLVED] Querying two tables

meigwil

Hi,

Thanks for all the replies I've been getting to my questions, here are some more 🙂

I have two tables, one of which contains data for competition results (comp_id, 1st, 2nd, 3rd etc) the other contains requests (req_id, comp_id, reply_sent=TRUE/FALSE) for the results. Both tables are updated often.

Both contain the comp_id field, and I want to search the first table for requests that have not received replies (WHERE reply_sent=FALSE) and then compare the results with the result table. If there are any results that have matching comp_id fields, I want to get the data to be sent to the requestee (?!).

I am unsure how to go about this. I have tried a query seraching both tables at the same time (which didn't seem to work) and now I'm searching the request table (SELECT comp_id FROM request WHERE reply_sent = '0') which gives me a set of comp_id values, but I don't know how to query the other table with this data (loop or one long query string?).

Any ideas?

Thanks again,

Mei

TF_TheMoon

When you say false do you mean NULL, 0 or the text FALSE?

I'll assume you mean the field is NULL

This will get you all fields from the competitions table and the requests table where there is a record in the request table that has a reply_status of NULL (unset) but where a record exists in the requests table.

SELECT
*
FROM
competitions AS c
INNER JOIN requests AS r ON r.comp_id = c.comp_id AND r.reply_sent IS NULL

Example Data
Competitions table

comp_id | Col1 | Col 2|

1 | 'Stuff' | 23
2 | 'New' | 3
3 | 'One' | 2

requests

request_id | comp_id |reply_status | request | reply

1 | 3 | NULL | 'Do this' | NULL
2 | 1 | 'Done' | 'Help Me' | 'No'

Would give

comp_id | col1 | col2 | request_id | comp_id | request_status | request | reply

3 | 'One'| 2 | 1 | 3 | NULL | 'Do This'| NULL

meigwil

That worked great!

My previous attempt was similar (SELECT * FROM FROM result, request WHERE result.comp_id = request.comp_id AND request.reply_sent = 0) so I was on the right track. I'll have to brush up on my SQL...

Thanks for your help.

Mei

TF_TheMoon

Not a problem, SQL is probably my strongest skill

One thing though, If you have two requests for the one competition, with one entry beign false and the other true, you'll get a result returned. I forgot to mention it as it depends on whether that is likely with your setup.

If it is possible the query will need re-writing and depending on which implementation of SQL you're using depends on the query you can use.

meigwil

One thing though, If you have two requests for the one competition, with one entry beign false and the other true, you'll get a result returned. I forgot to mention it as it depends on whether that is likely with your setup.

As long as I'll get just one result (for each true record) it will be OK. I'm expecting/hoping this to happen.

Is this the case with this query. From where I'm standing I think this is the case.

Mei