asked on

SQL syntax query

Hi there,

I'm using a query to pull information from three different tables. Its fairly simple to do this other than the fact that from one table I want to pull data from the recordwith the minimum ID value.

I basically want to combine the two queries shown in the code. The field "t.surname" in the first query should really be filled with the result of the second query

SELECT l.vch_code, t.surname,  q.* FROM suppfail.quote AS q INNER JOIN suppfail.policy AS p ON q.id = p.quote_id INNER JOIN ocs.logins AS l on q.brk_id = l.int_id
INNER JOIN suppfail.traveller AS t on q.id = t.quote_id
WHERE q.status = 'live' AND q.date_issue BETWEEN '2009-05-01' AND '2009-05-15'
 
SELECT surname FROM suppfail.traveller t WHERE quote_id = x AND id IN (SELECT MIN(ID) FROM suppfail.traveller t WHERE quote_id = x);

Open in new window

Kevin Cross

This is one method:

SELECT l.vch_code, t.surname,  q.* 
FROM suppfail.quote AS q 
INNER JOIN suppfail.policy AS p ON q.id = p.quote_id 
INNER JOIN ocs.logins AS l on q.brk_id = l.int_id
INNER JOIN suppfail.traveller AS t on q.id = t.quote_id
WHERE q.status = 'live' 
AND q.date_issue BETWEEN '2009-05-01' AND '2009-05-15'
AND t.id = (SELECT MIN(ID) FROM suppfail.traveller t2 WHERE t2.quote_id = t.quote_id);

Open in new window

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

g-spot

ASKER

Thanks mwvisa1.

Thats great. Works perfectly. Cheers.

Kevin Cross

Glad that helped!

Cheers,
Kevin

g-spot

ASKER

Not only did it help but for the first time I actually understood how it worked and can make use of the code again.