Combine mysql query

Databases
1

Sorry Guys in need of help here as i just can not get my head around double querys.

the ideas of the below is the find the reference and then check the reference on another table to see if the engineerid is null or 0.

SELECT `LairdRef` 
FROM  `query_dept` 
ORDER BY  `query_dept`.`date_time_received` DESC 
LIMIT 0 , 30

2nd table would be

SELECT * 
FROM  `tblreportdata` 
WHERE  `EngineerID` =  '0'

ideally i only want to list the reference that has the engineer as 0 from the results of the first table.

any one able to help me?

2

Do these tables have any field in common?

3

LairdRef
is the only common field.

4

First, ordering by a field that you can’t see doesn’t make any sense to me. I’d personally remove the ORDER BY ‘query_dept’.‘date_time_received’

Since LairdRef is the common field, you should try something like…

SELECT query_dept.LairdRef

FROM query_dept, tblreportdata

WHERE query_dept.LairdRef=tblreportdata.LairdRef AND tblreportdata.EngineerID=0

The first condition of the WHERE clause is the old way of creating an INNER JOIN.

You can also create a proper INNER JOIN which would be the same

SELECT query_dept.LairdRef

FROM query_dept INNER JOIN tblreportdata ON query_dept.LairdRef=tblreportdata.LairdRef

WHERE tblreportdata.EngineerID=0

Both are the same

edit: correcting myself because, yes, as usual, @r937 is right

5

molona, in your first query, you forgot a table

and in both queries, the column in your SELECT clause is ambiguous

just tryna help :slight_smile:

1 Like
6

the reason for the order by is due to the last references are normally the ones that are causing me the grief so i thought id scan the last 30.

7

thank you for you’re help both of you!

I will test it shortly when a user decides to break the system!

1 Like
8

IT WORKED AND I LOVE YOU GUYS SAVES ME A ROYAL PAIN!

1 Like
9

i love how the enthusiasm goes up exponentially when application developers finally understand how sql joins work

10

next time i need to join one i will look back at this and really try my best to do it myself!

i think i’ve got the jist of this :smile:
thank you all!

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.