Member Avatar for Coyx

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 because: n/a
  • 2 Contributors
  • 3 Replies
  • 200 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Coyx
Member Avatar for Coyx

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 because: n/a
Member Avatar for smantscheff

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
Member Avatar for Coyx

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

Again, thanks a bunch! :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.