0

Hi everyone,

I'm in a spot of bother. Attached is an image which should help better show what I'm about to ask for assistance with. :)

http://img96.imageshack.us/img96/7624/sqlm.jpg

What I have, is 2 tables that associate each other with 'packholder'

In the table shown, you can see Packholder - 1, 2, 3 or 4. Now, they go back to the Packholder table and ref 1, 2, 3, 4 with either "Yes", "No", "Bought Online" or "Retired".

Now, what I'm trying to do, is print out the selected columns in an Excel Sheet, but instead of showing the ID for Packholder (1 2 3 or 4), I want the result set to show, Yes, No, Bought Online or Retired, as per the entries 1 to 4 in the Packholder table.

Everything worked until I started messing about with trying to left join the information... which I'm not so good with these days! Also, the query as you see right there in the screen dump, actually works - but it still just shows the ID, rather than the Text for Packholder.

(Incase of image not showing, SQL below)

SELECT cms_contacts.clientnumber, cms_contacts.companyname, cms_contacts.address1, cms_contacts.postcode, cms_contacts.phone, cms_contacts.contactname, cms_contacts.packholder
FROM prod_contacts.cms_contacts
LEFT JOIN prod_contacts.cms_table_packholder ON cms_contacts.packholder
WHERE LEFT( postcode, 3 )
IN (
"S1", "S10", "S11", "S12", "S13", "S14", "S17", "S17", "S18", "S2", "S20", "S21", "S25", "S26", "S3", "S32", "S33", "S35", "S36", "S4", "S40", "S41", "S42", "S43", "S44", "S45", "S5", "S6", "S60", "S61", "S62", "S63", "S64", "S65", "S66", "S7", "S71", "S72", "S73", "S74", "S75", "S8", "S80", "S81", "S9"
)
AND packholder IN ("1", "2", "3", "4")
AND clientnumber != ''
ORDER BY clientnumber ASC

#Edit#

Also, I just noticed, that query above seems to be returning exactly 4 of each entry - as in, 3 duplicates in addition to 1 record. So I have 4 of the same record (you can just make out the blurred bits, 4 of one, then 4 of another, and so on) Is this because I'm selecting records where the post code is "XXX" and the packholder is "X" ?

Edited by Coyx: n/a

2
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by Coyx
0

Also tried this Syntax, to no avail! :(

SELECT clientnumber, companyname, address1, postcode, phone, contactname, packholder 
FROM prod_contacts.cms_contacts LEFT JOIN prod_contacts.cms_table_packholder ON cms_contacts.packholder = cms_table_packholder.id
WHERE LEFT( postcode, 3 )
IN (
"S1", "S10", "S11", "S12", "S13", "S14", "S17", "S17", "S18", "S2", "S20", "S21", "S25", "S26", "S3", "S32", "S33", "S35", "S36", "S4", "S40", "S41", "S42", "S43", "S44", "S45", "S5", "S6", "S60", "S61", "S62", "S63", "S64", "S65", "S66", "S7", "S71", "S72", "S73", "S74", "S75", "S8", "S80", "S81", "S9"
)
AND packholder
IN (
"1", "2", "3", "4"
)
AND clientnumber != ''
ORDER BY clientnumber ASC

Each query is punching out the data that I want, except they both still punch out Packholder "1", "2", "3", "4", instead of "Yes", "No", etc etc.

Edited by Coyx: n/a

1

Without having understood the details of your problem, I see that it cannot work: your LEFT JOIN clause does not contain any comparison:

FROM prod_contacts.cms_contacts
LEFT JOIN prod_contacts.cms_table_packholder ON cms_contacts.packholder

should probably read

FROM prod_contacts
LEFT JOIN cms_contacts ON cms_contacts.packholder = prod_contacts.packholder
0

Thank you very much Smantscheff - I've managed to resolve my problem based on your answer.

Again, thanks a bunch! :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.