EasyCFM.COM ColdFusion Forums / MySQL Related Issues / Anothe Join

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

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

daveED
07-25-2007 @ 1:45 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

hi, i am trying to join tables with differnt colums so i cant use union.
I need to get all records with the same unique_id from t1 & t2. i try this code and
it repeats the output for one table only?
Can you show me how to do this please?

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

SELECT t1.unique_id, t2.unique_id, t1.name, t2.name
FROM t1
INNER JOIN t1 ON t1.unique_id = t2.unique_id
WHERE t1.unique_id = '27040201311177';
</cfquery>

<cfoutput query="getRecords">
#getRecords.name#
</cfoutput>

This message was edited by daveED on 7-25-07 @ 2:24 PM

dlackey
07-25-2007 @ 4:33 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

Are you concerned with duplicate values?  You may want to look at a full join or if you are only looking for one column, you can output it to a list and then clean up the list removing duplicates... if this is what you mean, let me know.  I'm working on another query for another user here and then I can devote some attention to you.  Smile

Cheers,
Chip

daveED
07-25-2007 @ 4:47 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

hi,
yes i am looking to get all the records with a defined value.

Thanks,

dlackey
07-25-2007 @ 4:52 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

Blush  I didn't realize you posted the actual query.  

Did you use CROSS JOIN?

Cheers,
Chip

daveED
07-25-2007 @ 4:54 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

Thanks for the reply
for example one table has cd's the other table has dvd's, both the colums for som of the rows have the same unique_id.

IS adde cross join but it still only gives values from one table

as above so below
---
Historical Background of Scalar EM Weapons -- Fascinating Read --
http://www.cheniere.org/books/analysis/history.htm

daveED
07-25-2007 @ 4:56 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

Here is my actual code,

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

SELECT t1.unique_id, t2.unique_id, t1.name, t2.name
FROM t1
INNER JOIN t1 ON t1.unique_id = t2.unique_id
WHERE t1.unique_id = '27040201311177';
</cfquery>

<cfoutput query="getRecords">
#getRecords.name#
</cfoutput>

This message was edited by daveED on 7-25-07 @ 7:04 PM

dlackey
07-25-2007 @ 5:19 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

Have you tried to use Union All?

Union all selects all rows from each table and combines them into a single table. The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Cheers,
Chip

daveED
07-25-2007 @ 5:28 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

Wink  that works fine,

I thought that UNION dose not work with the colume names not being the same?

daveED
07-25-2007 @ 5:33 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 116
Joined: Sep 2006

So i can't use UNION with out having the colums in each table if there not the same. Frown

If all else fails i will just have to have the same colums across all tables, which i was hoping not to because there will be 173 colums.

Each row would blow out to 8K each.
And there might be 400K rows





This message was edited by daveED on 7-25-07 @ 5:43 PM

dlackey
07-25-2007 @ 5:50 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

I'm saying "Unioin All", not just "UNION"  After re-reading my post, that might not have been clear as to the suggestion.  I've never used it before so I'm not sure exactly what it will return.  Are you open to other ideas or only doing this via an SQL query?

My other idea, was to output one query to a comma delimited list, output the 2nd query to a 2nd comma delimited list, and then combine the two list.  How many records are you talking about, worse case scenario?

Cheers,
Chip

PAGE: 1 2

Website Designed and Developed by Pablo Varando.