Problem with LEFT JOIN

  • (2 Pages)
  • +
  • 1
  • 2

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

#1 Foobarer   User is offline

  • D.I.C Regular

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

Problem with LEFT JOIN

Posted 30 March 2018 - 06:28 AM

I have table with some details a user input, and a table storing the details with an active column (just for test, I know I can put it in the same table).

I want to get all the details the user ever input, but only where it's active (Because the user can delete the details rows by setting it to 0 in the active column)

So I do that:

        $query = "SELECT details.*
                         FROM details
                         LEFT JOIN pairs ON details.user_id = pairs.user_id
                         WHERE pairs.user_id = :user_id AND pairs.is_active = :active"; 
        


But I get a random number of rows

*Edit: I just realized I get times 7 the number of real rows, so if rowCount() is supposed to return 2, it returns 14!

This post has been edited by Foobarer: 30 March 2018 - 06:32 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Problem with LEFT JOIN

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14175
  • View blog
  • Posts: 56,789
  • Joined: 12-June 08

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 06:37 AM

Give a minimal example of the data you are using and the rows returned. I am pretty certain 'random rows' isn't happening.

cre ate table #tbl1 (lval int, sval varchar(10))
cre ate table #tbl2 (lval int, lval_tbl1 int, sval varchar(10))

ins ert into #tbl1(lval, sval) values (1, 'aa')
ins ert into #tbl1(lval, sval) values (2, 'bb')
ins ert into #tbl1(lval, sval) values (3, 'cc')

ins ert into #tbl2(lval,lval_tbl1, sval) values (1,1, 'ert')
ins ert into #tbl2(lval,lval_tbl1, sval) values (2,2, 'yu')
ins ert into #tbl2(lval,lval_tbl1, sval) values (3,1, 'ert4')

select *
from #tbl1
select *
from #tbl2

select  *
from #tbl1 a
left join #tbl2 b on a.lval = b.lval_tbl1
--where a.lval = 1

dr op table #tbl2 
dr op table #tbl1 



lval        sval
----------- ----------
1           aa
2           bb
3           cc

 

lval        lval_tbl1   sval
----------- ----------- ----------
1           1           ert
2           2           yu
3           1           ert4
 

lval        sval       lval        lval_tbl1   sval
----------- ---------- ----------- ----------- ----------
1           aa         1           1           ert
1           aa         3           1           ert4
2           bb         2           2           yu
3           cc         NULL        NULL        NULL

Was This Post Helpful? 1
  • +
  • -

#3 Foobarer   User is offline

  • D.I.C Regular

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

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 06:51 AM

Oh so maybe LEFT JOIN Is not what I'm looking for?
Well there is no real data yet, It's just testing.

What I need is: To get all the columns from table1, where user_id from table2 is matching the user_id from table1, but also where the column "active" is 1 in table2

Is it another statement I'm looking for?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14175
  • View blog
  • Posts: 56,789
  • Joined: 12-June 08

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 06:54 AM

The important part of a left join is if the 'right' table has no matching values they return null.. as the join should return something for all the rows in the 'left' table.

Sounds like you want a regular old join.
Was This Post Helpful? 0
  • +
  • -

#5 Foobarer   User is offline

  • D.I.C Regular

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

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 07:06 AM

I tried JOIN right now, but exactly the same results, and from some reason it's seven times the number of real rows

$query = "SELECT details.*
                 FROM details
                 JOIN pairs ON details.user_id = pairs.user_id
                 WHERE pairs.user_id = :user_id AND pairs.is_active = :active"; 


Maybe bad syntax with the WHERE??

This post has been edited by Foobarer: 30 March 2018 - 07:07 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14175
  • View blog
  • Posts: 56,789
  • Joined: 12-June 08

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 07:14 AM

I would need to see what the table structure for both look like, some example data, and some example output.
Was This Post Helpful? 1
  • +
  • -

#7 Foobarer   User is offline

  • D.I.C Regular

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

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 08:20 AM

Ok, the real table had some weird column names because I started with testings only so I changed it to generic column names:

This is TABLE 1:

user_id | message1 | message2 | message3


TABLE 2:

user_id_tb2 | is_active


(Yes, the column name of the user_id from TABLE 1 and TABLE2 are different, but I don't think that should matter if you JOIN with the correct syntax, right? I mean the column doesn't have to be called "user_id" exactly on table 2.


I want to get all the columns from table 1 where user_id is user_id_tb2 and is active '1' on TABLE 2

This post has been edited by Foobarer: 30 March 2018 - 08:21 AM

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14175
  • View blog
  • Posts: 56,789
  • Joined: 12-June 08

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 08:28 AM

If there's a one to one match from table one to table 2.. and table 2 only holds 'active'.. why is that not on the row of data in table 1?
Was This Post Helpful? 1
  • +
  • -

#9 Foobarer   User is offline

  • D.I.C Regular

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

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 02:12 PM

View PostFoobarer, on 30 March 2018 - 06:28 AM, said:

(just for test, I know I can put it in the same table)

I warned ye :detective:
But this table will not be one on one, it is currently, but I am planning to add more columns, I just want to test first whether it's possible to select it as I want, although so far no success
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is online

  • Bouncy!
  • member icon

Reputation: 6563
  • View blog
  • Posts: 26,615
  • Joined: 12-December 12

Re: Problem with LEFT JOIN

Posted 30 March 2018 - 05:50 PM

It doesn't help that you change your field and table names...

$query = "SELECT details.*
                 FROM details
                 JOIN pairs ON details.user_id = pairs.user_id
                 WHERE pairs.user_id = :user_id AND pairs.is_active = :active";

This is essentially correct, but is_active is in the details table (or table 2), not the pairs table.

If this still isn't working then provide clearer details and sample data, including your exact code and details of any error messages.



Also, having message1, message2, etc., in a table is a clear indication of the breaking of 1NF.
Was This Post Helpful? 1
  • +
  • -

#11 Foobarer   User is offline

  • D.I.C Regular

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

Re: Problem with LEFT JOIN

Posted 31 March 2018 - 05:47 AM

It's not a real table, the real one won't have "message1, message2" so I will (hopefully) not violate 1NF

I ended up splitting it to 2 queries instead
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw   User is online

  • Bouncy!
  • member icon

Reputation: 6563
  • View blog
  • Posts: 26,615
  • Joined: 12-December 12

Re: Problem with LEFT JOIN

Posted 31 March 2018 - 11:28 AM

Why two queries? If you want information from two tables that share a common field then one query should suffice.
Was This Post Helpful? 1
  • +
  • -

#13 Foobarer   User is offline

  • D.I.C Regular

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

Re: Problem with LEFT JOIN

Posted 31 March 2018 - 01:49 PM

Because I tried all the joins and non worked :euro: and the 2 queries work, I will try to change it again in the future, but I just spent too much time on that right now
Was This Post Helpful? 0
  • +
  • -

#14 Ornstein   User is offline

  • New D.I.C Head

Reputation: 8
  • View blog
  • Posts: 17
  • Joined: 13-May 15

Re: Problem with LEFT JOIN

Posted 01 April 2018 - 04:45 AM

I think I'm just as confused as everyone else, but it sounds like what happened was the rows from table 1 were being duplicated because there were more rows returned by table 2.

In other words: If you have 2 users in table 1 and each user has 6 relevant rows in table 2, the result of the join will be 12 rows.

I have a suspicion that a simple GROUP BY on the user ID column could have solved all this. :P
Was This Post Helpful? 1
  • +
  • -

#15 Foobarer   User is offline

  • D.I.C Regular

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

Re: Problem with LEFT JOIN

Posted 01 April 2018 - 01:59 PM

Exactly what you said: it returned duplicates, for example when I had 2 matching users, it returns 12 as you said, I'm going to try the GROUP BY :) (Although everything is working with the 2 queries, I will just make a test query to see for the number of rows)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2