Please help stop repeating records with join

shure2

Hi,

I have two tables that I want to join:
-leadproductdetal
-leadproductquality

They both share leadid and leadprodnumber

SELECT * 
FROM leadproductdetail
JOIN leadproductquality ON leadproductdetail.leadid = leadproductquality.leadid
AND leadproductdetail.leadprodnumber = leadproductquality.leadprodnumber
WHERE leadproductdetail.leadid =1
AND leadproductquality.leadid =1
AND leadproductdetail.leadprodnumber =1
AND leadproductquality.leadprodnumber =1

The problem is that because I have more leadproductdetails than leadproductqualities for leadid and leadprodnumer 1 the leadproductqualities record is repeated, see below for example

leadproddetail leadprodvalue leadprodquality leadprodvalue

depth 22 build quality 9
width 25 build quality 9
height 30 build quality 9

I only need build quality to show once, but can't work out the right join so it works 🙁 please help

BIOSTALL

The reason you are getting the duplicating records is because you are doing a one-to-many join or a many-to-many join.

There are two things you could change in your query:

Not essential but you can change:

WHERE leadproductdetail.leadid =1
AND leadproductquality.leadid =1
AND leadproductdetail.leadprodnumber =1
AND leadproductquality.leadprodnumber =1

To:

WHERE leadproductdetail.leadid =1
AND leadproductdetail.leadprodnumber =1

As you are joining on these fields they will be the same in each table and therefore you only need to check against one table. I don't know if that makes sense at all. Hard to explain.

Secondly, have a look at GROUP BY as I think this is what you are after to resolve multiple rows coming back:

SELECT * 
FROM leadproductdetail
JOIN leadproductquality ON leadproductdetail.leadid = leadproductquality.leadid
AND leadproductdetail.leadprodnumber = leadproductquality.leadprodnumber
WHERE leadproductdetail.leadid =1
AND leadproductdetail.leadprodnumber =1
GROUP BY leadproductquality.leadid

I hope that helps. Without seeing more code or fully understanding your database schema it's hard to tell exactly but hopefully that's kind of on the right tracks.

Let me know how you get on.

PS. Sorry I didn't reply to your messages from the other week. For some reason I didn't get an email notifying me I had a new message :-S

Good luck!!

shure2

BIOSTALL;10949555 wrote:
The reason you are getting the duplicating records is because you are doing a one-to-many join or a many-to-many join.

There are two things you could change in your query:

Not essential but you can change:
WHERE leadproductdetail.leadid =1
AND leadproductquality.leadid =1
AND leadproductdetail.leadprodnumber =1
AND leadproductquality.leadprodnumber =1
To:
WHERE leadproductdetail.leadid =1
AND leadproductdetail.leadprodnumber =1
As you are joining on these fields they will be the same in each table and therefore you only need to check against one table. I don't know if that makes sense at all. Hard to explain.

Secondly, have a look at GROUP BY as I think this is what you are after to resolve multiple rows coming back:
SELECT * 
FROM leadproductdetail
JOIN leadproductquality ON leadproductdetail.leadid = leadproductquality.leadid
AND leadproductdetail.leadprodnumber = leadproductquality.leadprodnumber
WHERE leadproductdetail.leadid =1
AND leadproductdetail.leadprodnumber =1
GROUP BY leadproductquality.leadid
I hope that helps. Without seeing more code or fully understanding your database schema it's hard to tell exactly but hopefully that's kind of on the right tracks.

Let me know how you get on.

PS. Sorry I didn't reply to your messages from the other week. For some reason I didn't get an email notifying me I had a new message :-S

Good luck!!

hi!

thank for your help on this as well!

I tried the code, it stopped the repeating records! but.......

In my database I have:
- 3 records of leadproductquality where leadid and leadprodnumber = 1
and
- 1 record where leadproductdetail and leadprodnumber = 1

so.. the problem is only 1 leadproductquality is shown (I think the leadproductdetail might be a limiting factor).

is it possible to join two tables where the one has more records than the other?

shure2

I solved it by splitting the sql up into two different queries, which isn't ideal but its a short term solution until I can find out how this method will work.

thanks for your help, if you can help me with my old javascript problem i would be really grateful!