Problem with LEFT JOIN

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 1694 Views - Last Post: 13 April 2018 - 09:34 AM Rate Topic: -----

#16 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14925
  • View blog
  • Posts: 59,592
  • Joined: 12-June 08

Re: Problem with LEFT JOIN

Posted 01 April 2018 - 02:27 PM

Why would the second table have multiple rows? Would it make sense to have many 'is active' to one user?
Was This Post Helpful? 1
  • +
  • -

#17 Ornstein   User is offline

  • New D.I.C Head

Reputation: 11
  • View blog
  • Posts: 20
  • Joined: 13-May 15

Re: Problem with LEFT JOIN

Posted 01 April 2018 - 06:46 PM

View Postmodi123_1, on 01 April 2018 - 02:27 PM, said:

Why would the second table have multiple rows? Would it make sense to have many 'is active' to one user?


I'm assuming (hoping?) it's not the user that is active, but something related to them. Something of which there can be more than one.
Was This Post Helpful? 1
  • +
  • -

#18 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: Problem with LEFT JOIN

Posted 02 April 2018 - 09:11 AM

Something else related to that user :D
Was This Post Helpful? 0
  • +
  • -

#19 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2791
  • View blog
  • Posts: 11,005
  • Joined: 03-December 12

Re: Problem with LEFT JOIN

Posted 02 April 2018 - 09:45 AM

Here is something to visualize data and JOINS.

My link
Was This Post Helpful? 1
  • +
  • -

#20 Foobarer   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 269
  • Joined: 28-March 17

Re: Problem with LEFT JOIN

Posted 12 April 2018 - 11:13 PM

So I've found a workaround (for now) without using JOINS, hopefully I will make joins work, but this does exactly what I wanted:


SELECT
    A.*
FROM
    A
WHERE
    A.id in (
        SELECT B.id FROM B WHERE B.id = :id )




Was This Post Helpful? 0
  • +
  • -

#21 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2791
  • View blog
  • Posts: 11,005
  • Joined: 03-December 12

Re: Problem with LEFT JOIN

Posted 13 April 2018 - 07:29 AM

That works just fine,

This looks like the type of join query you want.
SELECT
    A.*
FROM
    A
INNER JOIN B
ON A.id = B.id
-- optional
WHERE
    B.id = :id

Was This Post Helpful? 1
  • +
  • -

#22 Ornstein   User is offline

  • New D.I.C Head

Reputation: 11
  • View blog
  • Posts: 20
  • Joined: 13-May 15

Re: Problem with LEFT JOIN

Posted 13 April 2018 - 08:50 AM

View Postastonecipher, on 13 April 2018 - 07:29 AM, said:

That works just fine,

This looks like the type of join query you want ...


I thought we established earlier in the thread that this sort of query will create duplicate results when there's more than one corresponding entry on table B?

Here's an example of the GROUP BY solution I suggested:

SELECT A.* FROM A LEFT JOIN B ON B.user_id = A.user_id WHERE B.active = 1 GROUP BY A.user_id

Was This Post Helpful? 1
  • +
  • -

#23 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2791
  • View blog
  • Posts: 11,005
  • Joined: 03-December 12

Re: Problem with LEFT JOIN

Posted 13 April 2018 - 09:07 AM

It won't return anymore than doing the sub-query will. In fact it will return the same.

This is the closest I can come to what you are trying to do, base on the previous convo:

http://sqlfiddle.com/#!9/2bd42b/1
Was This Post Helpful? 1
  • +
  • -

#24 Ornstein   User is offline

  • New D.I.C Head

Reputation: 11
  • View blog
  • Posts: 20
  • Joined: 13-May 15

Re: Problem with LEFT JOIN

Posted 13 April 2018 - 09:29 AM

View Postastonecipher, on 13 April 2018 - 09:07 AM, said:

It won't return anymore than doing the sub-query will. In fact it will return the same.


Are you sure? In his query with the subquery, the subquery may return more results but ultimately only the relevant number of results will be returned. In your query, the user will be duplicated for each corresponding entry in the second table.

Borrowing your users and messages example, what he's trying to achieve is akin to getting a list of all users who have messages - but each user should obviously only be returned once. Using joins like in your example, each user will be duplicated for each message they have.
Was This Post Helpful? 1
  • +
  • -

#25 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2791
  • View blog
  • Posts: 11,005
  • Joined: 03-December 12

Re: Problem with LEFT JOIN

Posted 13 April 2018 - 09:34 AM

You’re right. The sub will only return records for users that have any message.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2