crimage
Posts: 5
|
Posted: 08/19/2004, 8:23 AM |
|
Hi,
I'm trying to join several tables to show in a grid, but i can't get it right.
i'm joining two tables using a third table which links two records together.
I have "verening" "contacts" and "link_contact_ver"
the problem is, when i join "link_contact_ver" with "vereniging" i don't get any records anymore.
I tried with inner, left and right join, but none of them gives the right results.
anyone an idea?
|
|
|
klw
|
Posted: 08/19/2004, 9:48 AM |
|
Sounds like your SQL syntax is incorrect.
Provide the complete syntax in reply to this post along
with the fields you are linking on.
|
|
|
crimage
Posts: 5
|
Posted: 08/19/2004, 9:54 AM |
|
SELECT contacts_id, contacts_naam, contacts_voornaam, contacts_straat, contacts_nummer, contacts_bus, contacts_postcode, contacts_gemeente,
contacts_telefoon, contacts_gsm, contacts_fax, contacts_email, ver_naam
FROM (link_contact_ver INNER JOIN contacts ON
contacts.contacts_id = link_contact_ver.contact_id) INNER JOIN vereniging ON
link_contact_ver.link_contact_ver_ver_id = vereniging.ver_id
|
|
|
DonB
|
Posted: 08/19/2004, 10:10 PM |
|
But do you have data that supports the JOINs and produces results? Might
try just two tables at a time to see which one is the problem. Or have you
mistakenly set up one field in the JOIN as a different datatype from the
other?
--
DonB
http://www.gotodon.com/ccbth
"crimage" <crimage@forum.codecharge> wrote in message
news:54124db6006678@news.codecharge.com...
> SELECT contacts_id, contacts_naam, contacts_voornaam, contacts_straat,
> contacts_nummer, contacts_bus, contacts_postcode, contacts_gemeente,
> contacts_telefoon, contacts_gsm, contacts_fax, contacts_email, ver_naam
> FROM (link_contact_ver INNER JOIN contacts ON
> contacts.contacts_id = link_contact_ver.contact_id) INNER JOIN vereniging
ON
> link_contact_ver.link_contact_ver_ver_id = vereniging.ver_id
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
crimage
Posts: 5
|
Posted: 08/21/2004, 2:01 AM |
|
i'm linking tables on id's
i've got this query now:
SELECT
`contacts`.`contacts_id`,
`contacts`.`contacts_naam`,
`contacts`.`contacts_voornaam`,
`contacts`.`contacts_straat`,
`contacts`.`contacts_nummer`,
`contacts`.`contacts_bus`,
`contacts`.`contacts_postcode`,
`contacts`.`contacts_gemeente`,
`contacts`.`contacts_telefoon`,
`contacts`.`contacts_gsm`,
`contacts`.`contacts_fax`,
`contacts`.`contacts_email`,
`vereniging`.`ver_naam`
FROM
`contacts`
LEFT OUTER JOIN `link_contact_ver` ON (`contacts`.`contacts_id` = `link_contact_ver`.`contact_id`)
RIGHT OUTER JOIN `vereniging` ON (`link_contact_ver`.`link_contact_ver_ver_id` = `vereniging`.`ver_id`)
but now the problem is, i get every contact two times (there are two records in "verenigingen") i only want every record from contacts one time.
anyone has an idea?
|
|
|
Don Safar
|
Posted: 08/21/2004, 8:22 AM |
|
"outer join" specifies to return all rows, even those that don't have a
match. remove the keyword "outer" from the join.
"crimage" <crimage@forum.codecharge> wrote in message
news:541270f5b64f9c@news.codecharge.com...
> i'm linking tables on id's
>
> i've got this query now:
>
> SELECT
> `contacts`.`contacts_id`,
> `contacts`.`contacts_naam`,
> `contacts`.`contacts_voornaam`,
> `contacts`.`contacts_straat`,
> `contacts`.`contacts_nummer`,
> `contacts`.`contacts_bus`,
> `contacts`.`contacts_postcode`,
> `contacts`.`contacts_gemeente`,
> `contacts`.`contacts_telefoon`,
> `contacts`.`contacts_gsm`,
> `contacts`.`contacts_fax`,
> `contacts`.`contacts_email`,
> `vereniging`.`ver_naam`
> FROM
> `contacts`
> LEFT OUTER JOIN `link_contact_ver` ON (`contacts`.`contacts_id` =
> `link_contact_ver`.`contact_id`)
> RIGHT OUTER JOIN `vereniging` ON
> (`link_contact_ver`.`link_contact_ver_ver_id` = `vereniging`.`ver_id`)
>
> but now the problem is, i get every contact two times (there are two
records in
> "verenigingen") i only want every record from contacts one time.
> anyone has an idea?
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|