EasyCFM.COM ColdFusion Forums / MySQL Related Issues / Join table not quite

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Join table not quite -- page: 1 2

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

daveED
08-20-2007 @ 7:15 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

I having trouble with joing two tables
where i am trying to get all records that have i this case a unique_id

The sql below is showing me the records from one table, and in the Debugging Information SQL Queries it also showing a record count which is double the record count there are 13 in one table with a unique_id 490323 and record count is 26.

Here is the source

<cfquery name="getRecords" datasource="#request_dsn#">

SELECT
id, name, unique_id
FROM db.t1
RIGHT JOIN db.t2 USING (unique_id)
WHERE t1.unique_id = '490323'

</cfquery>

<cfoutput query="getRecords" group="id">
#id# - #name# <br />
</cfoutput>

Can someone show me how to get records from both tables where a uniform column value is referenced?

This message was edited by daveED on 8-20-07 @ 8:10 AM

balaji
08-21-2007 @ 11:05 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 24
Joined: Jun 2007

hi Deved,
if you have a unique_id in both table. then you can try this
<cfquery name="getRecords" datasource="#request_dsn#">
SELECT
id, name, unique_id
FROM db.t1
RIGHT JOIN db.t2 on (t1.unique_id = t2.unique_id)
WHERE t1.unique_id = '490323'
</cfquery>

thnks

balaji

daveED
08-21-2007 @ 12:40 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

Thanks for the reply,

I had tryed that and in the output it still is displaying two results for each record?

megan
08-21-2007 @ 3:16 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

Hi

when you are outputting your results, did you try group="unique_id" instead of group="id" ???

thanks, Megan

We have art so that we shall not die of reality ~ Nietzsche

daveED
08-21-2007 @ 3:31 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

Hi
Yes, megan i have tryed that

Lossed
08-21-2007 @ 7:59 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Hi daveEd,

Are there "id" and "name" fields in both tables, and if so, do you want all of them in the record set or just a distinct values of each?

Or maybe you want to pull the ids and names from one table when their uniqueIDs are the same as those in another table?




Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

daveED
08-22-2007 @ 2:05 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

Hi Lossed,

Yes 'id' and 'name', fields are in both of the tables.
I would like to pull the 'ids' and 'names' from both tables where the unique_id are the same in a table.

Thak would be good,
Thanks

Lossed
08-22-2007 @ 5:39 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Hi daveEd,

Does each 'unique_id' have the same 'name' value associated with it? E.G, all entries with a unique_id of 490323 will have a name of, say, 'Henry'? If so then you might find this link about normalisation helpful:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

If the following doesn't work, could you please either post the create queries for each table, or explain the relationships between id, name, and unique_id in each table? That might help.

SELECT
    t1.id as t1ID,
    t2.id as t2ID,
    t1.name as t1Name,
    t2.name as t2Name,
    t1.unique_id as t1UID
FROM t1
JOIN t2 USING (unique_id)
WHERE t1.unique_id = 490323

Fingers crossed Smile


Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

daveED
08-23-2007 @ 1:38 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

Hi, Lossed
Thanks for link effot, But i have already reat that Wink

This is not my real table it just for posting to make it easy to read there are 70 to 90 columns in each

CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(128) NOT NULL,
unique_id VARCHAR(14) NOT NULL,
union_breaker_1 VARCHAR(80) NOT NULL
)TYPE=MyISAM;

ALTER TABLE t1 AUTO_INCREMENT = 10000;

CREATE TABLE t2 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(128) NOT NULL,
unique_id VARCHAR(14) NOT NULL,
union_breaker_2 VARCHAR(70) NOT NULL
)TYPE=MyISAM;

ALTER TABLE t2 AUTO_INCREMENT = 70000;



The tables do not use a relation ship, the only column that contains the same values is the "unique_id" where the 'name' might be ducati in one table and in table2 name mignt be 'bmw'.  

This message was edited by daveED on 8-23-07 @ 1:46 AM

Lossed
08-23-2007 @ 5:58 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Hi again daveEd,

Sorry but I'm stumped.




Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

PAGE: 1 2

Website Designed and Developed by Pablo Varando.