n00b0101
asked on
mysql - find common values in one database
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:
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:
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 JonesThe 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 | CaptureASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
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.