selecting from multiple tables where a condition is true

Question

Trying to run some SQL with no luck, trying to select data from two tables where a condition is true.

The tables are driver_details and locations. Both tables have the column user_id, and I want to get data from both based on the matched user_id from the between part. (that select statement works and returns ID's);

SELECT driver_details.firstName,
                    locations.lat,
                    locations.lng
                    FROM driver_details
                    INNER JOIN locations
                    WHERE user_id = 
                    (SELECT user_id FROM locations WHERE 
                    (lat BETWEEN 0 AND 5) AND 
                    (lng BETWEEN 0 AND 5))

I am getting the error: Fatal error: Call to a member function bind_param() on a non-object in C:\xampp\htdocs\vector\www\scripts\getDriversInRange.php on line 33

Are you sure that your subquery (the second `SELECT` statement) will always return 1 result and ONLY 1 result? If not, you should probably change `WHERE user_id = ` to `WHERE user_id IN` — Patrick Q, Mar 04 '14 at 17:40
No, by using `=` instead of `IN`, you're limiting yourself to just one result. See the answer that I just posted. I think it might be what you're looking for. — Patrick Q, Mar 04 '14 at 17:47

score 2 · Answer 1 · answered Mar 04 '14 at 17:36

2

You need to RELATE the 2 tables being JOINed using SOMETHING that's common in both tables, which is used to connect them ... you mention user_id

...
FROM driver_details
INNER JOIN locations
ON driver_details.user_id = locations.user_id
WHERE ...

answered Mar 04 '14 at 17:36

BWS

3,786
18
25

Technically you don't have to. It functions as a `CROSS JOIN`. That said, it's confusing and should be avoided. http://stackoverflow.com/a/16471286/2136840 – ChicagoRedSox Mar 04 '14 at 17:41

score 1 · Accepted Answer · answered Mar 04 '14 at 17:47

1

SELECT d.firstName,
    l.lat,
    l.lng
FROM driver_details d
    INNER JOIN locations l ON d.user_id = l.user_id
WHERE l.lat BETWEEN 0 AND 5
    AND l.lng BETWEEN 0 AND 5

This is basically a more complete example of what BWS posted.

answered Mar 04 '14 at 17:47

Patrick Q

6,373
2
25
34

selecting from multiple tables where a condition is true

2 Answers2