?
Solved

mysql - find common values in one database

Posted on 2011-05-11
4
Medium Priority
?
381 Views
Last Modified: 2013-12-12
I've been trying to write a query that will find common values in a table for every entry authored by a specific person.  Frankly, I'm so burnt, it's hard to explain what I'm trying to do here, but essentially, below are some tables and the values within each.

I'm trying to output:

tid | nid | author | title | name  | nid  | title
43  |  1  | 234  | John Doe  | Private  | 3 |  Bob Smith
43  |  2  | 234  | Jane Doe  | Private  | 3 |  Bob Smith
43  |  1  | 234  | John Doe  | Private  | 3 |  Harvey Jones
43  |  2  | 234  | Jane Doe  | Private  | 3 |  Harvey Jones
32  |  2  | 234  | Jane Doe  | Workshop  | 3 |  Bob Smith
51  |  2  | 234  | Jane Doe  | Skye  | 3 |  Harvey Jones

Open in new window



The closest I've come is with this query, but I can't figure out how to get both of those titles in the output:
SELECT n.title, n.uid, tn.nid, tn.tid, td.name from term_node tn 
LEFT JOIN node n on tn.nid = n.nid 
LEFT JOIN term_data td on tn.tid = td.tid 
WHERE tn.tid IN ( select tn2.tid FROM term_node tn2 LEFT JOIN node n3 ON tn2.nid = n3.nid) and n.type = 'ctype' and n.uid != 234 )


// node table
nid | title  | author | type 
1  | John Doe | 234  | ctype
2  | Jane Doe | 234  | ctype
3  | Bob Smith  | 345  | ctype
4  | Harvey Jones | 459  | ctype

//term_node table
nid | vid | tid 
1   | 1  | 43
1   | 1  | 47
3   | 3  | 32
3   | 3  | 43
3   | 3  | 36
2   | 2  | 32
2   | 2  | 43
2   | 2  | 49
2   | 2  | 51
4   | 4  | 51
4   | 4  | 98
4   | 4  | 43

//term_data table
tid | name
32  | Workshop
36  | Class
43  | Private
47  | Online
49  | CSM
51  | Skye
98  | Capture

Open in new window

0
Comment
Question by:n00b0101
  • 3
4 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35742525
Hi.

I haven't looked at this deeply, yet, but based on reading of the question it sounds like you are close but are only having difficulty with multiple columns of the same name.  To fix, you would use aliases.

i.e.,
SELECT n.title as Title1, n.uid, tn.nid, tn.tid, td.name, {other title column} as Title2

If that is not the issue, I will have to post back after building test data and trying query you have already.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35742590
This is close, but not sure I understand all the requirements.
SELECT tn.tid
     , n1.nid, n1.author as NAuthor, n1.title as NTitle
	 , n2.nid as vid, n2.author as VAuthor, n2.title as VTitle
FROM term_node tn 
JOIN node n1 ON n1.nid = tn.nid
JOIN term_node tv ON tv.tid = tn.tid AND tv.nid <> tn.nid
JOIN node n2 ON n2.nid = tv.nid
JOIN term_data td on tn.tid = td.tid 
WHERE n2.author <> 234

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35742638
If you are looking for common values across all tables, don't you need INNER JOIN? Also what are you trying to achieve with WHERE condition?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35743443
That is what I thought also, Sharath.  By the way, this is the result I get, so once you explain what the WHERE condition was doing it may help to figure out why I am off a bit from your sample output:

tid	nid	NAuthor	NTitle	vid	VAuthor	VTitle
43	1	234	John Doe	3	345	Bob Smith
43	1	234	John Doe	4	459	Harvey Jones
32	2	234	Jane Doe	3	345	Bob Smith
43	2	234	Jane Doe	3	345	Bob Smith
43	2	234	Jane Doe	4	459	Harvey Jones
51	2	234	Jane Doe	4	459	Harvey Jones
43	3	345	Bob Smith	4	459	Harvey Jones
43	4	459	Harvey Jones	3	345	Bob Smith

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question